Лекция 2 Функции связи таблиц поиска данных 1

Лекция 2 Функции связи таблиц (поиска данных) 1

Цель занятия: познакомиться с функциями электронной таблицы, предназначенными для поиска и извлечения данных из таблиц. Применением их для решения инженерных задач. 2

Вопросы. 1. Функции поиска данных. 2. Элементы автоматизации при работе с таблицами. 3. Примеры использования функций поиска данных. 3

Ключевые слова Автоматизация вычислений в электронной таблице, Списки, Переключатели, Проверка, Флажки, Функции поиска 4

2. 1. ФУНКЦИИ СВЯЗИ ТАБЛИЦ (ПОИСКА ДАННЫХ) 5

2. 1. Функции связи таблиц (поиска данных) Просмотр ПРОСМОТР; Вертикальный просмотр ВПР; Горизонтальный просмотр ГПР; Поиск позиции ПОИСКПОЗ; Функции ВЫБОР(), СТРОКА(), СТОЛБЕЦ(). Функции находятся в категории Ссылки и массивы Мастера функций. 6

Принцип связи таблиц 7

Функция ПРОСМОТР предназначена для поиска значений в таблицах и имеет два синтаксиса: один предназначен для работы с массивами, другой – для работы с векторами. 1. ПРОСМОТР(искомое значение; массив). 2. ПРОСМОТР(искомое значение; вектор поиска; вектор 8

Искомое значение - обязательный параметр, значение которого функция ПРОСМОТР ищет в первом столбце/строке указанного массива. Искомое значение может быть числом, текстом, логическим значением, именем или ссылкой на значение. 9

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

Функция ищет искомое значение равное или наибольшее значение меньшее заданного, и возвращает результат из последнего столбца/строки указанного диапазона, соответствующего найденной позиции. 11

Если функция не найдет значения, меньшего искомого значения, то возвращается сообщение об ошибке #НД – недействительные данные. 12

Функция ПРОСМОТР выполняет поиск по строке или по столбцу в соответствии с размерностями массива. Если ширина массива больше его высоты (т. е. есть массив имеет больше столбцов чем строк), то функция ПРОСМОТР ищет искомое значение в первой строке. Если высота массива больше его ширины (т. е. массив имеет больше строк, чем столбцов), то функция ПРОСМОТР выполняет поиск в 13 первом столбце.

Функция ГПР осуществляет поиск значений по первой строке таблицы и возвращает результат из указанной строки таблицы. Синтаксис функции: ГПР(Искомое_значение; Таблица; Номер_строки; Интервальный_просмотр) 14

Интервальный_просмотр – логическое значение, определяющее, каким образом осуществляется поиск искомого значения. Интервальный_просмотр может принимать значение ЛОЖЬ (0), ИСТИНА (1 или отсутствие значения). 0 означает точное совпадение, 1 или отсутствие значения – приближенное значение. В первом случае искомое значение должно быть точно равно значению в строке поиска, во втором случае программа ищет равное или 15

Например, если строка содержит значения {10 15 20 25}, а искомое значение равно 18, тогда программа выберет столбец, в котором значение равно 15. Если функция не находит соответствующего значения, то возвращается значение ошибки #Н/Д. 16

Функция ВПР осуществляет поиск значений по первому столбцу таблицы и возвращает результат из указанного столбца таблицы. Синтаксис функции: ВПР(Искомое_значение; Таблица; Номер_столбца; нтервальный_просмотр) Назначение аргументов функции ВПР аналогично назначению 17

Функция ПОИСКПОЗ Функция ПОИСКПОЗ предназначена для поиска в строке/столбце относительного номера позиции, в которой находится искомое значение. Синтаксис функции: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; Тип сопоставления) Искомое_значение – значение, которое требуется найти в массиве. Оно может быть числом, текстом или логическим значением; Просматриваемый_массив – строка или столбец, в котором осуществляется поиск; Тип сопоставления – может 18

Просматриваемый массив Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе Просматриваемый_массив. Например, функция ПОИСКПОЗ("б"; {"а"; "б"; "в"}; 0) возвращает 2 — относительную позицию буквы "б" в массиве {"а"; "б"; "в"}. Функция не различает регистры при сопоставлении текста. Если функция ПОИСКПОЗ не находит 19 соответствующего значения, то

