Министерство образования и науки Украины

Национальный горный университет

Институт экономики
Кафедра менеджмента организаций
«Проверка и поиск вводимых значений

 в системе анализа данных»
Выполнил студент

группы МН-09-3

Школа Владислав

Игоревич

Проверил доцент

Симоненко Александр

Иванович
Днепропетровск

2010

Содержание

1.     Введение……………………………………………………………………….4

2.     Основные понятия и определения к теме…………………………………...5

3.     Процедура осуществления проверки данных:

3.1.         Ограничение ввода данных значениями в раскрывающемся

списке…………………………………………………………………...6

3.2.         Ограничение ввода данных для целого числа в

определенных пределах………………………………………………9

3.3.         Ограничение ввода данных датами в заданном интервале

времени………………………………………………………………..10

3.4.         Ограничение ввода данных временем в заданном интервале……...11

3.5.         Ограничение ввода данных текстом определенной длины………...12

3.6.         Использование формул для вычисления допустимого значения….13

3.7.         Поиск недопустимых записей в книге………………………………14

4.     Процедура осуществления Поиска значений в списке данных

4.1.         Поиск значений в списке по вертикали по точному совпадению…15

4.2.         Поиск значений в списке по вертикали

по приблизительному совпадению…………………………………..17

4.3.         Поиск значений в списке по горизонтали по точному

совпадению…………………………………………………………...18

4.4.         Поиск значений в списке по горизонтали

 по приблизительному совпадению………………………………….19

4.5.         Создание формулы подстановки с помощью мастера

подстановок…………………………………………………………..20

4.5.1.   Поиск значений в списке по вертикали по точному

совпадению………………………………………………………..20

4.5.2.   Поиск значений по вертикали в списке неизвестного

размера по точному совпадению………………………………..21

5.     Примеры……………………………………………………………………...23

6.     Вывод…………………………………………………………………………27

7.     Список использованных источников………………………………………28
Введение

         Заполнить даже самую сложную электронную таблицу  не составляет проблем. Значительно сложнее никак не натворить при этом ошибок. Ведь, согласитесь, несложно ошибиться, набрав, например 1899, взамен 1999, или допустить промах в наборе внушительных чисел. В популярном табличном процессоре Microsoft Excel существует несколько хитростей, которые позволяют упростить ввод данных, а также осуществить автоматическую проверку их правильности.

                  

        


Основные понятия и определения

1.     Проверка данных — это возможность приложения Excel, которая позволяет определять, какие данные можно вводить в ячейку, а какие нет. Проверку данных можно настроить таким образом, чтобы запретить пользователям вводить данные, которые являются недопустимыми. При желании можно разрешить пользователям ввод недопустимых данных в ячейку, но при этом выводить предупреждение. В средствах проверки предусмотрена возможность настройки сообщений, содержащих сведения о том, какие элементы можно вводить в эту ячейку, а также инструкции по исправлению возможных ошибок.

2.     Сообщение (подсказка) — этот такой  тип сообщения, который появляется рядом с ячейкой. При необходимости это сообщение можно переместить. Оно остается на экране до тех пор, пока не будет выбрана другая ячейка или не будет нажата клавиша ESC.

3.     Сообщение (об ошибке) — этот такой  тип сообщения,  при котором после ввода пользователем неверных данных выводится сообщение об ошибке.

Существует три типа сообщений об ошибке:






Процедура осуществления проверки данных

1.   
Ограничение ввода данных значениями в раскрывающемся списке.

1.1.           Выберите для проверки одну или несколько ячеек.

1.2.           На вкладке Данные в группе Средства обработки данных выберите     команду Проверка данных.


1.3.          В диалоговом окне Проверка данных откройте вкладку Параметры.

1.4.          В поле «Тип  данных» выберите пункт  Список.

