5. Функции для работы со списками

Библиотека Excel содержит тринадцать встроенных функций списков (баз данных), позволяющих получить информацию из списка или произвести в нем необходимые вычисления. При этом некоторые из этих функций соответствуют уже известным Excel-функциям таблицы (например, БДСУММ(Список; Поле;

Критерий), БСЧЕТ(Список; Поле; Критерий), ДМАКС(Список; Поле; Критерий) и

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

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

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

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

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

6. Сводные таблицы

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

Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц. Перед построением сводной таблицы необходимо убрать все ранее созданные

промежуточные итоги и наложенные фильтры.

1. Установите курсор в любую ячейку списка и выберите команду СВОДНАЯ

ТАБЛИЦА из меню ДАННЫЕ.

2.В открывшемся диалоговом окне МАСТЕР СВОДНЫХ ТАБЛИЦ отметьте опцию "в списке или базе данных Microsoft Excel".

3.Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке ДАЛЕЕ.

4.В следующем окне определите значения каких полей списка будут использоваться в качестве заголовков строк (зона Строка), каких - в качестве заголовков столбцов (зона Столбец) и каких – в качестве данных (зона Данные), по которым следует подвести необходимые итоги (По умолчанию предлагается просуммировать значения выбранного поля. Для того чтобы изменить способ

79

обработки данных по этому полю необходимо дважды щелкнуть по образовавшемуся в зоне Данные полю и выбрать нужную операцию). В зону Страница помещается кнопка поля, по которому предполагается фильтровать данные. В каждой зоне может быть несколько кнопок. Для того, чтобы в новой таблице получить только итоговые значения следует все зоны, кроме зоны Данные оставить пустыми.

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

6.После нажатия на кнопку ГОТОВО, в указанном месте появляется таблица со сводными данными. В левом верхнем углу таблицы располагается кнопка с полем, помещенным в зону Страница. По умолчанию в таблице отображается вся информация по этому полю. Используя выпадающий список значений данного поля (в соседней справа ячейке) можно указать значение для фильтрации.

Используя панель инструментов СВОДНЫЕ ТАБЛИЦЫ можно изменять вид сводной таблицы.

6.1. Импорт данных в список Excel из внешних источников

Для того, чтобы применить к спискам, подготовленным например, с помощью текстового редактора (обширным прайс-листом, полученным по сети Интернет, или адресной книгой, списанной с компакт диска) все средства Excel, предназначенные для обработки баз данных, необходимо эти списки преобразовать в списки формата

Excel.

Такие преобразования существенно облегчает Мастер текста Excel:

1.Используя буфер обмена скопируйте из документа Word нужный текст и вставьте его на рабочий лист Excel.

2.После вставки все данные будут размещены в один столбец. Выделите весь занятый столбец.

3.Выберите команду ТЕКСТ ПО СТОЛБЦАМ из меню ДАННЫЕ.

4.После выбора формата данных в появившемся окне МАСТЕР ТЕКСТОВ (например, опции С РАЗДЕЛИТЕЛЯМИ) щелкните на кнопке ДАЛЕЕ и задайте вид разделителя (например, <запятую> и <пробел>).

5.На следующем шаге лучше всего установить опцию ОБЩИЙ (в этом случае числа будут отображаться как числа, даты как даты, а текст как текст).

6.Щелкните на кнопке ГОТОВО.

7.Добавьте заглавную строку и выполните необходимые операции форматирования.

Задание:

Выполните примеры VII.1-VII.3.

80

Контрольные вопросы

1.Можно ли зафиксировать на экране определенные строки или столбцы таблицы? Если – да, то что для этого нужно сделать?

2.В каких случаях может возникнуть необходимость создания нескольких окон для одного и того же открытого документа? Какими способами этого можно достичь?

3.Что такое форма данных?

4.В каких случаях предпочтительнее пользоваться формой данных?

5.Какие условия должны быть выполнены при создании списка (базы данных) в Excel?

6.Можно ли с помощью формы данных редактировать данные списка? Если – да, то что для этого нужно сделать?

7.Для чего предназначен инструмент Итоги?

8.Можно ли подводить промежуточные итоги сразу по нескольким функциям?

9.Какие действия нужно выполнить, чтобы после подведения итогов можно было бы увидеть только промежуточные результаты?

10.Как удалить результаты промежуточных итогов?

11.Для каких целей может быть использована фильтрация данных?

12.В каких случаях требуется фильтрация с использованием сложных критериев (расширенный фильтр)?

13.В каких случаях используется автофильтр

14.Как отобразить все данные после фильтрации?

15.Является ли обязательным наличие области критериев в расширенном фильтре?

16.Какое минимальное количество строк и столбцов может быть в области критериев?

17.Всегда ли результат расширенного фильтра будет отображаться на месте исходного диапазона?

18.Что может быть результатом выполнения функций для работы со списками?

19.В каких случаях при работе со списками могут быть использованы таблицы подстановки данных?

20.Для чего предназначен инструмент Сводные таблицы?

21.Можно ли в сводной таблице фильтровать данные? Если – да, то что для этого нужно сделать?

Упражнение 8

1.Загрузите файл: rashod.xls с сетевого диска obmen и сохраните на свой диск.

2.Используя форму данных добавьте в список данные об АО Престиж: 30.06.97, Материалы, $800, АО Престиж

3.Отсортируйте данные списка по дате.

4.Используя форму данных просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.02 на $2800.

81

5.Используя форму данных просмотрите все данные списка о расходах на материалы, превышающих $12000.

6.Используя автофильтр отобразите все данные списка по АО ИНВЕСТ.

7.Используя автофильтр отобразите все данные списка по накладным расходам, а затем накладные расходы за июнь.

8.Отмените автофильтр.

9.Присвойте имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

10.Зафиксируйте заглавную строку списка.

11.Вставьте перед диапазоном со списком 11 пустых строк.

12.В начале рабочего листа сформируйте область критериев для отображения с помощью расширенного фильтра всех данных списка по зарплате. Для этого в ячейку А1 скопируйте название столбца Расход, а в ячейку А2 поместите условие

–зарплата. В окне Расширенный фильтр указать в качестве исходного диапазона Список, в качестве диапазона условий диапазон ячеек А1:А2.

13.Измените область критериев для отображения всех данных списка по накладным расходам.

14.Измените область критериев, добавив в ее шапку 2 ячейки с названием Сумма или создайте новую, чтобы в результате выполнения расширенного фильтра отобразились накладные расходы в диапазоне от $500 до $1000.

15.Используя расширенный фильтр скопируйте в любую пустую область рабочего листа все данные списка о накладных расходах и зарплате за июль, предварительно изменив область критериев, либо создав новую. В качестве диапазона в котрый предполагается поместить результат достаточно указать первую (крайнюю левую) ячейку этого диапазона.

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

17.Измените область критериев, оставив в качестве критерия только вид расхода - зарплату.

18.Для заданного критерия отбора вычислите общую сумму:

•в ячейку В6 введите формулу расчета суммы, используя Мастер функции БДСУММ. Для указания диапазона базы данных выберите из списка имен ячеек (в левой части строки формул) имя соответствующего диапазона (Список), для задания Поля укажите с помощью мыши ячейку с названием поля Сумма, для задания диапазона критериев также воспользуйтесь мышью;

•в ячейку В5 введите текст Сумма по заданному критерию.

19.Используя функцию БСЧЕТ подсчитайте в ячейке С6 количество выданных зарплат. Имя Поля указываемого в окне Мастера оставьте прежним. В ячейку С5 введите текст Количество.

20.Подсчитайте сумму расходов и количество записей по материалам, изменив область критериев. (Область значений при этом изменится автоматически.)

82

21.Сформируйте таблицу для расчета суммы и количества расходов по каждому виду расхода, скопировав в ячейки А7, А8 и А9 значения поля Расходы: Материалы, Зарплата и Накладные расходы. В ячейках В6 и С6 рассчитайте общую сумму и количество расходов, очистив критерий поиска.

22.Для автоматической подстановки значений из ячеек А7, А8 и А9 в ячейку В2 области критериев и построчного получения результатов в сформированной таблице, выделите диапазон А6:С9 и выполните команду ТАБЛИЦА

ПОДСТАНОВКИ из меню ДАННЫЕ.

23.Сформируйте еще одну таблицу для расчета количества и суммы расходов, связанных с АО ИНВЕСТ, ТОО Надежда и ЗАО БИН, а также найдите максимальные и минимальные из них, воспользовавшись возможностью автоматической подстановки значений в область критериев для получения соответствующих результатов.

24.Измените в сформированной по предыдущему пункту таблице значения получателей на другие значения (например, скопировав на их место фамилии получателей). Область значений при этом изменится автоматически.

25.Сформируйте еще одну таблицу для расчета количества каждого из расходов по АО Престиж, ЗАО БИН и Васильевой М.Ф., для этого значения получателей запишите в разных строках под ячейкой с формулой, а значения расходов – в разных столбцах, правее ячейки с формулой. Выделите сформированную таблицу и выполните команду таблица подстановки из меню данные, указав в какую ячейку области критериев значения будут подставляться построчно, а в какую – из столбцов.

26.Создайте на новом рабочем листе сводную таблицу, позволяющую отобразить количество расходов, связанных с каждым получателем. Для этого поместите в область строк значения поля Получатель, а в область столбцов - значения поля Расход (в область данных нужно поместить поле Расход и щелкнув на нем 2 раза указателем мыши выбрать функцию для подсчета количества значений по этому полю).

27.Переместите поле Расход из области столбцов в область строк (для этого можно воспользоваться МАСТЕРОМ СВОДНЫХ ТАБЛИЦ, вызвав его либо с помощью соответствующей кнопки панели инструментов СВОДНЫЕ ТАБЛИЦЫ, либо выбрав соответствующую команду из контекстного меню).

28.На этом же рабочем листе, начиная с новой ячейки создайте еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов, связанных с каждым получателем, предусмотрев возможность фильтрации по полю Расход (поле Расход в этом случае нужно поместить в область страниц, а поле Получатель - в область строк).

•Отобразите данные по зарплате.

•Используя МАСТЕР СВОДНЫХ ТАБЛИЦ добавьте в область данных количество значений по полю Получатель.

•Отобразите данные по накладным расходам.

•Используя кнопку ОТОБРАЗИТЬ СТРАНИЦЫ панели инструментов СВОДНЫЕ ТАБЛИЦЫ отобразите информацию по каждому расходу.

83

29.На этом же рабочем листе, начиная с новой ячейки создайте еще одну сводную таблицу, позволяющую отобразить в один столбец сумму расходов по каждой

дате.

30.Сгруппируйте полученные данные по месяцам, воспользовавшись контекстным меню или соответствующей кнопкой панели инструментов СВОДНЫЕ ТАБЛИЦЫ.

Лабораторная работа №3

Загрузите с диска obmen файл Broker.xls и выполните, начиная с 3-го, предлагаемые в нем задания.

Лабораторная работа №4

Загрузите с диска obmen файл Кредит97.xls и выполните предлагаемые в нем задания.

		Содержание:
Тема I. Основы работы в среде табличного процессора MS Excel .....................			3
1.	Общие сведения о табличном процессоре Excel..........................................		5
2.	Рабочая книга Excel и ее элементы ...............................................................		6
2.1.		Понятие объекта, свойства, методы и события....................................	6
2.2.		Элементы окна приложения Excel.........................................................	7
3.	Настройка Excel...............................................................................................		9
3.1.		Настройка интерфейса Excel..................................................................	9
3.2.		Настройка параметров Excel ................................................................	11
4.	Операции с рабочей книгой.........................................................................		12
4.1.		Создание новой рабочей книги............................................................	12
4.2.		Открытие существующей рабочей книги...........................................	12
4.3.		Сохранение рабочей книги...................................................................	13
4.4.		Удаление файла рабочей книги...........................................................	13
5.	Операции над рабочими листами................................................................		14
5.1.		Связывание рабочих листов таблицы.................................................	15
6.	Операции с элементами рабочего листа.....................................................		15
7.	Ввод данных и формул в ячейки рабочего листа.......................................		16
7.1.		Выражения и операции Excel...............................................................	16
7.2.		Типы данных Excel................................................................................	18
7.3.		Ввод данных...........................................................................................	18
7.4.		Ввод формул..........................................................................................	19
7.5.		Организация ссылок..............................................................................	20
7.6.		Ввод специальных данных...................................................................	21
8.	Режимы вычислений.....................................................................................		22
8.1.		Выбор ручного или автоматического режима вычислений..............	22
8.2.		Вычисления с точностью как на экране..............................................	22
9.	Сортировка данных таблицы .......................................................................		22
10.	Корректировка табличных документов..................................................		23
10.1.		Перемещение по табличному документу ...........................................	23

84

10.2.		Выделение фрагментов таблицы.........................................................	24
10.3.		Редактирование данных........................................................................	24
10.4.		Копирование данных и формул...........................................................	25
10.5.		Автозаполнение.....................................................................................	26
11.	Оформление табличного документа........................................................		28
11.1.		Изменение ширины столбцов..............................................................	28
11.2.		Операции над длинным текстом ячейки.............................................	29
11.3.		Изменение высоты строки....................................................................	29
11.4.		Выбор шрифта.......................................................................................	29
11.5.		Ввод затенений и цветов ......................................................................	30
11.6.		Изменение вида выравнивания по горизонтали ................................	30
11.7.		Расположение текста по центру нескольких ячеек по горизонтали 31
11.8.		Изменение расположения текста относительно вертикали..............	31
11.9.		Изменение ориентации вывода текста................................................	31
11.10.		Обрамление элементов таблицы......................................................	31
11.11. Изменение формата вывода чисел...................................................			31
11.12.		Использование возможностей автоформатирования ....................	32
12.	Подготовка табличного документа к печати..........................................		32
12.1.		Предварительный просмотр таблицы перед печатью.......................	32
12.2.		Печать.....................................................................................................	33
Контрольные вопросы ..........................................................................................			34
Упражнение 1 ........................................................................................................			35
Задание для самостоятельной работы:................................................................			36
Тема II.		Построение диаграмм...........................................................................	36
1.	Работа с диаграммами...................................................................................		36
1.1.		Создание диаграмм ...............................................................................	36
1.2.		Объекты диаграммы..............................................................................	37
1.3.		Форматирование объектов...................................................................	37
1.4.		Изменение типа диаграммы.................................................................	37
1.5.		Добавление к диаграмме новых данных.............................................	38
2. Справочная система MS Excel .....................................................................			38
Контрольные вопросы ..........................................................................................			38
Упражнение 2 ........................................................................................................			39
Тема III.		Основные правила работы со встроенными функциями..................	40
1.	Использование встроенных функций Excel ...............................................		40

1.1.Суммирование ячеек, удовлетворяющих определенному критерию

41

1.2.	Подсчет количества значений в диапазоне ........................................	41
1.3.	Подсчет количества пустых ячеек в диапазоне .................................	42

1.4.Подсчет количества непустых ячеек в диапазоне, удовлетворяющих

заданному условию...................................................................................................		42
1.5.	Расчет среднего значения.....................................................................	42
1.6.	Определение максимального значения...............................................	42
1.7.	Определение минимального значения................................................	42
1.8.	Генерация случайного числа................................................................	43

85

	1.9.	Функции прогнозирования ...................................................................	43
	1.10.	Определение ранга числа .....................................................................	43
	1.11.	Определение процентной нормы числа ..............................................	44
	1.12.	Функции для работы с матрицами ......................................................	44
	1.13.	Функции даты и времени ......................................................................	45
	1.14.	Функция проверки условия ..................................................................	46
	1.15.	Использование функции И / ИЛИ .......................................................	46
	1.16.	Функция поиска данных в некотором диапазоне ..............................	47
	1.17.	Оценка ежемесячных выплат ...............................................................	48
Контрольные вопросы ..........................................................................................			49
Лабораторная работа №1......................................................................................			49
Тема IV.		Работа с именами ячеек . Использование примечаний ......................	49
1.	Работа с именами ячеек................................................................................		49
	1.1.	Создание имен .......................................................................................	49
	1.2.	Замена адресов ячеек их именами .......................................................	50
	1.3.	Вставка имен в формулы ......................................................................	50
2.	Использование примечаний.........................................................................		51
Задание для самостоятельной работы:................................................................			51
Контрольные вопросы ..........................................................................................			51
Упражнение 3 ........................................................................................................			51
Лабораторная работа №2......................................................................................			53
Тема V.		Анализ данных .......................................................................................	53
1.	Подбор параметра..........................................................................................		53
Упражнение 4 ........................................................................................................			54
2.	Таблицы автоматической подстановки данных.........................................		55
3.	Поиск решения...............................................................................................		58
Упражнение 5 ........................................................................................................			61
4.	Консолидация данных...................................................................................		62
	4.1.	Консолидация по расположению .........................................................	63
	4.2.	Консолидация по категориям ...............................................................	63
	4.3.	Удаление результатов ошибочной консолидации .............................	64
Задание:		..................................................................................................................	66
Контрольные ..........................................................................................вопросы			66
Упражнение ........................................................................................................6			66
Задание для ................................................................самостоятельной работы:			67
Тема VI. ....................................................................		Обмен данными в EXCEL	67
1.	Одновременная .........работа с несколькими табличными документами		67
2.	Обмен ........................................................................данными через буфер		68
3.	Динамический ..........................................................обмен данными DDE		68
4.	Связь .............................................и внедрение объектов OLE (Active-X)		69
Контрольные ..........................................................................................вопросы			69
Упражнение ........................................................................................................7			69
Тема VII. .................................		Работа со списками ( базами данных ) в EXCEL	72
1.	Общие ....................................принципы работы с большими таблицами		72
	1.1. ................................................................	Фиксация на экране титулов	72

86

	1.2. Создание нескольких окон для одного табличного документа........		73
	1.3. Разбиение документа на страницы вручную......................................		73
	1.4.	Печать повторяющихся заголовков.....................................................	73
2.	Работа со списками (базами данных)..........................................................		74
	2.1.	Создание списка (базы данных)...........................................................	74
	2.2. Поиск записей в списке ........................................................................		75
	2.3. Редактирование записей с помощью формы......................................		75
	2.4. Удаление записей с помощью формы.................................................		75
	2.5.	Добавление записей..............................................................................	75
3.	Подведение промежуточных итогов в списке............................................		76
4.	Фильтрация списка........................................................................................		77
	4.1. Выбор элементов списка с помощью автофильтра...........................		77
	4.2.	Фильтрация списка с использованием сложных критериев
(расширенный фильтр) .............................................................................................			78
5.	Функции для работы со списками...............................................................		79
6.	Сводные таблицы..........................................................................................		79
	6.1. Импорт данных в список Excel из внешних источников..................		80
Контрольные вопросы ..........................................................................................			81
Упражнение 8 ........................................................................................................			81
Лабораторная работа №3......................................................................................			84
Лабораторная работа №4......................................................................................			84

87