Основы компьютерной грамотности. Базовый учебный курс

Подождите немного. Документ загружается.

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

ном направлении используют кнопку Назад.

При поиске можно комбинировать

различные критерии (рис. 3.77). На-

пример, можно вести поиск элемен-

тов, у которых значение одного поля

больше заданного числа, а значение

другого совпадает с некоторой после-

довательностью символов. Если кри-

терием служит текстовая строка, то

для маскирования текстовых позиций

можно использовать символы ? и *.

Вопросительный знак маскирует ту

позицию, которую он занимает. Усло-

вию А??а в поле

Имя в рассматривае-

мом примере будут удовлетворять две

записи.

Звездочка * маскирует все после-

дующие символы. Например, условию учитель* или у* в поле Должность

будут удовлетворять все строки, кроме одной. Попробуйте это проверить.

В числовых критериях поиска можно использовать логические опера-

торы. Например, условие >2000, внесенное в поле Оклад, означает, что

нужно найти элементы списка, значение которых превышает 2000. Убеди-

тесь в этом.

Выбрав нужную запись, ее можно удалить (щелкнуть по кнопке Удалить)

или отредактировать (щелкнув по кнопке Правка).

3.6.4.2. Сортировка данных

Традиционный способ обработки данных списков – сортировка – мо-

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

необходимо выбрать меню Данные → Сортировка. В открывшемся диалого-

вом окне Сортировка диапазона следует выбрать поля и направление сорти-

ровки – по возрастанию или убыванию.

Выполните сортировку для нашей задачи, отсортировав данные по

Окладу (по возрастанию) и Фамилии (в алфавитном порядке, то есть по воз-

растанию). На рис. 3.78 и 3.79 представлены диалоговое окно сортировки и

результат сортировки.

После сортировки можно получить список с подведением промежу-

точных и общих итогов. Для этого следует выбрать в меню

Данные →

Итоги.

260

Модуль 3. Работа с электронными таблицами

Рис. 3.77. Поиск информации

по критериям

modul_3_2.qxd 1/11/07 1:12 PM Page 260

В нашем примере произведем сначала сортировку по полю Оклад,

а затем просуммируем оклады при каждом переходе на другую катего-

рию. На рис. 3.80 и 3.81 показаны диалоговое окно и результат подведе-

ния итогов.

3.6. Базы данных в электронных таблицах

261

Рис. 3.78. Сортировка данных в списке

Рис. 3.79. Результат сортировки в списке

modul_3_2.qxd 1/11/07 1:29 PM Page 261

262

Модуль 3. Работа с электронными таблицами

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

Рис. 3.81. Промежуточные итоги

modul_3_2.qxd 1/11/07 1:32 PM Page 262

3.6.4.3. Фильтрация данных

Автоматический фильтр позволяет создать новый список, в который бу-

дут включены элементы исходного списка, удовлетворяющие некоторому

критерию.

Для создания такого списка следует выбрать меню Данные → Фильтр →

Автофильтр. В таблице рядом с названиями полей появятся пиктограммы

со стрелками. Если щелкнуть по стрелке, то появится перечень значений

данного поля. Выберите одно значение из этого списка, тогда в нем оста-

3.6. Базы данных в электронных таблицах

263

Рис. 3.82. Создание Пользовательского автофильтра

Рис. 3.83. Результат фильтрации данных из списка

modul_3_2.qxd 1/11/07 1:35 PM Page 263

нутся только те элементы, у которых значение этого поля совпадает с вы-

бранным. Если выбрать пункт Условие, то можно задать логическое усло-

вие поиска, сложные условия поиска можно составлять, используя логиче-

ские функции И и ИЛИ. Например, на рис. 3.82, 3.83 осуществлена выбор-

ка данных, удовлетворяющих критерию Оклад больше или равен 1800.

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

3.6.5.1. Объемные сводные таблицы

Сводные таблицы как способ обработки и представления данных удоб-

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

для анализа соотношений между данными в списке.

Сводная таблица – это плоская или объемная (состоящая из нескольких

страниц) прямоугольная таблица, позволяющая просуммировать или иным

образом подытожить большие объемы данных, расположенных в разных

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

одного или нескольких полей исходного списка, объявленных как поля

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

для группировки данных в строках и столбцах сводной таблицы.

Для создания сводной таблицы выбирается меню Данные → Сводная таб-

лица. Эта команда открывает диалог Мастера сводных таблиц и диаграмм.

Шаг 1 (рис. 3.84) предусматривает выбор источника данных (в нашем

случае это список MS Excel).

264

Модуль 3. Работа с электронными таблицами

Рис. 3.84. Шаг 1 работы Мастера сводных таблиц и диаграмм

modul_3_2.qxd 1/11/07 1:36 PM Page 264

Шаг 2 – следует указать диапазон, содержащий исходные данные

(рис. 3.85).

Шаг 3 – определение места расположения сводной таблицы (рис. 3.86).

Этот шаг позволяет определить структуру создаваемой сводной таблицы