1.5.          Щелкните поле Источник, а затем введите значения списка, используя разделитель элементов списка Microsoft Windows (по умолчанию — запятые).  Можно также создать список значений с помощью ссылки на диапазон ячеек в любой части книги. Дополнительные сведения см. в разделе Создание раскрывающегося списка из диапазона ячеек.

Например:

для ограничения рейтинга качества производителя тремя позициями введите: Низкое, Среднее, Высокое.


 Примечания:

1.     Ширина раскрывающегося списка определяется шириной ячейки, для которой применяется проверка данных. Ширину ячейки можно настроить так, чтобы не обрезать допустимые записи, ширина которых больше ширины раскрывающегося списка.

2.     Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка.

3.     Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки.

4.     Если допустимые значения заданы диапазоном ячеек с определенным именем, в котором имеется пустая ячейка, установка флажка Игнорировать пустые ячейки позволит вводить в проверяемую ячейку любые значения. Это также верно для любых ячеек, на которые ссылаются формулы проверки: если любая ячейка, на которую указывает ссылка, пуста, то при установленном флажке Игнорировать пустые ячейки в проверяемую ячейку можно вводить любые значения.

1.6.          Можете задать сообщение для ввода (подсказки)  при выделении ячейки:



1.7.          Также задайте сообщение об ошибке:


1.8.          Протестируйте правильность работы проверки данных. Попробуйте ввести в ячейку сначала допустимые, а потом недопустимые данные и убедитесь, что параметры проверки установлены правильно и сообщения появляются в нужный момент.

1.9.         


1.10.      Совет:  после изменения процедуры проверки одной ячейки можно автоматически применить эти изменения ко всем остальным ячейкам, имеющим такие же параметры. Для этого откройте диалоговое окно Проверка данных и на вкладке Параметры установите флажок Распространить изменения на другие ячейки с тем же условием.




2.   
Ограничение ввода данных для целого числа в определенных пределах.

2.1.          В поле  «Тип данных» выберите пункт  Целое число.

2.2.          В поле «Значение» выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение «Между».  Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение.

Например, чтобы выставить ученикм оценки,

введите в поле «Минимум» — «1», а  в поле «Максимум» — «5».


Результат:





3.     Ограничение ввода данных датами в заданном интервале времени.

3.1.          В поле «Тип данных» выберите пункт Дата.

3.2.          В поле «Значение» выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.

3.3.          Введите начальную, конечную или определенную разрешенную дату. Можно также вывести формулу, которая возвращает дату.

Например, для задания интервала времени между текущей датой и датой через 3 дня после текущей выберите ограничение «Между» в поле «Данные», введите =СЕГОДНЯ() в поле Минимальное значение и затем введите =СЕГОДНЯ()+3 в поле Максимальное значение.


Результат:




4.   
Ограничение ввода данных временем в заданном интервале.

4.1.          В поле «Тип данных» выберите пункт Время.

4.2.          В поле «Значение» выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.

4.3.          Укажите время начала, окончания или определенное время, которое необходимо разрешить. Также можно ввести формулу, которая возвращает значение времени.

4.4.          Например, можна подвести итог сдачи норматива «Бег 3 км», в поле «Значение» выберите пункт Между, в поле Минимальное значение введите нижний предел норматива, а затем в поле Максимальное значение введите верхний-.


Результат:
.



5.   
Ограничение ввода данных текстом определенной длины.

5.1.          В поле «Тип данных» выберите пункт Длина текста.

5.2.          В поле «Значение» выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно.

5.3.          Введите минимальную, максимальную или определенную длину для текста. Можно также вывести формулу, которая возвращает числовое значение.

5.4.          Например, для задания конкретной длины для поля имени и фамилии (C1) равной текущей длине поля имени (A1) и поля фамилии (B1) выберите значение ограничения Меньше или равно в поле Данные и введите =СУММ(ДЛСТР(A1);ДЛСТР(B1);10) в поле Максимальное значение.


Результат:




                                                                                                                                                                                                                                          
6.   
Использование формул для вычисления допустимого значения.

