Журнал регистрации

Таблицу с журналом регистрации разместим на отдельном листе и присвоим ему имя ЖурналРегистрации . Журнал регистрации состоит из семи столбцов. Столбец А содержит дату в формате даты Excel, а столбец В — суммы полученных членами семьи доходов. В столбец С мы будем заносить суммы расходов. Столбец D будет содержать информацию о том, кто получил доход или произвел расход. Столбец Е — откуда получены доходы и на какую статью расходов потрачены деньги. В столбцах F и G будет содержаться дополнительная информация. Введите названия столбцов журнала регистрации, как показано на.
Присвоение имен ячейкам журнала регистрации
Поскольку в дальнейшем при составлении формул мы будем ссылаться на столбцы журнала регистрации, давайте присвоим соответствующим диапазонам ячеек имена. Вот как это делается.
1. Выделите диапазон ячеек A1:G 1000.
2. Нажав комбинацию клавиш [Ctrl+Shift+F3], вызовите диалоговое окно Создать имена.
3. Отметьте в нем опцию В строке выше и нажмите кнопку ОК.
После этого диапазонам ячеек будут присвоены следующие имена: столбец А — Дата, столбец В — Доход, столбец С — Расход, столбец D — Кто, столбец Е — Откуда_Куда, столбец F — На_что, столбец G — Что_именно.
ПРИМЕЧАНИЕ

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

Заполнение журнала регистрации
Главное, что необходимо обеспечить при заполнении журнала регистрации, — это чтобы одни и те же предметы и понятия назывались одинаково. Например, вместо слова "Машина* нельзя употреблять слово "Автомобиль" или, скажем, "Автомашина", поскольку это приведет к ошибкам при анализе данных.
После того как в журнале имеется хоть одна запись, информацию можно вводить с помощью диалогового окна Форма. Для вызова этого окна следует поместить табличный курсор в любое место таблицы и вызвать команду Данные/Форма. Однако, несмотря на кажущееся удобство этого метода, он подразумевает ввод данных с клавиатуры и является источником ошибок.
Для предотвращения ошибок можно использовать функцию автозаполнения, которая описана в главе 2. Ее действие заключается в том, что программа пытается "угадать" вводимое значение по данным, которые уже содержатся в столбце. Таким образом, достаточно в столбце Е набрать букву "М" — и слово "Машина" сразу же появится в ячейке, если оно уже было введено ранее.
При заполнении таблицы удобно также использовать такой прием, как выбор из списка. Щелкните правой кнопкой мыши в ячейке, куда надо ввести текст, выберите в контекстном меню команду Выбор из списка, и на экране появится список со всеми элементами, которые были внесены в столбец ранее . Вам остается лишь отметить в списке нужный элемент.
Анализ данных с помощью сводной таблицы
По прошествии некоторого времени после заполнения таблицы возникает необходимость в определении структуры доходов и расходов семьи. Это можно сделать двумя способами:
— с применением встроенных инструментов Excel;
— путем создания собственных средств анализа на основании формул.
Наиболее удобным встроенным инструментом для анализа данных в Excel является сводная таблица. Это вспомогательная таблица, построенная с помощью мастера сводных таблиц и анализирующая данные исходной таблицы. Исходной в данном случае является таблица, расположенная на листе ЖурналРегистрации .
Для создания сводной таблицы данные, находящиеся на листе ЖурналРегистрации, можно разделить на две группы:
— критерии, по которым производится анализ (к ним относятся столбцы Дата, Кто, Откуда/Куда, На что и Что именно)’,
— значения (находятся в столбцах Доход и Расход).
Для вызова мастера сводных таблиц выделите на рабочем листе диапазон, где находится таблица с журналом регистрации, и выполните команду Данные/Сводная таблица. На экране появится первое диалоговое окно Мастер сводных таблиц — шаг 1 из 4 .
В диалоговом окне Мастер сводных таблиц — шаг 1 из 4 указывается источник данных для сводных таблиц. В области Создать таблицу на основе данных, находящихся расположены переключатели, указывающие, откуда поступают данные. Нас вполне устраивает заданный по умолчанию переключатель В списке или базе данных Microsoft Excel. При нажатии кнопки Далее открывается следующее диалоговое окно, Мастер сводных таблиц — шаг 2 из 4 .
В этом диалоговом окне необходимо указать диапазон ячеек исходной таблицы, где содержатся данные, на основании которых и будет производиться анализ сводной таблицы. Если нас устраивает предварительно выделенный в таблице диапазон, то, нажав кнопку Далее, мы переходим к следующему диалоговому окну .
Как видите, здесь определяется структура создаваемой таблицы. В центре диалогового окна расположены области:
— Строка — используется в качестве заголовка строки в сводной таблице;
— Столбец — служит заголовком столбца в сводной таблице;
— Страница — задает критерий выборки данных, по которым создается сводная таблица;
— Данные — рассчитанные итоговые значения.
Все заголовки исходной таблицы, расположенной на рабочем листе ЖурналРе-гистрации, отображены в правой части диалогового окна в виде кнопок. Каждую из этих кнопок с помощью мыши можно перетащить в любую из четырех перечисленных областей.
Предположим, нам нужно определить по заголовку Откуда/Куда и статье Питание, сколько денег было потрачено на питание каждого члена семьи в отдельности и сколько на семью в целом. Для этого произведем следующие перемещения:
— заголовок поля Откуда/Куда — в область Страница;
— заголовок На что — в область Строка’
— заголовок Кто — в область Столбец;
— заголовок Расход — в область Данные.
При помещении кнопки с заголовком Расход в область Данные заголовок последней изменится на К-во значений по полю Расход.
Для того чтобы изменить вид или способ вычисления данных сводной таблицы, следует дважды щелкнуть мышью на каждом из размещенных в различных областях заголовков.
После щелчка на заголовке Откуда/Куда, расположенном в области Страница диалогового окна Мастер сводных таблиц — шаг 3 из 4, появится диалоговое окно Вычисление поля сводной таблицы . Здесь, в списке Скрыть элементы, нужно выделить элементы, которые нет необходимости отражать в создаваемой сводной таблице. Например, в нашем списке присутствуют статьи доходов Альфа, Гонорар и т. д., а также статьи расходов Питание, Одежда и прочие. А как вы понимаете, элементы, которые указывают на источник дохода, не должны отображаться в создаваемой сводной таблице, фиксирующей расходы.
В результате двойного щелчка на заголовке в области Данные диалогового окна Мастер сводных таблиц — шаг 3 из 4 вызывается следующее диалоговое окно, Вычисление поля сводной таблицы . В списке Операция укажите операцию, которая будет производиться в диапазоне ячеек, находящихся под данным заголовком. В нашем случае следует выбрать элемент Сумма. В поле Имя будет указано имя операции — Сумма по полю Расход.
При нажатии кнопки Далее появляется диалоговое окно Мастер сводных таблиц — шаг 4 из 4 . В области Поместить таблицу в при создании новой таблицы активизируйте переключатель новый лист, в результате чего сводная таблица будет расположена на вновь созданном рабочем листе. Сводную таблицу можно поместить и на лист ЖурналРегистрации, активизировав предварительно переключатель существующий лист.
Щелкните на кнопке Параметры, и на экране появится диалоговое окно Параметры сводной таблицы . Оно состоит из двух областей:
Формат — устанавливаются параметры форматирования и вид сводной таблицы;
Данные — задаются параметры источника сводной таблицы.
После нажатия кнопки Готово четвертого диалогового окна мастера сводная таблица будет расположена на вновь созданном листе (Лист1), название которого можно изменить .

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