Введение

Что можно сказать о современных программах обработки электронных таблиц? Сегодня это средства, позволяющие не только выполнять вычисления, но и управлять списками и создавать диаграммы. Если же говорить об EXCEL, которая является одной из наиболее известных программ обработки электронных таблиц, то без преувеличения можно утверждать, что ее возможности практически неисчерпаемы. Лично я считаю, что такие программы на сегодняшний день представляют собой один из наиболее мощных и гибких инструментов, созданных для компьютера.

EXCEL

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

За всю историю табличных расчетов с применением персональных компьютеров требования пользователей к подобным программам существенно изменились. В начале основной акцент в такой программе, как, например , Vi s i C a l c, ставился на счетные функции. Сегодня, положение другое. Наряду с инженерными и бухгалтерскими расчетами организация и графическое изображение данных приобретают все возрастающее значение. Кроме того, многообразие функций, предлагаемое такой расчетной и графической программой, не должно осложнять работу пользователя. Программы для Windows создают для этого идеальные предпосылки.

В последнее время многие как раз перешли на использование Windows в качестве своей пользовательской среды. Как следствие, многие фирмы, создающие программное обеспечение, начали предлагать большое количество программ для Windows.

СВОДНЫЕ ТАБЛИЦЫ

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

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

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

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

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

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

При создании сводной таблицы можно использовать один из четырех типов источников данных:

• список Excel;

• внешний источник данных, доступный через Microsoft Query;

• несколько диапазонов консолидации ( отдельных списков Excel );

• другую сводную таблицу.

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

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

1. Задание типа источника данных.

2. Указание местонахождения исходных данных или получение их из внешнего источника с помощью Microsoft Query.

3. Задание макета таблицы и выбор итоговой функции.

4. Указание места для размещения таблицы.

Шаг 1 : задание типа источника данных

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

Шаг 2 : указание местонахождения исходных данных

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

Шаг 3 : задание макета таблицы

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

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

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

По умолчанию мастер сводных таблиц применяет функцию Сумма

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

СУММ ( ) - сумма ;

СЧЕТ ( ) - количество значений ;

СРЗНАЧ ( ) - среднее ;

МАКС ( ) - максимум

МИН ( ) - минимум ;

ПРОИЗВЕД ( ) - произведение;

СТАНДОТКЛОН ( ) - несмещенное отклонение;

СТАНДОТКЛОНП ( ) - смещенное отклонение;

ДИСП ( ) - несмещенная дисперсия;

ДИСПР ( ) - смещенная дисперсия.

Шаг 4 : указание места для размещения таблицы

В окне диалога , укажем место , где будет располагаться сводная таблица.

Чтобы поместить таблицу на новом рабочем листе , установим переключатель Новый лист. В противном случае установим переключатель Существующий лист и введем имя диапазона на ссылку.

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

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

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

Сводная ведомость зарплаты за полугодие

Реорганизация сводной таблицы

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

Размещение полей на оси страниц

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

Цикл по элементам на оси страниц

Когда выводится поле на оси строк или столбцов, то для просмотра всех элементов этого поля достаточно прокрутить таблицу. Но если поле находится на оси страниц , то можно увидеть только один элемент этого поля. Чтобы увидеть другой элемент поля , нужно выбрать его в раскрывающемся списке этого поля. Последовательно выбирая элементы в списке , можно видеть двумерные «срезы» данных для соответствующих значений поля , помещенного в область страниц.

Вывод итогов для поля страницы

Внизу каждого раскрывающегося списка поля страницы виден элемент ( Все ). Выбор этого элемента списка позволяет вывести на экран итоговые значения для каждого поля на оси страниц.

Перемещение элементов поля страницы на отдельные листы книги

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

Изменение расположения кнопок полей

страниц

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

1. Выделить в сводной таблице любую ячейку.

2. Выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы.

3. Используя раскрывшийся список Макет страницы и поле Числополей в столбце окна диалога Параметры сводной таблицы задаем нужное расположение кнопок.

Обновление сводной таблицы

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

Обновление таблицы при открытии

файла

Чтобы Excel автоматически обновлял сводную таблицу при каждом открытие книги, в которой она находится, выберете команду Параметры в меню Сводная таблица на панели инструментов Сводная таблица. Затем в окне диалога Параметры сводной таблицы устанавливаем флажок Обновить при открытии.

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

Обновление связанных сводных

таблиц

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

Обновление сводной таблицы , связанной с внешними данными

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

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

Выделение элементов сводной

таблицы

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

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

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

Включение и выключение структурного выделения

Чтобы включить или выключить структурное выделение , выделим любую часть сводной таблицы. Затем выберем команду Выделить в меню Сводная таблица на панели инструментов Сводные таблицы.