щелчком по кнопке Макет.

3.6. Базы данных в электронных таблицах

265

Рис. 3.85. Шаг 2 работы Мастера сводных таблиц и диаграмм

Рис. 3.86. Шаг 3 работы Мастера сводных таблиц и диаграмм

modul_3_2.qxd 1/11/07 1:40 PM Page 265

В открывшемся диа-

логовом окне (рис. 3.87)

в правой части находятся

кнопки с названиями

всех полей списка. Сле-

дует отбуксировать мы-

шью те из них, которые

необходимы в сводной

таблице. В поле данных

помещаются поля, по

которым будет произво-

диться суммирование, а

в полях строк и столб-

цов – поля для группи-

ровки.

В нашем примере пе-

реместим кнопку Категория в поле Строка, а Оклад – в поле Данные. Щелк-

нем по кнопке ОК, а затем в предыдущем окне – кнопку Готово.

Итак, мы получили сводную таблицу. Для ее усовершенствования

предназначены окно макета и панель инструментов Сводная таблица

(рис. 3.88).

266

Модуль 3. Работа с электронными таблицами

Рис. 3.87. Создание макета сводной таблицы

Рис. 3.88. Сводная таблица

modul_3_2.qxd 1/11/07 1:43 PM Page 266

3.6.5.2. Дополнительная группировка в сводных таблицах

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

определенным полям. Покажем это на примере.

Создайте список из данных (рис. 3.89).

Создадим на основе этого списка сводную таблицу, группируя данные

по названиям и цене (строки) и году издания (столбцы), производя сумми-

рование по полю

Покупка (рис. 3.90).

3.6. Базы данных в электронных таблицах

267

Ó÷åáíèêè ïî ìàòåìàòèêå 90 140 1997

Ó÷åáíèêè ïî ìàòåìàòèêå 80 100 1998

Ó÷åáíèêè ïî ëèòåðàòóðå 140 110 1997

Ó÷åáíèêè ïî ìàòåìàòèêå 80 36 1999

Ó÷åáíèêè ïî ëèòåðàòóðå 140 67 1998

Ó÷åáíèêè ïî ôèçèêå 150 45 1997

Ó÷åáíèêè ïî ôèçèêå 170 60 1998

Ó÷åáíèêè ïî ôèçèêå 150 70 1999

Ó÷åáíèêè ïî ëèòåðàòóðå 115 45 1999

Ó÷åáíèêè ïî ôèçèêå 150 100 2000

Ó÷åáíèêè ïî ëèòåðàòóðå 130 110 2000

Ó÷åáíèêè ïî ìàòåìàòèêå 90 85 2000

Название Цена Количество Год

Рис. 3.89. Список Учебники для школьной библиотеки

Рис. 3.90. Сводная таблица Учебники для школьной библиотеки

Ó÷åáíèêè Öåíà 1997 1998 1999 2000 Îáùèé èòîã

Ïî ëèòåðàòóðå 115 45 45

130 110 110

140 110 67 177

Итог 110 67 45 110 332

Ïî ìàòåìàòèêå 80 100 36 136

90 85 85

Итог 100 36 85 221

Ïî ìàòåìàòèêå 90 140 140

Èòîã 140 140

Ïî ôèçèêå 150 45 70 100 215

170 60 60

Итог 45 60 70 100 275

Îáùèé èòîã 295 227 151 295 968

Сумма по полю Количество Год издания

modul_3_2.qxd 1/11/07 1:44 PM Page 267

Далее произведем группировку по году изда-

ния, объединяя их по два (с шагом 2). Для этого

выделим заголовок поля Год, затем выполним

команду Данные → Группа и структура → Группиро-

вать. Откроется диалоговое окно группировки

(рис. 3.91).

Задайте в нем шаг 2 и щелкните по кнопке ОК.

Получится сводная таблица, в которой данные

сгруппированы по интервалам лет (рис. 3.92).

Если нужно получить не сумму по полю Количество (она задается Мас-

тером автоматически), а другую итоговую функцию, например, максималь-

ное количество учебников, купленных по каждой цене, щелкните дважды

по названию поля Цена, появится диалоговое окно Вычисление поля свод-

ной таблицы (рис. 3.93), в котором следует выбрать функцию Максимум.

268

Модуль 3. Работа с электронными таблицами

Рис. 3.91. Диалоговое

окно Группирование

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

Учебники Цена 1997−1998 1999−2000 Общий итог

Ïî ëèòåðàòóðå 115 45 45

130 110 110

140 177 177

Итог 177 155 332

Ïî ìàòåìàòèêå 80 100 36 136

90 85 85

Итог 100 121 221

Ïî ìàòåìàòèêå 90 140 140

Итог 140 140

Ïî ôèçèêå 150 45 170 215

170 60 60

Итог 105 170 275

Îáùèé èòîã 522 446 968

Сумма по полю Количество Год издания

Рис. 3.93. Выбор итоговой функции Максимум

modul_3_2.qxd 1/11/07 1:44 PM Page 268