zanyatieExcel_3

Занятие 3

Microsoft Excel

11. Сохраните полученный результат как лист Функция ВПР.

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

Синтаксис

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

•Искомое значение — значение, которое требуется найти в первой строке таблицы.

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

•Таблица — таблица или массив констант с информацией, в которой производится поиск данных. Можно использовать ссылку на интервал или имя интервала. Значения

впервой строке аргумента «таблица» могут быть текстовыми, числовыми или

логическими.

-Если аргумент «интервальный просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны быть расположены в возрастающем порядке: ...-2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если же аргумент «интервальный просмотр» имеет значение ЛОЖЬ, сортировка для аргумента «таблица» не обязательна.

-Текстовые строки считаются эквивалентными независимо от регистра букв.

-Сортировка значений в порядке возрастания, слева направо.

•Номер строки — номер строки в массиве «таблица», из которой берется значение. Если значение аргумента «номер строки» равно 1, то берется значение из первой строки аргумента «таблица», если оно равно 2 — берется из второй строки и т. д. Если значение аргумента «номер строки» меньше 1, функция ГПР показывает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», показывается значение ошибки #ССЫЛ!.

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

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

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

-Если аргумент «интервальный просмотр» имеет значение ЛОЖЬ и аргумент «искомое значение» является текстом, в аргументе «искомое значение» можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому

В.М.Самохвалов

21

Занятие 3

Microsoft Excel

знаку; звездочка — любой последовательности знаков. Чтобы найти какойлибо из самих этих знаков, следует поставить перед ними знак тильды (~).

Упражнение «Использование функции ГПР»

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

1.Откройте Функция ВПР книги Учебная база1 и скопируйте его в конец книги.

По умолчанию этот лист будет называться Функция ВПР (2). 2. Подготовьте на этом листе диапазон новых записей, введя:

В ячейки А51-H51 соответственно

Фамилия Имя Отчество Адрес Телефон Дата рождения Род занятий Дата приема

3.В ячейку А52 введите формулу =ГПР(B41;A41:B48;1;ЛОЖЬ).

Вней отобразится фамилия Арутюнова

4.В ячейку B52 введите формулу = ГПР(B41;A41:B48;2;ЛОЖЬ).

В ней отобразится имя Екатерина

5. В ячейку C52 введите формулу =ГПР(B41;A41:B48;3;ЛОЖЬ)..

В ней отобразится отчество Сергеевна

6. В ячейку D52 введите формулу = ГПР(B41;A41:B48;4;ЛОЖЬ).

В ней отобразится адрес ул. Галкина, 1-23

7. В ячейку E52 введите формулу = ГПР(B41;A41:B48;5;ЛОЖЬ).

В ней отобразится телефон 12-34-56

8. В ячейку F52 введите формулу = ГПР(B41;A41:B48;6;ЛОЖЬ).

В ней отобразится дата рождения 09.09.1932

9. В ячейку G52 введите формулу = ГПР(B41;A41:B48;7;ЛОЖЬ).

В ней отобразится род занятий пенсионер

10. В ячейку H52 введите формулу = ГПР(B41;A41:B48;8;ЛОЖЬ).

В.М.Самохвалов

22

Занятие 3

Microsoft Excel

В ней отобразится дата приема.

Лист примет вид:

Рисунок 23. Результаты выполнения функции ВПР.

Таким образом, данные пациента вновь приобрели журнальную форму (преобразованы из карточной формы записи).

11. Сохраните полученный результат как лист Функция ГПР.

Добавление итогов

Упражнение «Добавление итогов в базу данных».

Чтобы посчитать общее число принятых пациентов:

1. Откройте лист1 книги Учебная база1.

2.В ячейку А37 введите

Всего пациентов

3.В ячейку С37 вставьте функцию СчётЗ используя Мастер функций:

В.М.Самохвалов

23

Занятие 3

Microsoft Excel

Рисунок 24. Вставка функции СчётЗ.

Функция СЧЁТЗ подсчитывает количество ячеек в диапазоне, содержащих данные любого типа (это может быть текст, число, дата, пробел и даже значение ошибки).

С помощью функции СЧЁТЗ можно подсчитать количество непустых ячеек

Вполе Значение 1 диалогового окна Аргументы функции введите диапазон ячеек А2:А36.

4.Нажмите кнопку ОК диалогового окна Аргументы функции.

Вячейке С37 появится число

35.

Упражнение «Добавление промежуточных итогов в базу данных».

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

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

25)

В.М.Самохвалов

24

Занятие 3

Microsoft Excel

Рисунок 25. База данных, отсортированная по полю Род занятий

2.Сделайте копию лист1 книги Учебная база1 и разместите её в этой же книге сразу за листом Функция ГПР.

