Формулы. Имена ячеек. Функции

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

Формулу ЭТ распознает по знаку равенства в начале информационной строки. Операндами арифметических выражений являются числа, простые переменные, массивы, функции. Для идентификации переменных, как было отмечено ранее, используются названия строк и столбцов, на пересечении которых расположены информационные ячейки. Именно с целью распознавания столбцы обозначаются буквами, а строки - цифрами.

Для адаптации идентификаторов ячеек в формулах при вставке новых или удалении существующих строк и столбцов используется абсолютная и относительная адресация. Первая отличается от второй наличием знака $ перед названием столбца или строки. Абсолютные ссылки удобно заменять более понятными именами практического содержания. Например, формула =$B$5*$B$6 малоинформативна, в то время как выражение " =ток*сопротивление " говорит гораздо больше.

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

ЭТ, как и все программы, выполняющие расчеты, имеют в своем распоряжении ряд функций, позволяющих расширить возможности анализа данных. Функции Excel можно разделить на ряд классов: финансовые, даты и времени, математические, статистические, баз данных, текстовые, логические и информационные. Какие конкретные функции имеются в каждом из этих классов, можно посмотреть в справке ЭТ. " Мастер функций " (клавиша " fx ") поможет вам выбрать и правильно оформить требуемую функцию.

В состав математического обеспечения Excel (папка Office), как правило, входит файл Funcs.xls, где можно найти достаточно подробную информацию о всех функциях Excel. Рекомендуем вам заглянуть в этот файл.

Диаграммы

Просила дева: "Отдохни,

На грудь мне голову склони!"

Но взор его слезой блистал,

И он со вздохом отвечал:

Excel sior!

Лонгфелло

 

Диаграммы позволяют повысить наглядность излагаемого в публикации материала и показать соотношение различных значений или динамику изменения ряда данных. Диаграмма может быть создана как на отдельном листе, так и в текущем рабочем листе. В первом случае необходимо выделить табличную область диаграммы и нажать F11. Это можно выполнить также с помощью “ Мастера диаграмм ”: (Вставка/Диаграмма / На отдельной странице). На текущем рабочем листе диаграмма строится с помощью “ Мастера диаграмм ” (имеется кнопка на инструментальной панели), который в диалоговом режиме предлагает делать выбор из стандартных сюжетов.

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

Рис. 1.2. Виды диаграмм

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

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

После выделения диаграммы (ЛК мыши) можно выделить какой-либо элемент диаграммы (область диаграммы, отдельный график, ось, название и др.). ПК мыши поможет вам выполнить то, что требуется.

Как правило, назначение типа относится ко всей диаграмме. Однако пользователь может задать разные типы для имеющихся рядов данных (диаграммы смешанного типа). Для этого на диаграмме щелчком мыши выделяется требуемый график (отмечается черными квадратиками). После нажатия ПК мыши выбирается " Тип диаграммы "и завершается диалог. Для того чтобы изобразить на диаграмме линию-тренд, необходимо выделить аппроксимируемую линию, после чего выполнить: ПК / Добавить линию тренда. Указать тип и параметры тренда.

Для удаления диаграммы достаточно выделить её однократным нажатием ЛК мыши, после чего нажать клавишу Del.

Функции списка

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

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

Формы данных

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

Фильтры

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

Автофильтр

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

Задание критерия. После выполнения последовательности меню Данные/Фильтр/Автофильтр на всех полях списка появляются кнопки, позволяющие фильтровать содержимое данного поля по всем записям (рис. 1.3). Выбор некоторого значения в поле фамилий, например “Волков”, позволяет вывести на экран только те записи, которые относятся к данной фамилии.

Рис. 1.3. Автофильтр

Рис. 1.4. Пользовательский автофильтр

Кроме рассмотренного простейшего критерия имеется возможность составить более сложный пользовательский автофильтр. При выборе параметра “Условие” появляется приведенная на рис. 1.4 диалоговая панель

Здесь возможно построение более сложных логических конструкций. При этом используются операции отношения: = (равно), > (больше), < (меньше), <> (не равно), <= (меньше или равно), >= (больше или равно), " начинается с ", " не начинается с ", " заканчивается на ", " не заканчивается ", " содержит ", " не содержит " (рис. 1.5). На рис. 1.4 второе условие означает вывод всех фамилий на буквы М-Я (операция больше относится к кодам букв, возрастающим по алфавиту).

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

С помощью команды Данные/Фильтр/Отобразить всё пользователь может восстановить отображение всех элементов списка. Однако эта команда не удалит кнопки, установленные на всех полях списка. Для того чтобы удалить эти кнопки, необходимо повторно выполнить Дан
ные/Фильтр/Автофильтр.

Рис. 1.5. Выбор условий и значений пользовательского автофильтра

Расширенный фильтр

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