Когда структурное выделение включено , кнопка слева от команды Разрешить выделение находится в нажатом состоянии , и три верхние команды этого подчиненного меню становятся доступными.

Если режим структурного выделения выключен , то кнопка выглядит не нажатой , а три первые команды не доступны.

Выделение только заголовков или только данных

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

Выделение всей сводной таблицы

Чтобы выделить сводную таблицу целиком ,необходимо выбрать команду Выделить в меню Сводная таблица на панели инструментов Сводные таблицы. Затем в подчиненном меню Выделить выбрать команду Таблица целиком.

Форматирование сводной таблицы

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

1. Выделить в сводной таблице любую ячейку.

2. Выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы.

3. В окне диалога Параметрысводной таблицы необходимо установить флажок Сохранить форматирование.

Применение автоформата к сводной

таблице

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

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

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

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

После этого в меню Сводная таблица на панели инструментов Сводные таблицы выберем команду Выделить и затем - Таблицацеликом. И на конец , в меню Формат выберем команду Стиль и применим стиль Обычный.

Применение числовых форматов

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

1. Выделим любую ячейку в области данных таблицы ( но только не кнопку поля и не заголовок элемента ).

2. Нажмем кнопку Поле сводной таблицы на панели инструментов Сводные таблицы.

3. В окне диалога Вычисление поля сводной таблицы нажмем кнопку Формат.

После нажатия кнопки Формат мы попадем в обычное окно диалога для числового форматирования , которое открывается в ответ на выбор команды Ячейки в меню Формат.

Отображение пустых ячеек в

сводной таблице

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

1. Выделим в сводной таблице любую ячейку.

2. Выберем команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы.

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

Добавление и удаление полей

Чтобы добавить в сводную таблицу новые поля или удалить существующие , с начало выделим любую ячейку в таблице. Затем

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

Использование нескольких полей

данных

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

Переименование полей и

элементов

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

Сортировка элементов

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

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

Сортировка элементов поля с помощью значений данных

Чтобы отсортировать элементы поля с помощью соответствующих значений данных , надо выполнить следующие действия:

1. Выделить любой элемент поля или кнопку поля , которое необходимо отсортировать.

2. Нажать кнопку Поле сводной таблицы на панели инструментов Сводные таблицы ( или выбрать команду Поле в меню Своднаятаблица ).

3. Нажать кнопку Далее.

Эти шаги приведут в окно диалога Дополнительные параметры поля сводной таблицы.

Сортировка элементов поля по

заголовкам

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

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

Затем выполним одну из приведенных ниже инструкций:

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

В секции Параметры сортировки установим переключатель Повозрастанию или По убыванию в зависимости от требуемого порядка сортировки. Затем в раскрывшемся списке С помощьюполя выберем имя сортируемого поля.

Использование нестандартного порядка сортировки

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

Когда вместо одного заголовка элемента вводится имя другого существующего заголовка элемента , то Excel рассматривает это действие как команду перестановки двух заголовков. Нестандартный порядок сортировки сохраняется при обновлении и реорганизации сводной таблицы.

Группировка данных

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

Excel предлагает несколько вариантов группировки элементов.

Группировка числовых элементов

Чтобы сравнить числовые элементы поля выделим любой элемент поля и нажмем кнопку Группировать на панели инструментов Сводные таблицы.

В окне диалога Группировать введем необходимые значения в поля Начальная с , По и С шагом .

Группировка элементов по временным диапазонам

Чтобы сгруппировать элементы поля по временным диапазонам , выделим любой элемент в поле Дата , нажмем кнопку Группировать на панели инструментов Сводные таблицы и заполним окно диалога.

Excel обеспечивает достаточную гибкость при группировке элементов поля дат. В списке С шагом окна диалога Группирование можно задать временной интервал от секунды до года. Если стандартных интервалов не достаточно , можно указать любое количество дней . Кроме того , можно создать две и более группировок одновременно , выделив несколько элементов в списке С шагом .

Использование общих и

промежуточных итогов

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

Общие итоги

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

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

Чтобы создать сводную таблицу без общих итогов, в окне диалога Мастер сводных таблиц - шаг 4 из 4 нажмем кнопку Параметры. Затем снимем флажки Общая сумма по столбцам и /или Общаясумма по строкам. Если нужно удалить общие итоги из существующей сводной таблицы , выберем команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы. Затем снимем флажки Общая сумма по столбцам и / или Общая сумма по строкам.

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

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

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

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

Чтобы изменить используемую по умолчанию итоговую функцию или применить несколько функций , выполним следующие действия:

