Создание оформленных таблиц в Microsoft Excel

Основным способом создания оформленной таблицы на листе является использование кнопки « Таблица» в группе Таблица вкладки Вставка ленты инструментов. В появившемся диалоговом окне (рис. 14.1) следует задать диапазон ячеек, которые образуют таблицу. Если в первой строке выделенного диапазона содержатся ячейки с заголовками столбцов, следует установить флажок «Таблица с заголовками» – тогда первая строка получит статус строки заголовков полей таблицы. Если первая строка не содержит заголовков, флажок нужно снять: присвоение статуса строки заголовков полей первой строке с данными (записи) некорректно и бессмысленно.

Другой способ создания оформленной таблицы – преобразование в нее уже заполненных и отформатированных ячеек с данными. Для этого следует выделить ячейки и применить к ним любой из стилей таблиц (см. Лабораторную работу №10). Будет также открыто окно рис. 14.1. Следует проверить диапазон ячеек и, при необходимости, скорректировать.

После нажатия кнопки «ОК» в окне рис. 14.1диапазон ячеек будет преобразован в оформленную таблицу:

Ø Весь диапазон ячеек, образующих таблицу, будет поименован (см. Лабораторную работу №11). Имя таблицы по умолчанию – Таблица N;

Ø К ячейкам диапазона будет применено форматирование в соответствии с выбранным (или текущим) стилем таблицы;

Ø Если была создана таблица с заголовками, и строка заголовков не помещается на экране, то эти заголовки будут отображаться вместо стандартных заголовков столбцов Microsoft Excel;

Ø К заголовкам столбцов будет применен автофильтр (см. ниже);

Ø Правый нижний угол оформленной таблицы будет иметь вид «». Перетаскиванием этого маркера мышью можно изменять количество ячеек в таблице – как в сторону увеличения, так и в сторону уменьшения;

Ø При выделении любой из ячеек таблицы будут отображаться новая группа вкладок «Работа с таблицами» с единственной вкладкой Конструктор (рис. 14.2, в дальнейшем – «Конструктор таблиц»).

Рис. 14.2 Вкладка конструктора оформленных таблиц

На одном листе можно создать несколько оформленных таблиц.

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

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

Кнопка « Размер таблицы» в группе Свойства вкладки Конструктор таблиц откроет диалоговое окно, единственным элементом которого будет поле со ссылкой на диапазон ячеек, который можно отредактировать.

Для отключения режима оформленной таблицы служит кнопка « Преобразовать в диапазон» в группе Сервис вкладки Конструктор таблиц.

Вкладка Конструктор таблиц (рис. 14.2) содержат также ряд инструментов для обработки при помощи таблиц данных, импортируемых из других программ или экспортируемых в другие программы (посредством языка XML), связывания таблиц в документах Microsoft Excel с таблицами данных, хранящимися на серверах Microsoft. Данные функции в пособии рассматриваться не будут.

В следующих разделах Лабораторной работы представлен ряд дополнительных инструментов для обработки данных. Большинство этих инструментов предназначены для работы с оформленными таблицами. Если же не хочется создавать оформленные таблицы на листе, для эффективной работы с такими инструментов можно оформить ячейки в виде базы данных (в версиях Microsoft Excel до 2003 именно такое оформление называлось списком – предшественником таблиц Microsoft Excel 2010).

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

Ø Диапазон непрерывен и изолирован, т.е. внутри диапазона нет полностью пустых строк или столбцов, а от других данных на листе диапазон отделен как минимум одной пустой строкой или столбцом;

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

Ø Столбцы диапазона содержат данные одного типа (числовые, текст, дата, см. Лабораторную работу №10); объединение ячеек не допускается.

Функции для работы с оформленными таблицами и базами данных

Для проведения вычислений над данными, которые оформлены в виде базы данных или таблицы, предназначены специальные функции работы с базами данных (табл. 14.1). Функция БИЗВЛЕЧЬ извлекает отдельное значение из столбца списка, удовлетворяющее заданным условиям, остальные функции данной категории имеют аналоги в других категориях: БДСУММ – СУММ, ДСРЗНАЧ – СРЗНАЧ, ДМИН – МИН, ДМАКС – МАКС, БДСЧЁТ – СЧЁТ, БДСЧЁТА – СЧЁТЗ, БДПРОИЗВЕД – ПРОИЗВЕД и др. В отличие от аналогов функции для работы с базами данных всегда имеют три аргумента: База_данных – весь диапазон ячеек (включая строку с названиями полей); Поле – имя поля, заключенное в кавычки, порядковый номер столбца в диапазоне или адрес ячейки с именем поля; Критерий – адрес д иапазона условий, в котором содержатся условия, позволяющие отобрать для расчета только часть записей базы данных или списка. Правила создания диапазона условий (критериев) будут рассмотрены ниже.

Табл. 14.1