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

Лекция 2 (Функции поиска).ppt

  • Количество слайдов: 51

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

40 40

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

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

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

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

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

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

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

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

Формула в ячейке С 11 =Если( $A$1=1; ВПР ($B$11; Население!$B$5: $L$8; ПОИСКПОЗ (C 10; Формула в ячейке С 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 В рассмотренном примере номер столбца можно определить, используя функцию Формула в ячейке С 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. Построение диаграммы Постройте столбиковую диаграмму на основе данных строк B 10 -L 11.