Тип сопоставления 0 Ноль означает полное совпадение искомого значения и значения в строке/столбце поиска. Если Тип_сопоставления равен 0 и Искомое_значение является текстом, то Искомое_значение может содержать маску (подстановочные знаки): звездочку (*) и вопросительный знак (? ). Звездочка соответствует любой последовательности знаков, вопросительный знак — любому одиночному знаку. Если нужно найти сам вопросительный знак или звездочку, перед ними следует ввести 20

Тип сопоставления 1 Если Тип_сопоставления равен 1, то отыскивается значение равное или меньшее искомого значения. Просматриваемый_массив в этом случае должен быть отсортирован по возрастанию значения в строке/столбце поиска. Например: Просматриваемый_ массив - {25, 30, 35, 40}, Искомое_значение – 33. Результат поиска – 30, если искомое значение 35, то результат 35, если искомое значение – 20, то результат #Н/Д. 21

Тип сопоставления -1 Если Тип_сопоставления равен -1, то отыскивается значение равное или большее искомого значения. Просматриваемый_массив в этом случае должен быть отсортирован по убыванию значения в строке/столбце поиска. Например: Просматриваемый_ массив - {40, 35, 30, 25}, Искомое_значение – 33. Результат поиска – 35, если искомое значение 30, то результат 30, если искомое значение – 45, то 22 результат #Н/Д.

Пример. Найти уровень воды в реке 10 апреля 1968 года 23

1. Найти уровень воды в реке 10 апреля 1968 года. =ПРОСМОТР(10; A 2: A 33; E 2: E 33), результат Q=1120. 24

2. Найти уровень воды 10 апреля 1968 года (строка 11). Искомое значение “апрель” находится в ячейке В 37, строка поиска =11, число 11 записано в ячейку С 37: =ГПР(B 37; A 2: M 33; С 37; 0), результат Q=1120. 25

3. Найти уровень воды 10 апреля 1968 года. Искомое значение 10 находится в ячейке В 38, столбец для поиска =5 , число записано в ячейку С 38: =ВПР(B 38; A 3: M 34; C 38; 0), результат Q=1120. 26

4. Найти позицию, в которой находится месяц “апрель” Слово “апрель” записано в ячейку В 37, строка поиска {A 2: M 2}, тип сопоставления – 0. =ПОИСКПОЗ(B 37; A 2: M 2; 0), результат “ 5”. 27

5. Аналогично можно найти позицию, в которой находится число “ 10” Искомое число 10 находится в ячейке В 38, область поиска {A 2: A 33}. Тип совпадения можно выбрать пусто – по умолчанию число; 0 – точное совпадение; 1 – меньше или равно, так как числа в столбце А расположены по возрастанию: =ПОИСКПОЗ(B 38; A 2: A 33; 1), результат “ 11”. 28