Для вызова " Расширенного Фильтра " исполняется команда Данные/Фильтр/Расширенный фильтр. На экране появляется диалоговое окно
(рис. 1.6), где указываются область данных, область критериев и позиция, с которой должен записываться (если это требуется) модифицированный список.

Рис. 1.6. Диалоговое окно расширенного фильтра

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

Область критериев может быть в любом месте рабочего листа (желательно над или под таблицей). В приведённом на рис. 1.7 примере критерии заданы в строках 15 и 16 таблицы. Для соединения критериев с помощью оператора " И " критерии записываются в одной и той же строке. При этом новое условие записывается в новой клетке. Если второй критерий относится к тому же полю, то название поля повторяется. Для соединения критериев с помощью оператора " ИЛИ " критерии записываются в разных строках. Записанный в ячейку критерий содержит оператор сравнения. Знак равенства при задании критериев не используется, поскольку он будет интерпретироваться как вычисляемое выражение.

Рис. 1.7. Результат работы расширенного фильтра

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

Рассмотренный пример показывает условия, заданные по типу " И ". Для того чтобы задать условия типа " ИЛИ ", критерии записываются в разных строках, как это показано на Рис. 1.8.

	Cтудент	Экзамены
№	Фамилия	Имя	Отчество	Математика	Физика	Сопромат	Химия	Ин.Яз
	Хомяков	Михаил	Евгеньевич
	Лисицын	Павел	Иванович
	Зайцев	Игорь	Николаевич
	Волков	Александр	Сергеевич
	Волков	Пётр	Сергеевич
				Математика	Физика
					>3
				>3

 

Рис. 1.8. Фильтр с условиями типа " ИЛИ

Расширенный фильтр скрывает некоторые строки, но у каждой строки остаётся исходный номер, рассматриваемый как содержимое поля "№" (Рис. 1.7, Рис. 1.8). Номера строк, находящихся непосредственно над и под скрытыми строками, выделяются цветом. Для того, чтобы снова увидеть все строки таблицы, необходимо выполнить команду главного меню Данные/Фильтр/Отобразить всё.

Табличный способ задания логических условий является удачной находкой, которая применяется и в других программных продуктах MS Office (Access).

Промежуточные итоги

Интересной и полезной функцией ЭТ является автоматический расчет промежуточных данных с возможностью скрытия и раскрытия детальной информации. Например, при известном часовом потреблении электрической энергии (8760 чисел) нам хотелось бы взглянуть на динамику суточного (365 чисел), или недельного (52 числа), или месячного (12 чисел) максимума или минимума нагрузки. Отыскать нужные числа в исходном массиве (построчная или постолбцовая запись) - задача, мягко говоря, не из легких.

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

Рис. 1.9. Панель промежуточных итогов в разных режимах настройки параметров

Рис. 1.10. Итоги

Excel имеет прекрасную возможность - группировать данные, отслеживая значения некоторого столбца (недели). Для этого используется команда Данные/Итоги/Промежуточные итоги, которая располагает доступом к встроенным функциям с целью расчета промежуточных величин (рис. 1.9). В результате автоматически формируются группы, соответствующие итогам (рис. 1.10). Слева от заголовков появляется панель для управления режимами показа группируемых данных с помощью кнопок "+" и "-", указывающих и изменяющих режимы отображения ("-" - скрыть, "+" - показать). На рис. 1.10 данные по второму месяцу скрыты, а по третьему месяцу показаны детально.

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

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

Таблица 1.10

Затраты по высоковольтным выключателям
Выключатель	Изготовитель	Ток откл	Ном. ток	Число	Цена	Затраты
ВМП	Екатеринбург
ВМП	Екатеринбург
ВК	Ровно
ВК	Ровно
ВМП	Екатеринбург
ВМП	Екатеринбург
ВМП	Екатеринбург
ВК	Ровно
ВК	Ровно

 

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

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

Результатом работы команды Данные/Сводная таблица станет структура, представленная на рис. 1.11. Кнопка в поле «ВМП» позволяет менять данные в зависимости от типа выключателя. Кнопки в полях «ток кз» или «ток откл» позволяют выбирать как все, так и конкретные данные по каждому из упомянутых полей.

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

Рис. 1.12. Макет сводной таблицы

При создании сводных таблиц необходимо помнить, что они являются, по существу, лишь табличным отражением трехмерных функций f(x,y,z). Мастер сводных таблиц посредством макета (рис. 1.12) помогает структурно распределить параметры (в приведенном примере строка – категория «ток кз», столбец – «ном. ток»). Значения функции помещаются в поле «Данные». Один из параметров («выключатель») помещается в поле «Страница».

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

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

Консолидация данных

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

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

Консолидировать данные в Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Предусмотрены два способа консолидации данных:

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

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

Примечания

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

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

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