1. Дважды щелкнем на заголовке поле. Вместо этого можно выделить заголовок поля и нажать кнопку Поле своднойтаблицы на панели инструментов Сводные таблицы.

   
   

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

Чтобы удалить промежуточные итоги для поля, проделаем следующее:

1. Дважды щелкнем на заголовке поле. Также можно выделить заголовок поле и нажать кнопку Поле сводной таблицы на панели инструментов Сводные таблицы.

2. В окне диалога Вычисление поля сводной таблицы установим переключатель Промежуточные итоги в положении Нет и нажмем кнопку ОК.

Но если позднее мы снова решим вывести в таблице промежуточные итоги , то сделаем так:

1. Дважды щелкнем на заголовке поле. Кроме того , можно выделить заголовок поле и нажать кнопку Поле своднойтаблицы на панели инструментов Сводные таблицы.

2. В окне диалога Вычисление поля сводной таблицы установим переключатель Промежуточные итоги в положение Автоматические и нажмем кнопку ОК.

Промежуточные итоги для самых

внутренних полей

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

Чтобы добавить промежуточные итоги для самого внутреннего поля, выполним следующие действия:

1. Дважды щелкнуть на заголовке внутреннего поля. Вместо этого можно выделить заголовок поля и нажать кнопку Поле своднойтаблицы на панели инструментов Сводные таблицы.

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

Изменение вычислений в

сводных таблицах

К числовым полям , помещенным в область данных сводной таблицы, Excel по умолчанию применяет функцию Сумма , а к любым не числовым полям - Количество значений. Но можно применить и многие другие формы вычислений. Кроме того , Microsoft Excel 97 позволяет использовать в сводных таблицах вычисляемые поля и элементы.

Использование других итоговых

функций

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

Применение нескольких итоговых функций к одному полю

К полю можно применить любое количество итоговых функций. Чтобы использовать дополнительные функции для поля , которое уже находится в области данных сводной таблицы , выделим любую ячейку в области данных и нажмем кнопку Поле сводной таблицы на панели инструментов. Оказавшись в окне диалога Мастер сводных таблиц - шаг 3 из 4 перетащим в область данных диаграммы макета кнопку этого же поля еще раз , чтобы разместить вторую копию заголовка поля. Дважды щелкнув на копии заголовка , затем в списке

Операция окна диалога Вычисление поля сводной таблицы выберем нужную итоговую функцию и нажмем кнопку ОК , чтобы вернуться в сводную таблицу.

Ссылки на данные в сводной

таблице

Если формула рабочего листа использует для получения данных из сводной таблицы обычную ссылку на ячейку , то она будет прекрасно работать , пока ячейка остается на своем месте. Но после реорганизации сводной таблицы эта формула возвратит неверные данные. Чтобы избежать этого , используем в формуле функцию ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ для создания ссылки на данные сводной таблицы.

Функция ПОЛУЧИТЬ. ДАННЫЕ. СВОДНОЙ. ТАБЛИЦЫ имеет два аргумента. Первый аргумент служит для указания сводной таблицы , а второй аргумент описывает извлекаемые данные. Первый аргумент может быть любой ячейкой сводной таблицы или именем диапазона , назначенного сводной таблице , а так же надписью , записанной в ячейке над левым верхним углом таблицы. Второй аргумент , заключаемый в двойные кавычки , перечисляет имена полей , на пересечении которых находятся извлекаемые данные. Имена полей во втором аргументе должны быть разделены пробелом.

Использование сводной таблицы для консолидации диапазонов

С помощью мастера сводных таблиц можно консолидировать данные из таблиц , расположенных на разных рабочих листах Excel. Непременным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного диапазона. Например , ведомости зарплаты за каждый квартал 1998года сформированы на разных рабочих листах Excel с именами 1кв -98 , 2кв -98 , 3кв -98 , 4кв -98.

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

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

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

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

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

По списку ( Все ) можно получить сведения по каждому кварталу.

ВЫВОД:

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

Exsel предназначен для использования в финансовой сфере, в научных расчетах, инженерно-конструкторской деятельности и т.д.

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

ЛИТЕРАТУРА:

1. М. Додж, К. Кината, К. Стинсон "Эффективная работа м Microsoft Exsel 97", издательство "Питер"; Санкт-Петербург, 1998г.

2. Е.К. Овчаренко, О.П. Ильина, Е.В. Балыбердин "Финансово - экономические расчеты в Exsel", Москва, 1999 г.

3. Йорг Шиб, Exsel 7,0: Сотни полезных рецептов, Дюссельдорф-Киев-Москва-Санкт-Петербург, 1997 г.