3.Для выделения базы щелкните в любом месте базы данных на листе лист1(2) , т.е. диапазона ячеек A1:G36.

4. В пункте меню Данные (в Excel 2007 вкладка Данные) щелкните на команде Итоги (в Excel 2007 по кнопке Промежуточные итоги группы

Структура).

Откроется диалоговое окно:

В.М.Самохвалов

25

Занятие 3

Microsoft Excel

Рисунок 26. Диалоговое окно Промежуточные итоги

5. В окошках (выпадающих списках) диалогового окна Промежуточные итоги установите значения:

При каждом изменении в: – Род занятий

Операция:	– Количество,
а флажками отметьте:
Добавить итого по:	– Род занятий
	– Итоги под данными

6.Нажмите кнопку ОК диалогового окна Промежуточные итоги.

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

При этом значение ячейки G42 Общее количество совпадает с ранее подсчитанным значением Всего пациентов ячейки С41.

В.М.Самохвалов

26

Занятие 3

Microsoft Excel

Рисунок 27. Количество принятых пациентов по каждому роду занятий

7.Переименуйте лист 1(2) в лист РодЗанятий и сохраните книгу Учебная

база1.

Упражнение «Консолидация данных».

Консолидация данных это сбор и объединение данных из разных исходных областей. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах. Существует несколько способов консолидации.

Рассмотрим консолидацию с помощью трехмерных ссылок, что является наиболее предпочтительным способом.

В.М.Самохвалов

27

Занятие 3

Microsoft Excel

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

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

Создайте таблицу и диаграмму количества принятых пациентов по каждому роду занятий по дням второй недели сентября 2011 г. Исходными данными будет являться книга

ЖурналПриема.

1.Откройте Лист1 новой книги.

2.Подготовьте макет таблицы для ввода консолидированных данных:

Род занятий

05.09.2011 06.09.2011 07.09.2011 08.09.2011 09.09.2011

инвалид

пенсионер

работающий

студент

Всего пациентов

3. Отформатируйте макет по образцу, Рисунок 28:

Рисунок 28. Формат макета таблицы.

Теперь установите в этой таблице ссылки на соответствующие ячейки исходных таблиц

4.Откройте книгу ЖурналПриема и перейдите в консолидированную таблицу.

5.Выделите ячейку В2 таблицы.

6.В строке формул поставьте знак " = ".

7.Перейдите на лист 05092011 книги ЖурналПриема, где находятся исходные данные для этого столбца.

8.Щелкните ячейку G5, Рисунок 29.

9.Нажмите клавишу Enter или кнопку Ввод строки формул.

В ячейку В2 вставится ссылка на ячейку G5 листа 05092011 книги

ЖурналПриема.

В.М.Самохвалов

28

Занятие 3

Microsoft Excel

10.Проделайте ту же самую операцию для ячеек В3-В6, вводя в них ссылки на ячейки G9, G14, G19, G20 листа 05092011 книги ЖурналПриема.

11.Заполните аналогичным образом столбец С (С2-С6) таблицы, используя в качестве источника данных лист 06092011 книги ЖурналПриема.

12.Далее заполните столбец D (D2-D6) таблицы, используя в качестве источника данных лист 07092011 книги ЖурналПриема.

13.Заполните столбец E (E2-E6) таблицы, используя в качестве источника данных лист 08092011 книги ЖурналПриема.

Рисунок 29. Ввод трехмерной ссылки.

14.Наконец, заполните столбец F (F2-F6) таблицы, используя в качестве источника данных лист 09092011 книги ЖурналПриема.

Консолидированная таблица примет вид:

В.М.Самохвалов

29

Занятие 3

Microsoft Excel

Родзанятий	05.09.2011	06.09.2011	07.09.2011	08.09.2011	09.09.2011
инвалид	3	2	4	0	4
пенсионер	3	4	7	1	7
работающий	4	7	14	14	14
студент	4	7	0	10	10
Всегопациентов	14	20	25	25	35

Рисунок 30. Таблица, консолидированная с помощью трехмерных ссылок.

15. Сделайте диаграмму по этой таблице. Она должна выглядеть так:

Прием пациентов с 5 по 9 сентября 2011

40

35

30

25

20

15

10

5

0

05.09.2011	06.09.2011		07.09.2011	08.09.2011	09.09.2011
инвалид			пенсионер	работающий
студент			Всегопациентов

Рисунок 31. Диаграмма Прием пациентов на второй неделе сентября.

16.Переместите лист1 открытой книги в книгу Учебная база1 после листа РодЗанятий, переименуйте его в Консолидация и сохраните книгу

Учебная база1.

В.М.Самохвалов

30