6.1. В поле «Тип данных» выберите пункт Прочие.

6.2. В поле «Формула»введите формулу для расчета логического значения (ИСТИНА для верных или ЛОЖЬ для неверных данных).

Например:

         Ячейка с описанием продукта  (C6) должна содержать только текст. (=ЕТЕКСТ(C6)).


Результат:




7.   
Поиск недопустимых записей в книге.

Полученные от других пользователей листы с введенными сведениями могут содержать неверные данные. Для упрощения поиска ошибок программа Microsoft Excel позволяет выделить все данные, не отвечающие заданным условиям, окружностями красного цвета. Для этого служат кнопки Обвести неверные данные и Удалить обводку неверных данных на панели инструментов Зависимости.

Значение в данной ячейке обведено, так как оно не соответствует установленным правилам проверки данных.

После исправления данных в ячейке обводка исчезает.
Результат:
.



Поиск значений в списке данных

Предположим, что требуется найти внутренний телефонный номер сотрудника по его идентификационному номеру или узнать ставку комиссионного вознаграждения, предусмотренную за определенный объем продаж. Необходимые данные можно быстро и эффективно находить в списке и автоматически проверять их правильность. Значения, возвращенные поиском, можно затем использовать в вычислениях или отображать как результаты. Существует несколько способов поиска значений в списке данных и отображения результатов.
4.1.        
Поиск значений в списке по вертикали по точному совпадению.

Для выполнения этой задачи используется функция ВПР.

ВПР ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.

Буква В в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.

Синтаксис:

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)


Искомое значение — значение, которое должно быть найдено в первом столбце табличного массива. Этот аргумент может быть значением или ссылкой. Если искомое значение меньше, чем наименьшее значение в первом столбце табличного массива, функция ВПР возвращает значение ошибки #Н/Д.


Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое значение». Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.
Номер столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер столбца = 1, то возвращается значение из первого столбца таблицы; если номер столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер столбца»:

—   меньше 1, функция ВПР возвращает значение ошибки #ЗНАЧ!;

— больше, чем число столбцов в таблице, функция ВПР возвращает значение ошибки #ССЫЛ!.
Интервальный просмотр   — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.

Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается точное или приблизительное значение. Если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое значение.

Значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке, иначе функция ВПР может возвратить неправильный результат. Дополнительные сведения см. в разделе Сортировка данных.

Если данный аргумент имеет значение ЛОЖЬ, функция ВПР ищет только точное соответствие. В этом случае сортировка значений в первом столбце аргумента «таблица» не обязательна. Если в этом первом столбце имеется два или более значений, соответствующих аргументу «искомое значение», используется первое найденное значение. Если точное соответствие не найдено, возвращается значение ошибки #Н/Д.

Пример:


Результат:

.



4.2.        
Поиск значений в списке по вертикали по приблизительному совпадению.

Для выполнения этой задачи используется функция ВПР(см. выше).

Важно:  

Данный метод работает только при условии, что значения в первом столбце были отсортированы по возрастанию.

Пример:

В данном примере известна частота и требуется найти соответствующий ей цвет.


Результат:
.



4.3 Поиск значений в списке по горизонтали по точному совпадению
Для выполнения этой задачи используется функция ГПР.

Выполняет поиск значения в верхней строке таблицы или массива значений и возвращает значение того же столбца в заданной строке таблицы или массива.

Функция ГПР используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, следует использовать функцию ВПР.

Буква Г в аббревиатуре «ГПР» означает «горизонтальный».
Синтаксис (такой же как и у ВПР):

ГПР(искомое_значение;таблица ;номер_строки;интервальный_просмотр)
Пример:

Результат:
.

        



4.4 Поиск значений в списке по горизонтали по приблизительному совпадению.

Для выполнения этой задачи используется функция ГПР(см. выше).

Важно:

Данный метод работает только при условии, что значения в первой строке были отсортированы по возрастанию.
Пример:

