Проверка и поиск вводимых значений в системе анализа данных (стр. 2 из 3)
Результат:
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% |
- Вычислить розничную цену пеленок путем добавления процента наценки к стоимости.
=ВПР("DI-328";A2:D6;3;ЛОЖЬ)*(1+ВПР("DI-328";A2:D6;4;ЛОЖЬ))