6. Совместное применение функций ПОИСКПОЗ и ВПР. Найти уровень воды 10 апреля 1968 года, номер столбца (месяц) для извлечения данных найти с помощью функции ПОИСКПОЗ. Число 10 записано в ячейку В 38. В ячейку В 37 записано слово “апрель” Формулу записывать можно вручную или комбинировать запись вручную и с помощью мастера функций: =ВПР(B 38; $A$2: $M$33; ПОИСКПОЗ(B 29

Функция ВЫБОР() Функция ВЫБОР выбирает значение или действие из списка значений по номеру индекса. Синтаксис функции: Функция ВЫБОР(Номер_индекса; Значение 1; Значание 2; и т. д. ). 30

Функции СТРОКА(), СТОЛБЕЦ() Для определения номера строки или столбца могут использоваться также функции СТРОКА() / СТОЛБЕЦ(). В качестве аргумента может быть указана ссылка на ячейку или диапазон, для которой определяется номер. Если аргумент опущен, то возвращается номер строки / столбца, в котором записана 31

1. 2. ЭЛЕМЕНТЫ АВТОМАТИЗАЦИИ ПРИ РАБОТЕ С ТАБЛИЦАМИ 32

Таблица 1 A B 1 2 3 4 5 6 7 8 9 C D E F G Список сотрудников Фамилия, Дол Окл Инициалы жност ад ь Прем Про Подо К ия ф. ходны выда взнос й налог че а н ы руки 33

Модуль ПРОВЕРКА Таблица 2 A 1 Должности 2 Директор 3 4 5 Зам_ Директора Бухгалтер Главный_Инж енер 6 Инженер 7 Программист 34

Создание списков перечислений 1. Создайте список должностей и присвойте ему имя “Должности”. 2. Выделите область таблицы (ячейки) куда будите помещать данные, например, В 3: В 9; 3. Введите команду Данные, Проверка. В окне диалога Проверка вводимых значений откройте список “Тип данных” и выберите Список, активизируйте строку Источник и нажмите кнопку F 3, для вызова списка имен ячеек, или введите 35

Использование флажков и переключателей В каждую строку столбца “Проф. взносы” вставьте элемент управления “Флажок”. Для обеспечения связи его с ячейкой вызовите контекстное меню кнопки, Введите команду Формат ячейки. В окне диалога Формат ЭУ в строке Связь с ячейкой укажите адрес ячейки. Теперь при активизации флажка в ячейке появится строка ИСТИНА, а при снятии флажка – строка ЛОЖЬ. 36

Установление связи флажка с ячейкой 37

3. ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ ФУНКЦИЙ ПОИСКА ДАННЫХ 38

Постанвка задачи Дано: три таблицы Требуется: организовать управляемое пользователем построение диаграммы из любой таблицы на одном месте. Управление организовать с помощью переключателей. Исходные данные должен выглядеть следующим образом: 39

40

Итоговый документ должен выглядеть следующим образом: 41

Порядок работы выполнения: 1. Открыть новую книгу и сохранить ее на рабочем диске. 2. Разместить исходные данные на трех листах. 3. Добавить новый лист с помощью контекстного меню ярлычка листа для диаграммы. 4. Открыть панель инструментов “Элементы управления”. 5. На лист Диаграмма поместить рамку и вставить в нее три переключателя в соответствии с рис. . 6. Установить связь переключателей с 42

Вставка рамки 1. Открыть панель инструментов Элементы управления: Вид, Панели инструментов. 2. Выбрать кнопку Другие элементы (внизу, справа). 3. Найти в списке ЭУ Microsoft Forms 2. 0 Frame

Установка переключателей Установить на рамку переключатели. Вызвать свойства объектов и изменить их имена. Связать переключатели с ячейкой A 1.

7. В ячейках В 10: L 11 разместить таблицу из двух строк. В первую строку поместить заголовок, аналогичный заголовку таблиц данных, а во вторую строку поместить формулы для выборки данных из таблиц в соответствии с выбранной командой. В ячейку G 8 пометите формулу для вывода заголовка в 45 соответствии с выбранной

Формула в ячейке G 8 В ячейку G 8 введите формулу: ЕСЛИ(А 1=1; ”Численность населения мира”; ЕСЛИ(А 1=2; ”Промышленное производство”; ”Сельскохозяйственное производство”)). 46

Формула в ячейке G 8 Для создания заголовка можно использовать также функцию ВЫБОР(): =ВЫБОР(А 1; "Население"; "Промы шленность"; "Сельское хозяйство"). 47

Создание списка Создайте список “Страны”. Для этого воспользуйтесь списком из любой таблицы данных. В ячейку В 11 введите формулу =ПРОВЕРКА и создайте список Страны.

Формула в ячейке С 11 =Если( $A$1=1; ВПР ($B$11; Население!$B$5: $L$8; ПОИСКПОЗ (C 10; Население!$B$4: $L$4; 0); Если( $A$1=2; ВПР ($B$11; Промышлен!$B$5: $L$8; ПОИСКПОЗ (C 10; Промышлен!$B$4: $L$4; 0); ВПР ($B$11; Сельск/хоз!$B$5: $L$8; ПОИСКПОЗ (C 10; Сельск/хоз!$B$4: $L$4; 0) )) Скопировать формулу в остальные 49

Формула в ячейке С 11 В рассмотренном примере номер столбца можно определить, используя функцию СТОЛБЕЦ(). =Если( $A$1=1; ВПР ($B$11; Население!$B$5: $L$8; СТОЛБЕЦ()-1; 0); Если( $A$1=2; ВПР ($B$11; Промышлен!$B$5: $L$8; СТОЛБЕЦ()1; 0); ВПР ($B$11; Сельск/хоз!$B$5: $L$8; СТОЛБЕЦ()- 50

Построение диаграммы Постройте столбиковую диаграмму на основе данных строк B 10 -L 11.