Результат:
.



4.5. Создание формулы подстановки с помощью мастера подстановок.
Мастер подстановок создает формулу подстановки, основанную на данных листа, содержащих заголовки строк и столбцов. С помощью мастера подстановок можно найти остальные значения в строке, если известно значение в одном столбце, и наоборот. В формулах, которые создает мастер подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

Щелкните ячейку в диапазоне.

На вкладке Формулы в группе Решения выберите команду Подстановка.

Если команда Подстановка недоступна, необходимо загрузить надстройку мастера  подстановок.
4.5.1.  Поиск значений в списке по вертикали по точному совпадению

Для выполнения этой задачи используются функции ИНДЕКС и ПОИСКПОЗ.
Пример:

         Результат:
         .




Приведенная формула использует следующие аргументы:



Формула поиска значений в несортированном диапазоне
1.     A2:B5: Весь диапазон, в котором осуществляется поиск значений.

2.     ПОИСКПОЗ("Груши";A2:A5;0): Функция ПОИСКПОЗ определяет номер строки.

3.      "Груши": Значение для поиска в столбце подстановки.

4.      A2:A5: Столбец для поиска для функции ПОИСКПОЗ.

5.     2: Столбец, из которого возвращается значение. 1 — это крайний левый столбец.
4.5.2. Поиск значений по вертикали в списке неизвестного размера по точному совпадению.

Для выполнения этой задачи используются функции СМЕЩ и ПОИСКПОЗ.

Данный метод целесообразно использовать при поиске данных в ежедневно обновляемом внешнем диапазоне данных. Известна цена в столбце B, но неизвестно, сколько строк данных возвратит сервер, а первый столбец не отсортирован в алфавитном порядке.
Пример:

        
Результат:
         .
Приведенная формула использует следующие аргументы:


1.     A1: Левая верхняя ячейка диапазона, называемая начальной ячейкой.

2.      ПОИСКПОЗ("Груши";A2:A5; 0): Функция ПОИСКПОЗ определяет номер строки под начальной ячейкой для поиска искомого значения.

3.     "Груши": Значение для поиска в столбце подстановки.

4.     A2:A5: Столбец для поиска для функции ПОИСКПОЗ. Не включайте начальную ячейку в этот диапазон.

     5.  1: Число столбцов для поиска искомого значения, расположенных справа от начальной ячейки.




Примеры

Исходные данные №1:

Таблица 1

Код изделия	Изделие	Стоимость	Наценка
ST-340	Коляска	1 456,70р.	30%
BI-567	Нагрудник	35,60р.	40%
DI-328	Пеленки	214,50р.	35%
WI-989	Салфетки	51,20р.	40%
AS-469	Распиратор	25,60р.	45%

1.    Вычислить розничную цену пеленок путем добавления процента наценки к стоимости.

=ВПР("DI-328";A2:D6;3;ЛОЖЬ)*(1+ВПР("DI-328";A2:D6;4;ЛОЖЬ))


2.    Вычисление отпускной цены салфеток путем вычитания указанной скидки из розничной цены (20%).

=(ВПР("WI-989";A2:D6; 3; ЛОЖЬ) * (1 + ВПР("WI-989"; A2:D6; 4; ЛОЖЬ))) * (1 - 20%)


3.    Проверить стоимость товара:

—Если стоимость изделия больше или равна 1000р., отображается строка «Наценка составляет nn%»;

— в противном случае отображается строка «Стоимость меньше 200р.» (наценка составляет 30%).
=ЕСЛИ(ВПР(A2; A2:D6; 3; ЛОЖЬ) >= 200; "Наценка составляет " & 100 * ВПР(A2; A2:D6; 4; ЛОЖЬ) &"%"; "Стоимость меньше 1000р.").



4.    Проверить стоимость нагрудника:

 — Если стоимость изделия больше или равна 200р., отображается строка «Наценка составляет nn%»;

