Работа с данными составляет 2-й этап технологии применения ТБП, а объединение таблиц можно осуществить путем их связы-вания. Поскольку зачастую разные таблицы размещаются на разных листах, то говорят не о связывании таблиц, а о связывании листов.
Две таблицы называются связанными, когда в ячейках одной из них присутствуют ссылки на ячейки другой.
Пример. Пусть таблица "Прайс-лист" содержит цены на прода-ваемые книги, а таблица "Калькуляция" – стоимость конкретной закупки. Обе таблицы принадлежат одной книге PRICEL.xls, и размещены на Лист 1 и Лист 2, соответственно. Стоимость на Лист 2 в таблице "Калькуляция" вычисляется с использованием значения цены на Лист 1. Обращение к ячейке из таблицы "Прайс-лист" состоит из имени листа и адреса ячейки, разделенных восклицательным знаком: Лист 1!В4. В итоге формулы имеют вид:
=В3*Лист 1!В4 и =В4*Лист 1!В3
Если ссылка на ячейку осуществляется из другой книги, то в ссылку добавляется полное имя книги, то есть путь к файлу и имя файла, При этом имя книги заключается в квадратные скобки, а полное имя файла в апострофы. В случае с ценой имеем: если в каталоге EXCEL на диске С, то
=В3*'C:\ EXCEL\[ PRICEL.xls]Лист 1'!В4
Сортировка данных
Пример. Прайс-лист.
В случае, когда рассматриваемая таблица имеет большие размеры, работа с ней становится затруднительной. В связи с этим в Excel предусмотрены команды, облегчающие эту работу, в частности, команды сортировки и фильтрации.
Под сортировкой данных, размещенных в таблице, понимается упорядочивание строк таблицы по значениям некоторых ее столбцов. Сортировка упрощает последующий анализ и поиск данных и реализуется командой ДАННЫЕ ® СОРТИРОВКА.
Фильтрация данных
Фильтрация позволяет находить некоторое подмножество строк таблицы, удовлетворяющее определенным условиям. Для фильтрации может быть использована команда ДАННЫЕ ® ФИЛЬТР ® АВТОФИЛЬТР. В результате выполнения этой команды клетки с названиями столбцов преобразуются в раскрывающиеся списки, в которых можно задавать нужные условия для поиска данных. Пример. Прайс-лист.
 |
Сводные таблицы
Сводная таблица является одним из самых мощных средств Excel, которое применяется для получения компактных итоговых таблиц на основании громоздких исходных таблиц.
Пример. (учебный) Объем продаж ПК на мировом рынке.
Рассмотрим следующую ситуацию, Фирма собирает статистическую информацию о продаже персональных компьютеров (ПК), получает ее из различных источников и отнюдь не в хронологическом порядке. Данные заносятся в рассматриваемую таблицу по мере поступления, В результате в ней может быть большое количество записей относительно одной и той же фирмы, приходящихся на один и тот же год и даже квартал. Задача состоит в вычислении суммарного объема поквартальной продажи ПК для каждой фирмы. Пусть исходная таблица имеет вид:
Сначала получим результат вручную, а затем кратко охаракте-ризуем компьютер-ную технологию. Ре-зультат представляет-ся в виде 2-х таблиц, одна из которых будет отражать объем продаж в 1997 г., другая – в 1998 г. Строки этих таблиц будут помечены названиями фирм, а столбцы номерами кварталов. По первому кварталу относительно фирмы Compaq в исхо-дной таблице есть две записи, поэтому объемы продаж из этих записей должны быть просумми-рованы. По 2-му кварта-лу записей относительно фирмы Compaq нет. Для фирмы IBM существует запись только по 2-му кварталу. По 1998 г. в исходной таблице существует лишь одна запись относительно фирмы IBM. Полученные 2 таблицы называются сводной таблицей. Такие таблицы в Excel формируются автоматически. В рассмотренном примере данные по продаже за один квартал суммировались, однако вместо суммирования могут быть использованы и другие операции, например, определение максимального, минимального или среднего значения. Следует обратить внимание, что при построении сводной таблицы попутно осуществляется и фильтрация данных, проявляющаяся в выделении в особую таблицу данных, относящихся к одному году.
Технология создания сводной таблицы в Excel поддерживается Мастером сводных таблиц. Эта программа реализует построение сводной таблицы за 4 шага, наиболее важным среди которых являе-тся 3-й. На этом шаге определяется макет будущей сводной табли-цы. Диалоговое окно этого шага имеет сле-дующий вид. В правой части окна расположе-ны кнопки с на- званиями столбцов исходной таблицы.
В левой части окна располагается макет будущей сводной таблицы, состоящей из 4-х полей для ввода: строка страница, столбец и данные. Определение структуры сводной таблицы заключается в соотнесении названий столбцов исходной таблицы с полями макета. Техника соотнесения – перетаскивание мышью названий столбцов в поле макета.