Подведение общих и промежуточных итогов

В экономических задачах часто требуется выполнение таких операций, как сортировка, фильтрация данных, формирование разнообразных итогов и сводок. Для решения таких задач необходимо представить электронную таблицу Excel в виде списка или базы данных: при этом строки таблицы называют записями, а столбцы – полями. Чтобы таблицу превратить в список или базу данных, необходимо выполнять следующие правила:

· таблицы должны иметь однострочный заголовок;

· на одном рабочем листе не помещать более одного списка;

· отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой;

· формат заголовочной строки должен отличаться от формата данных списка;

· список не должен содержать объединенные ячейки, пустые строки или пустые столбцы.

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

Итоговая таблица содержит в левом верхнем углу номера уровней итогов 1, 2 и 3. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.

В итоговой таблице с левой стороны включается линейка уровня с кнопками (+) и (–). Щелчок по кнопке (+) показывает группу строк, кнопка
(–) скрывает группу.

 

Создание диаграмм

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

Чтобы создать диаграмму, необходимо сначала ввести для нее данные на листе или преобразовать их: произвести сортировку, группировку данных и, там, где это требуется - рассчитать итоговые показатели (или воспользоваться сводными таблицами).

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

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

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

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

Независимо от способа размещения диаграмма является связанной с исходными данными на листе, то есть при обновлении данных обновляется и созданная на их основе диаграмма.

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

Сохранение рабочей книги

Если рабочая книга сохраняется в первый раз, ей следует дать название, указать диск, папку, куда должна быть сохранена рабочая книга. Для этого следует:

· в меню Файл выбрать пункт Сохранить. Это вызовет появление диалогового окна Сохранение документа;

· в поле ввода Имя файла укажите имя, которое хотите присвоить рабочей книге;

· чтобы сохранить файл на другом диске, разверните список Папка и выберите нужный диск;

· чтобы сохранить файл в другой папке, щелкните дважды на значке этой папки в списке файлов и папок;

· щелкните на кнопке Сохранить, нажмите Enter.

Аргументы функции ВПР:

- искомое значение - значение, которое должно быть найдено в первом столбце массива (таблицы 2), может быть значением, ссылкой или текстовой строкой, по условию задачи – наименование подразделения: указываем ссылку на ячейку, содержащую наименование подразделения в таблице 1, в нашей задаче это ячейка С3;

- табл_массив - это таблица с информацией, в первом столбце которой ищутся данные, может содержать ссылку на интервал или имя интервала; в нашей задаче – это интервал ячеек Лист2!A5:B7 из таблицы 2, который в формуле должен иметь абсолютный адрес, т.к. при копировании формулы адрес табличного массива не должен изменяться. (Посмотрите, как будет копироваться формула, если вы забыли про абсолютные ссылки!).

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

- диапазон просмотра - это логическое значение, которое определяет, как ВПР должна находить соответствующее значение: точно или приближенно. Если диапазон_просмотра имеет значение ИСТИНА, задающееся по умолчанию, то значения в первом столбце аргумента табл_массив должны быть расположены в возрастающем порядке, в противном случае функция ВПР может выдать неправильный результат. Если диапазон_просмотра имеет значение ЛОЖЬ (или 0), то табл_массив не обязан быть сортированным.

Аргументы функции СУММЕСЛИ:

- диапазон - это интервал вычисляемых ячеек, в соответствии с условием задачи, - интервал ячеек таблицы 1 C3:С25;

- критерий - это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. При расчете показателя для подразделения А критерием суммирования будет признак соответствия наименования подразделения наименованию «А»;

- диапазон_суммирования -это фактические ячейки для суммирования. По условию задачи необходимо суммировать ячейки столбца «годовой доход сотрудника без учета премий».