1 Работа с таблицами формата Список

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

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

1. Список обязательно должен содержать строку заголовков.

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

3. В списке не должно быть пустых строк.

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

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

1.1 Основные приемы работы со списком

Excel обладает мощными средствами для работы со списками. Это:

1. пополнение списка с помощью формы;

2. фильтрация списка;

3. сортировка списка;

4. подведение промежуточных итогов;

5. создание итоговой сводной таблицы на основе данных списка.

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

1.2 Ввод данных

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

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

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

Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.

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

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

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

1.3 Фильтрация списков

В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.

Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные — Фильтр — Автофильтр. При включении Автофильтра возле имен полей списка появляются кнопки со стрелками.

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

Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.

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

Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра, выбрав команду Данные — Фильтр — Автофильтр, а потом установить Автофильтр снова.

Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна полезная команда Первые 10..., которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.

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

Подпись:

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

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

1. Для выбранного поля (например, Менеджер) из раскрывающегося меню кнопки Автофильтра выберите команду (Условие...).

2. В диалоговом окне Пользовательский автофильтр задайте условия отбора значений списка.

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

Для полей числового типа или дат пользуйтесь следующим правилом:

1. меньше И больше, когда вас интересует область между двумя числами или датами;

2. больше ИЛИ меньше в том случае, если вас интересует область вне интервала, заданного двумя числами или датами.

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

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

С помощью расширенного фильтра можно:

1. Определить более сложный критерий фильтрации.

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

3. Устанавливать вычисляемый критерий отбора.

Чтобы воспользоваться расширенным фильтром, необходимо задать диапазон критериев.

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

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

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

Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.

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

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

Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала перейдите на этот лист и только потом обращайтесь к команде Данные — Фильтр — Расширенный фильтр.

1.5 Сортировка

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

Изменение порядка строк в списке называется сортировкой.

Чтобы отсортировать список:

§ поместите курсор в тот столбец таблицы, по которому нужно выполнить сортировку;

§ затем щелкните по кнопке Сортировка по возрастанию или Сортировка по убыванию.

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

Сортировка отфильтрованного списка выполняется только для видимых строк.

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

1. выберите команду Данные — Сортировка;

2. в появившемся окне «Сортировка диапазона» в поле Сортировать по выберите название столбца и укажите порядок сортировки;

3. затем сделайте то же самое для второго и третьего поля;

4. нажмите ОК.

Упорядочивание текстовых строк чаще всего выполняют по алфавиту. Но это не всегда удобно. Для того чтобы установить особый порядок сортировки, например по дням недели или месяцам, выберите команду Данные — Сортировка. Затем нажмите кнопку Параметры. В открывшемся диалоговом окне Параметры сортировки из раскрывающегося списка «Сортировка по первому ключу» выберите полные названия месяцев. Теперь при сортировке по полю «Год» и (или) «Месяц» названия месяцев будут располагаться в привычном порядке.

1.6 Создание промежуточных итогов

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

Для получения промежуточных итогов выполните ряд действий:

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

2. При необходимости выполните фильтрацию.

3. Выберите команду Данные — Итоги.

4. В диалоговом окне «Промежуточные итоги» установите критерии, функцию и другие параметры, которые будут использованы при расчете итогов.

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

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

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

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

Для того чтобы удалить все рассчитанные промежуточные итоги, выберите команду Данные — Итоги и нажмите кнопку Убрать все.

3 Поиск решения

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

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

Изменяемые ячейки — те, от которых зависит значение целевой ячейки.

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

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

Итак, используем средство поиска решения для максимизации прибыли, отображаемой в ячейке F7, путем изменения квартального бюджета рекламы (ячейки B5:E5) при ограничении общего бюджета рекламы (ячейка F5) суммой в 40 000 р.

Программа Поиск Решения относится к надстройкам и может отсутствовать в меню. Для её установки используется команда Сервис – Надстройки.

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

В поле Установить целевую ячейку вводится адрес или имя ячейки, в которой находится формула оптимизируемой модели ($F$6).

Для максимизирования/минимизирования значения целевой ячейки путём изменения значений влияющих ячеек установим соответствующий переключатель в положение максимальному значению/минимальному значению. Если нужно получить значение в целевой ячейке равным некоторому числу, то переключатель нужно установить в положение значению и ввести нужное число.

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

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

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

Если выбрать Тип отчета — Результаты, то в рабочей книге появится новый лист Отчет по результатам 1. В нем приводится исходное и результирующее значение целевой функции и изменяемых ячеек (аргументов), а также информация для каждого ограничения о том, является ли оно связанным или нет в оптимальной точке (ограничение <= или >= называется связанным, если оно превращается в точное равенство).

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

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

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

Для восстановления исходных данных — в положение Восстановить исходные значения.

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

3.1 Параметры программы поиск решения

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

