Анализ данных Excel — Функции поиска
Вы также можете комбинировать эти функции, чтобы получить требуемые результаты на основе имеющихся у вас входных данных.
Использование функции VLOOKUP
lookup_value — это значение, которое вы хотите найти. Lookup_value может быть значением или ссылкой на ячейку. Значение Lookup_value должно находиться в первом столбце диапазона ячеек, указанного в table_array.
table_array — это диапазон ячеек, в которых VLOOKUP будет искать lookup_value и возвращаемое значение. table_array должен содержать
Примечание . Первый столбец, содержащий lookup_value, может быть отсортирован в порядке возрастания или нет. Тем не менее, результат будет основан на порядке этого столбца.
col_index_num — номер столбца в table_array, который содержит возвращаемое значение. Числа начинаются с 1 для самого левого столбца таблицы-массива
range_lookup — это необязательное логическое значение, которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение. range_lookup может быть
опущено, и в этом случае предполагается, что оно TRUE, и VLOOKUP пытается найти приблизительное совпадение
ИСТИНА, и в этом случае VLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value
ЛОЖЬ, в этом случае VLOOKUP пытается найти точное совпадение
1, и в этом случае предполагается, что это ИСТИНА, а VLOOKUP пытается найти приблизительное совпадение
0, в этом случае предполагается, что это ЛОЖЬ, а VLOOKUP пытается найти точное соответствие
lookup_value — это значение, которое вы хотите найти. Lookup_value может быть значением или ссылкой на ячейку. Значение Lookup_value должно находиться в первом столбце диапазона ячеек, указанного в table_array.
table_array — это диапазон ячеек, в которых VLOOKUP будет искать lookup_value и возвращаемое значение. table_array должен содержать
Примечание . Первый столбец, содержащий lookup_value, может быть отсортирован в порядке возрастания или нет. Тем не менее, результат будет основан на порядке этого столбца.
col_index_num — номер столбца в table_array, который содержит возвращаемое значение. Числа начинаются с 1 для самого левого столбца таблицы-массива
range_lookup — это необязательное логическое значение, которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение. range_lookup может быть
опущено, и в этом случае предполагается, что оно TRUE, и VLOOKUP пытается найти приблизительное совпадение
ИСТИНА, и в этом случае VLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value
ЛОЖЬ, в этом случае VLOOKUP пытается найти точное совпадение
1, и в этом случае предполагается, что это ИСТИНА, а VLOOKUP пытается найти приблизительное совпадение
0, в этом случае предполагается, что это ЛОЖЬ, а VLOOKUP пытается найти точное соответствие
Примечание. Если range_lookup опущено или TRUE или 1, VLOOKUP работает правильно, только если первый столбец в table_array отсортирован в порядке возрастания. В противном случае это может привести к неверным значениям. В таком случае используйте FALSE для range_lookup.
Использование функции VLOOKUP с range_lookup TRUE
Рассмотрим список студенческих оценок. Вы можете получить соответствующие оценки с помощью VLOOKUP из массива, содержащего интервалы отметок и категорию прохода.
Обратите внимание, что метки первого столбца, на основе которых получены оценки, сортируются в порядке возрастания. Следовательно, используя TRUE для аргумента range_lookup, вы можете получить приблизительное совпадение, которое требуется.
Рекомендуется называть массивы таким образом, чтобы вам не приходилось запоминать диапазоны ячеек. Теперь вы готовы найти оценку для списка оценок, которые у вас есть следующим образом —
col_index_num — указывает, что столбец возвращаемого значения в table_array равен 2
Первый столбец, содержащий значение поиска в оценках table_array, находится в порядке возрастания. Следовательно, результаты будут правильными.
Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. VLOOKUP вычисляет следующим образом —
col_index_num — указывает, что столбец возвращаемого значения в table_array равен 2
Первый столбец, содержащий значение поиска в оценках table_array, находится в порядке возрастания. Следовательно, результаты будут правильными.
Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. VLOOKUP вычисляет следующим образом —
| Метки | Пройти категорию |
|---|---|
| Потерпеть поражение | |
| > = 35 и | Третий класс |
| > = 50 и | Второй класс |
| > = 60 и | Первый класс |
| > = 75 | Первый класс с отличием |
Использование функции VLOOKUP с range_lookup FALSE
Рассмотрим список продуктов, содержащий идентификатор продукта и цену для каждого из продуктов. Идентификатор продукта и цена будут добавляться в конец списка при каждом запуске нового продукта. Это будет означать, что идентификаторы продукта не должны быть в порядке возрастания. Список продуктов может быть таким, как показано ниже —
Вы можете получить цену продукта по идентификатору продукта с помощью функции VLOOKUP, поскольку идентификатор продукта находится в первом столбце. Цена указана в столбце 3, поэтому col_index_ num должно быть 3.
Это связано с тем, что первый столбец в массиве ProductInfo не отсортирован в порядке возрастания. Следовательно, не забывайте использовать FALSE всякий раз, когда данные не отсортированы.
Использование функции HLOOKUP
Вы можете использовать функцию HLOOKUP , если данные в строках , а не столбцы.
пример
Давайте возьмем пример информации о продукте. Предположим, что массив выглядит следующим образом —
Назовите этот массив ProductRange. Вы можете узнать цену продукта по идентификатору продукта с функцией HLOOKUP.
Назовите этот массив ProductRange. Вы можете узнать цену продукта по идентификатору продукта с функцией HLOOKUP.
lookup_value — это значение, которое будет найдено в первой строке таблицы
table_array — таблица данных, в которой ищутся данные
row_index_num — номер строки в table_array, из которого будет возвращено соответствующее значение
range_lookup — это логическое значение, которое указывает, хотите ли вы, чтобы HLOOKUP нашел точное или приблизительное совпадение
опущено, и в этом случае предполагается, что оно TRUE, и HLOOKUP пытается найти приблизительное соответствие
ИСТИНА, и в этом случае HLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value
ЛОЖЬ, и в этом случае HLOOKUP пытается найти точное совпадение
1, и в этом случае предполагается, что это ИСТИНА, а HLOOKUP пытается найти приблизительное совпадение
0, в этом случае предполагается, что это ЛОЖЬ, и HLOOKUP пытается найти точное соответствие
lookup_value — это значение, которое будет найдено в первой строке таблицы
table_array — таблица данных, в которой ищутся данные
row_index_num — номер строки в table_array, из которого будет возвращено соответствующее значение
range_lookup — это логическое значение, которое указывает, хотите ли вы, чтобы HLOOKUP нашел точное или приблизительное совпадение
опущено, и в этом случае предполагается, что оно TRUE, и HLOOKUP пытается найти приблизительное соответствие
ИСТИНА, и в этом случае HLOOKUP пытается найти приблизительное совпадение. Другими словами, если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше lookup_value
ЛОЖЬ, и в этом случае HLOOKUP пытается найти точное совпадение
1, и в этом случае предполагается, что это ИСТИНА, а HLOOKUP пытается найти приблизительное совпадение
0, в этом случае предполагается, что это ЛОЖЬ, и HLOOKUP пытается найти точное соответствие
Примечание. Если range_lookup опущен или имеет значение TRUE или 1, HLOOKUP работает правильно, только если первый столбец в table_array отсортирован в порядке возрастания. В противном случае это может привести к неверным значениям. В таком случае используйте FALSE для range_lookup.
Использование функции HLOOKUP с range_lookup FALSE
Вы можете получить цену продукта по идентификатору продукта с помощью функции HLOOKUP, поскольку идентификатор продукта находится в первой строке. Цена находится в строке 3 и, следовательно, row_index_ num должно быть 3.
Правильный ответ из массива ProductRange — 171,65. Вы можете проверить результаты.
Вы замечаете, что, как и в случае с VLOOKUP, вы получили
Правильный результат, когда range_lookup равен FALSE, и
Неправильный результат, когда range_lookup равен TRUE.
Правильный результат, когда range_lookup равен FALSE, и
Неправильный результат, когда range_lookup равен TRUE.
Это связано с тем, что первая строка в массиве ProductRange не сортируется в порядке возрастания. Следовательно, не забывайте использовать FALSE всякий раз, когда данные не отсортированы.
Использование функции HLOOKUP с range_lookup TRUE
Рассмотрим пример оценок учеников, используемых в VLOOKUP. Предположим, у вас есть данные в строках, а не в столбцах, как показано в таблице, приведенной ниже —
Обратите внимание, что метки первой строки, на основе которых получены оценки, сортируются в порядке возрастания. Следовательно, используя HLOOKUP с TRUE для аргумента range_lookup, вы можете получить оценки с приблизительным соответствием, и это то, что требуется.
row_index_num — указывает, что столбец возвращаемого значения в table_array равен 2
Первый столбец, содержащий значение поиска в Grades table_array, находится в порядке возрастания. Следовательно, результаты будут правильными.
Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. HLOOKUP вычисляет следующим образом —
row_index_num — указывает, что столбец возвращаемого значения в table_array равен 2
Первый столбец, содержащий значение поиска в Grades table_array, находится в порядке возрастания. Следовательно, результаты будут правильными.
Вы также можете получить возвращаемое значение для приблизительных совпадений. т.е. HLOOKUP вычисляет следующим образом —
| Метки | > = 35 и | > = 50 и | > = 60 и | > = 75 | |
|---|---|---|---|---|---|
| Пройти категорию | Потерпеть поражение | Третий класс | Второй класс | Первый класс | Первый класс с отличием |
Использование функции INDEX
Если у вас есть массив данных, вы можете получить значение в массиве, указав номер строки и номер столбца этого значения в массиве.
Рассмотрим следующие данные о продажах, в которых вы найдете продажи в каждом из северного, южного, восточного и западного регионов по продавцам, которые указаны в списке.
Предположим, вы не знаете номеров строк для продавцов и номеров столбцов для регионов. Затем вам нужно сначала найти номер строки и номер столбца, прежде чем вы получите значение с помощью функции индекса.
Вы можете сделать это с помощью функции MATCH, как описано в следующем разделе.
Использование функции MATCH
Если вам нужно положение элемента в диапазоне, вы можете использовать функцию MATCH. Вы можете комбинировать функции MATCH и INDEX следующим образом:
Как рассчитать стандартное отклонение в Excel — Mexn
Давайте рассмотрим простой случай проверки строки, попросив пользователя ввести свое имя. Нашим критерием проверки будет то, что пользователь вводит только буквенные символы или пробелы. Если встретится что-то еще, ввод будет отклонен.
| Метки | Пройти категорию |
|---|---|
| Потерпеть поражение | |
| > = 35 и | Третий класс |
| > = 50 и | Второй класс |
| > = 60 и | Первый класс |
| > = 75 | Первый класс с отличием |
Назначение
Удаляет выведенную запись; остальные записи базы сдвигаются. Удаленные записи не могут быть восстановлены.
Восстанавливает отредактированные поля в выведенной записи, удаляя произведенные изменения. Если нужно восстановить запись, то это необходимо сделать перед нажатием клавиши ENTER или перед переходом к другой записи.
Выводит окно диалога, в котором можно ввести критерии или условия дляотыскания нужного подмножества записей
Удаляет существующий критерий из окна диалога. Доступна только тогда, когда нажата кнопка Критерии.
Возвращает к типу формы данных по умолчанию. Доступна только тогда, когда нажата кнопка Критерии
Если нажата кнопка Критерии, то кнопки Предыдущая и Следующая передвигаются только по отобранным записям.
После задания данных для сортировки и выбора команды на экране появиться окно диалога, в котором следует задать следующие поля:
Затем -Два дополнительных окна дают возможность определить порядок вторичной сортировки для строк, в которых имеются совпадающие элементы. Нужно устанавливать флажок По Возрастанию или По Убыванию в каждом окне для определения упорядочения данных.
Строка Меток Столбцов-Определяет, имеется ли в сортируемой базе строка заголовков, которую нужно исключить из сортировки. Если база
содержит метки столбцов, то выберите переключатель Есть, а если меток нет, то выберите переключатель Нет.
Параметры -Выводит окно диалога Параметры Сортировки, в котором Вы можете:
-Определить пользовательский порядок сортировки для столбца, указанного в
-Изменить ориентацию сортировки, то есть, вместо сортировки сверху вниз
После установки всех необходимых полей и параметров следует нажать на кнопку ОК.
Microsoft Excel использует следующие основные принципы при сортировке:
-если сортировка ведется по одному столбцу, то строки с одинаковыми
значениями в этом столбце сохраняют прежнее упорядочивание;
-строки с пустыми ячейками в столбце, по которому ведется сортировка,
— параметры сортировки — выбранный столбец, порядок (возрастающий или
убывающий) и направление сортировки (сверху вниз или слева направо) —
сохраняются от предыдущего применения сортировки до тех пор, пока их не
изменить, не выберать другую базу данных или не изменить метки столбцов;
— если сортировка ведется по нескольким столбцам, строки с одинаковыми
значениями в первом ключевом столбце сортируются в порядке,определяемом вторым ключевым столбцом, строки с одинаковыми значениями в первых двух ключевых столбцах сортируются по третьемустолбцу.
Если результат сортировки не тот, которого ожидали, обратите внимание на тип сортируемых данных. Убедитесь, что все числа введены как цифровые данные или как текст.Следует ввести все данные в одном столбце либо как числа либо кактекст.
Команда АВТОФИЛЬТРпомещает кнопки раскрывающихся списков (кнопки со стрелкой) непосредственно в строку с метками столбцов, с их помощью можно выбрать элементы базы, которые следует вывести на экран. Например, можно вывести на экран все строки, содержащие некоторое значение в столбце, скажем, «все строки, которые содержат дату заказа 11.12.97 в столбце Дата Заказа».
Можно также применять пользовательские критерии сравнения для фильтрации данных в базе .Для этого в списке следует выбрать пункт Настройка. На экран выведится окно диалога Пользовательский Автофильтр.
В этом окне можно определить до двух критериев сравнения для одного и того же столбца при фильтрации данных. Microsoft Excel сравнит элементы списка с введенными ранее значениями и выведет лишь строки, удовлетворяющие критерию. Можно также использовать условные операторы И / ИЛИ для объединения или сравнения критериев в одном и том же столбце.
Для определения двух критериев выберите один из следующих вариантов:
-Нажмите переключатель «И» для объединения двух критериев в Вашем
фильтре.В этом случае выберуться данные, для которых выполняются сразу
-Нажмите переключатель «ИЛИ» для использования двух различных
критериев в Вашем фильтре. В этом случае выберуться данные, для которых
Команда УСИЛЕННЫЙ ФИЛЬТР позволяет отыскивать строки с помощью более сложных критериев, например, «продажи10.02.97 на сумму более, чем 500000 р., или продажи 11.02.97 на сумму более, чем 300000 р.»
При выборе этой команды в появившемся окне диалога следует установить следующие параметры:
Действие -Переключатель Фильтровать Список На Месте скрывает строки, которые не удовлетворяют указанному критерию.Переключатель Копировать На Другое Место копирует отфильтрованные данные на другой рабочий лист или на другое место на том же рабочем листе.
Интервал Списка -Определяет интервал, содержащий данные, подлежащий фильтрации.
Интервал Критериев -Определяет интервал ячеек на рабочем листе, который содержит нужные критерии. Microsoft Excel выводит ссылку на этот
Копировать На -Определяет интервал ячеек, в который копируются строки, удовлетворяющие определеннымкритериям. Это поле активно только в том случае, если выбран переключатель Копировать На Другое Место.
Без Повторений -Выводит только строки, удовлетворяющие критерию и не содержащие повторяющихся элементов. Если интервал критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.
Когда строки скрываются с помощью команд АВТОФИЛЬТРи УСИЛЕННЫЙ ФИЛЬТР, рабочий лист переходит в режим фильтрации.
В этом режиме многие команды Microsoft Excel воздействуют только на видимые ячейки. После того, как применен фильтр для вывода только нужных строк, можно скопировать полученное подмножество данных в другое место для дальнейшего анализа.
Для того чтобы вернуться к первоначальному виду базы данных, следует
воспользоваться командой ДАННЫЕ \ ФИЛЬТР \ ПОКАЗАТЬ ВСЕ .
7.1 Редактирование и выбор записей в форме данных
Выделить любую ячейку таблицы, вызвать форму данных и выполнить следующии упражнения:
— с помощью кнопки Создать ввести 2 новые записи;для перехода к
следующему полю пользуйтесь мышкой, после ввода значения последнего
— проделать то же самое с последней записью, но потом измененное значение
Для того чтобы выбрать записи по какому-либо условию следует нажать кнопку Критерии и ввести следующие параметры:
— для выбора по единственному критерию в текстовом поле следует ввести
— для выбора больше какого-либо значения в числовом поле поставить
оператор сравнения“>“ в соответствующем поле и написать нужное значение
— для выбора по первой букве текстового поля ввести первую букву в
— для выбора по двум критериям в числовых полях в двух любых числовых
полях ввести операторы сравнения и нужные значения.
После ввода параметров нажать клавишу ENTER. Для просмотра выбранных записей пользуйтесь кнопками Предыдущая и Следующая. Чтобы удалить предыдущий критерий воспользуйтесь кнопкой Очистить. Закройте форму данных.
Для того чтобы отсортировать базу данных в порядке убывания третьего столбца следует выбрать любую ячейку третьего поля базы данных и выполнить командуДАННЫЕ \ СОРТИРОВКА . Появиться окно диалога, в котором в поле Сортироватьдолжно автоматически выбраться имя третьего столбца . Указать порядок сортировкипо убыванию.
В первом полеЗатемнужно определить порядок вторичной сортировки для строк третьего поля, в которых имеются совпадающие элементы. Нужно выбрать имя второго поля и установить флажок По Возрастанию. После установки всех параметров нажмите на клавишу ОК.
8.2. Как нужно изменить таблицу чтобы она имела вид базы данных ?
8.3. Какие возможности по редактированию баз данных предоставляет команда ФОРМА ?
[expert_bq id=»1570″]Седьмой дециль находится в возрастной группе 30-35 лет, так как это первый интервал, для которого накопленная частота больше или равна номера дециля 2272 2424,1. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы создать 10 равномерно удаленных друг от друга уровней дискретизации для значений диапазона Данные, в диапазон, состоящий из 10 ячеек отдельного столбца, введите следующую формулу массива.Расширенные формулы подсчета ячеек | WordEx999
Как видите, проверка ввода в C++ – это большая работа. К счастью, многие такие задачи (например, выполнение проверки чисел в виде строки) можно легко превратить в функции, которые можно повторно использовать в самых разных ситуациях.