— в противном случае отображается строка «Стоимость составляет n,nn» (стоимость составляет 35,6).

= ЕСЛИ(ВПР(A3; A2:D6; 3; ЛОЖЬ) >= 200; "Наценка составляет: " & 100 * ВПР(A3; A2:D6; 4; ЛОЖЬ) &"%"; "Стоимость составляет " & ВПР(A3; A2:D6; 3; ЛОЖЬ))


Исходные данные №2:

(взяты с информационного банковского портала http://www.my-banks.info):
Таблица 2

Основные коэффициенты, характеризующие деятельность ЗАО КБ «ПриватБанк» в 2004-2008 годах, %

Коэффициенты	2008	2007	2006	2005	2004	Изменение за 2004-2008 гг.	Изменение за 2007-2008 гг.
Достаточность капитала	10,2	9,59	9,74	10,3	9,96	0,26	0,64
Соотношение между суммой основных средств и нематериальных активов к собственному капиталу	17,9	28,46	39,9	47,7	51,2	-33,29	-1058
Коэффициент капитализации	69,4	50,35	63,3	49,7	62,6	6,74	19,02
Качество активов	11,3	7,29	9,5	8,69	9,21	2,1	4,02
Качество кредитного портфеля	11,5	8,97	10,6	11,3	12,9	-1,4	2,52
Качество кредитного портфеля	11,5	8,97	10,6	11,3	12,9	-1,4	2,52
Доля ликвидных активов в обязательствах	4,43	10,05	8,34	14,8	11,3	-6,85	-5,63
Соотношение между чистым процентным доходом и процентными затратами	78,8	105	101	59	53	25,8	-26,24
Операционная рентабельность продаж	54,4	48,09	52,1	47	42,1	12,34	6,33
Рентабельность активов	1,61	2,73	1,5	2,16	1,12	0,49	-1,12
Рентабельность собственного капитала	15,8	28,47	15,4	20,9	11,3	4,51	-12,71

Источник: данные НБУ, расчеты ИБП.
1.     Оцените состоние активов (уменьшение/увеличение) за 2007-2008 г.

=ЕСЛИ(ВПР(A5;A2:H12;8;ЛОЖЬ)>0;"Позитивная динамика";"Негативная динамика")


2.     Проследите изменение рентабельности собственного капитала за последний год.

=ВПР(A12; A2:H12; 3; ЛОЖЬ)-ВПР(A12; A2:H12; 2; ЛОЖЬ)

3.     Найти среднюю операционную рентабельность за последние 5 лет.

=СРЗНАЧ(ВПР(A10;A2:H12;2;ЛОЖЬ);ВПР(A10;A2:H12;3;ЛОЖЬ);ВПР(A10; A2:H12;4;ЛОЖЬ);ВПР(A10;A2:H12;5;ЛОЖЬ);ВПР(A10;A2:H12;6;ЛОЖЬ))




Вывод

В данной работе я попытался описать основные моменты поиска и проверки значений в MS Excel.

Как оказалось, инструмент «Поиск решений» имеет огромные возможности, что не может не радовать. Ну, а поиск значений с помощью функций – это просто «конфетка».

До написания этой работы я думал, что мой уровень знаний MS Excel является базовым,

но после -  я понял: MS Excel — это как иностранный язык, всегда есть что-то,

что надо срочно освоить.

Так, что дерзайте, не пожалеете!!!!!!!!!!




Список использованной литературы:
1.   
http://office.microsoft.com/ru-ru/excel/HP100726001049.aspx

2.   
http://office.microsoft.com/ru-ru/excel/HA010346571049.aspx

3.   
http://www.on-line-teaching.com/excel/lsn027.html

4.   
http://www.shram.kiev.ua/hacker/gid/excel.shtml

5.    http://office.microsoft.com/ru-ru/excel/HP100704621049.aspx?pid=CH100799991049#Look up values vertically in a list by using an exact  match

6.    http://www.my-banks.info