Параметр	Описание
Максимальное Время	Это положительное целое число <= 32 767 сек. Значение по умолчанию равно 100 секунд. Этот параметр ограничивает время, отведенное на поиск решения.
Итерации	Поскольку методы оптимизации, реализованные в программе Поиск Решения, являются итеративными, то можно задавать ограничение на общее число итераций. Значение по умолчанию равно 100, максимальное значение — 32767.
Точность	Контролирует точность решения, должно быть дробным числом в интервале от 0 до 1, имеет значение по умолчанию, равное 0.000001.
Допустимое отклонение	Представляет процент допустимого отклонения от оптимального решения при целочисленных ограничениях. Используется в качестве критерия остановки в методе ветвей и границ. Не играет роли, если не введены целочисленные ограничения.
Линейная Модель	Используется, если целевая функция и все ограничения (и все связи) в модели линейны. Ускоряет процесс отыскания решения.
Оценка	Линейная — использует линейную экстраполяцию при одномерном поиске. Квадратичная — использует квадратичную экстраполяцию, что дает лучшие результаты для нелинейных задач.
Производные	Параметры этой группы определяют способ вычисления при оценке частных производных целевых функций и ограничений.
Метод	Параметры этой группы определяют выбор итеративного алгоритма. Ньютона. Это квази ньютоновский алгоритм, применяемый по умолчанию. Требует больше памяти и больше вычислений на каждой итерации (т.к. является методом второго порядка, т.е. требует вычисления и первых, и вторых производных), но обеспечивает более высокую скорость сходимости в окрестности решения. Требует меньшего количества итераций. Сопряженных градиентов. Является методом первого порядка (т.е. требует вычисления только первых производных). Требует меньше памяти, чем метод Ньютона, но обычно большее число итераций. Рекомендуется при решении задач большой размерности. В случае решения сложных задач рекомендуется применять комбинацию методов. Вначале обычно применяют метод сопряженных градиентов, а в окрестности решения — метод Ньютона.

С помощью кнопки Сохранить Модель можно сохранить текущую модель, а с помощью кнопки Загрузить Модель — загрузить одну из ранее сохраненных моделей. Нажав ОК, можно вернуться из диалогового окна Параметры в диалоговое окно Поиск решения.

Практическое задание по инструменту Поиск решения

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

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

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

· Насколько успешно каждый филиал продавал тот или иной

тип товара?

· Как распределена сумма, заработанная филиалом, между менеджерами?

· Как продавались товары со скидкой?

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

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

Установите табличный курсор в одну из ячеек Списка (см. Приложение). Выполните команду Данные — Сводная таблица. На экране появится первое из трех диалоговых окон Мастера сводных таблиц и диаграмм.

На этом этапе определяется источник данных. Так как наши данные хранятся в базе данных рабочего листа, выберите В списке или базе данных Microsoft Office Excel и переходите к следующему шагу, щелкнув по кнопке Далее.

Подпись: На втором шаге Мастер попросит уточнить диапазон, в котором хранятся исходные данные. Если при выполнении команды Данные — Сводная таблица курсор был помещен в один из элементов - списка, Excel выполнит это автоматически.

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

В последнем диалоговом окне мастера щелкните по кнопке Макет.

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

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

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

Данные — поле (поля), для которого подводится итог в сводной таблице.

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

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

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

Для настройки изображения какого-либо поля дважды щелкните на нем.

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

4.2 Работа со сводными таблицами

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

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

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

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

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

Подпись: Добавление нового поля. В тот

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

В этом окне «жирным» шрифтом выделены названия тех полей списка, которые нашли свое отражение в сводной таблице.

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

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

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

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

* ИМЯ. Изменяет название, отображаемое на кнопке поля. Также это название можно изменить, просто отредактировав текст в ячейке, содержащей кнопку поля.

* ИТОГИ. Изменяет тип промежуточных итогов. Подводить итоги имеет смысл только в том случае, когда в области строк или столбцов сводной таблицы находится несколько полей.

* ОТОБРАЖАТЬ ПУСТЫЕ ЭЛЕМЕНТЫ. Этот флажок нужно установить, чтобы в сводной таблице отображались элементы по лей, не содержащие данных.

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

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

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

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

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

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

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

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

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

Для обновления сводной таблицы выберите команду Данные - Обновить данные или щелкните на кнопке Данные — Обновить данные .

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

Если таблицы имеют одинаковые столбцы с одинаковыми типами данных в них, то их называют однотипными. Консолидация данных позволяет объединить данные нескольких однотипных таблиц в одну.

Исходные области консолидируемых данных задаются либо трехмерными формулами, либо в поле Ссылка диалогового окна Консолидация (команда Данные — Консолидация). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3. Если исходные области и область назначения находятся на одном листе, можно использовать имена или ссылки на диапазоны.

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

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

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

Если области находятся в разных книгах разных каталогов диска, используется полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылка на диапазон: [C:\Бюджет\Отдел продаж.xls]Февраль’!Оборот.

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

Технология консолидации данных:

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

2. Данные — Консолидация.

3. Из списка Функция выберите используемую для обработки данных функцию.

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

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

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

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