Глава 4

Обработка данных

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

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

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

Проблемы с сортировкой и фильтрацией

Сортировка по значениям в нескольких столбцах

Проблема

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

 

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

Решение

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

 

Рис. 4.2. В отсортированном списке данные о продажах группируются по дням

Что такое список данных?

Списком данных называется набор данных на листе Excel, в котором каждому столбцу сопоставлено уникальное имя, а каждая строка представляет совокупность данных (например, данные о продажах за определенные дни). Список данных не может содержать пустых строк и столбцов, между строкой заголовков и данными также не должно быть пустых строк, а ячейки рядом со списками не должны содержать лишних данных. Таким образом, справа от списка данных должен находиться минимум один пустой столбец, слева — минимум один пустой столбец или край листа, а снизу — минимум одна пустая строка. В действительности список данных имеет много общего с таблицей базы данных. Типичный список данных показан на рис. 4.2.

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

Проблема

Excel ограничивает меня сортировкой данных в алфавитном или числовом порядке, а это не всегда удобно. Мой начальник считает продажу машин самым важным делом, на второе место ставит их обслуживание, на третье — продажу аксессуаров. Эти приоритеты должны отражаться в рабочих листах: сначала «машины», за ними «сервис» (если не продавать машины, то и обслуживать будет нечего) и на третьем месте «аксессуары». Однако Excel предлагает отсортировать список отделов в алфавитном порядке либо по возрастанию (аксессуары, машины, сервис), либо по убыванию (сервис, машины, аксессуары). Мне приходится тратить много времени на перестановку строк в соответствии с требованиями начальника. Нельзя ли заставить Excel сортировать названия отделов в определенном мною порядке? Это сэкономило бы мне несколько часов работы каждый месяц.

Решение

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

1.Введите сортируемые значения в группе смежных ячеек одной строки или столб­ца в том порядке, в котором должна производиться сортировка. В нашем примере значения Машины, Сервис и Аксессуары вводятся в ячейках A1, A2 и A3 соответственно.

2.   Выделите ячейки с только что введенными значениями, выполните команду Сервис4Параметры и перейдите на вкладку Списки.

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

4.   Щелкните сначала на кнопке Импорт, затем — на кнопке OK.

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

1.    Щелкните на любой ячейке списка данных и нажмите клавиши Ctrl+Shift+8, чтобы выделить весь список.

2.    Выполните команду Данные4Сортировка. Раскройте список Сортировать по и выберите столбец, который должен быть отсортирован в порядке пользовательского списка.

3.    Щелкните на кнопке Параметры. В окне Параметры сортировки раскройте список Сортировка по первому ключу, выберите свой пользовательский список и щелк­ните на кнопке OK.

4.    Если потребуется, задайте дополнительные критерии сортировки. Щелкните на кнопке OK и, вернувшись к окну Сортировка, снова щелкните на кнопке OK, чтобы отсортировать данные.

Сортировка данных слева направо

Проблема

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

Решение

Чтобы отсортировать строку значений, выделите ее, выполните команду Данные4 Сортировка, щелкните на кнопке Параметры, в группе Сортировать установите переключатель Строки диапазона и щелкните на кнопке OK. Раскройте список Сортировать по, выделите сортируемую строку, выберите способ сортировки (По возрастанию или По убыванию) и щелкните на кнопке OK.

Фильтрация данных

Проблема

Я работаю с огромными листами. Так, один из моих листов имеет всего 3 столбца в ширину и содержит более 1000 строк. Пример показан на рис. 4.3.

 

Рис. 4.3. Работать с таким объемом данных не слишком удобно

В данный момент меня интересуют только данные по агенту с первым идентификационным номером (SalesID=1). И только. И ничего более. Как убрать все остальные записи?

Решение

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

Включая режим автофильтра, будьте внимательны и не щелкните на заголовке столбца (сером маркере с обозначением столбца — A, B, C и т. д.). В противном случае после выполнения команды Данные4Фильтр4Автофильтр Excel добавит кнопку фильтрации только в выделенный столбец.

Чтобы отключить все фильтры, выполните команду Данные4Фильтр4Отобразить все. Чтобы полностью отключить режим фильтрации и убрать кнопки со стрелками, выполните команду Данные4Фильтр4Автофильтр повторно. В Excel 2003 средства фильтрации включены в новый механизм работы со списками. За дополнительной информацией о списках в Excel 2003 обращайтесь к разделу «Проблемы со списками Excel 2003» главы 9.

Вывод первых и последних 10 значений

Проблема

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

Решение

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

 

Рис. 4.4. Поиск наибольших и наименьших элементов списка
с использованием автофильтрации

Создание многоуровневого фильтра

Проблема

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

Решение

Чтобы отфильтровать данные по выбранному критерию, выполните команду Данные4Фильтр4Автофильтр, щелкните на кнопке со стрелкой над столбцом, к которому применяется критерий, и выберите в меню команду Условие. На экране появится диалоговое окно Пользовательский автофильтр (рис. 4.5).

 

Рис. 4.5. Фильтровать данные по значениям
нескольких столбцов? Легко!

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

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

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

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

Проблема

Имеется электронная таблица с 8500 адресами, которые будут использоваться в предстоящей рассылке. Я должен сократить список до 5000 адресов, чтобы не превысить бюджет. Многие из 8500 адресов повторяются, но Excel упорно отказывается найти дубликаты. Знакомый порекомендовал мне экспортировать данные в Access и выполнить там запрос на поиск дубликатов. Пожалуйста, не за­ставляйте меня осваивать Access!

Решение

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

 

Рис. 4.6. Хотя диалоговое окно Расширенный фильтр вполне тривиально,
оно может быть весьма полезным

Копирование уникальных записей списка данных выполняется так.

1.   Выполните команду Данные4Фильтр4Расширенный фильтр.

2.   Установите переключатель Скопировать результат в другое место.

3.   Щелкните на кнопке свертки справа от поля Исходный диапазон, выделите фильтруемые ячейки и нажмите клавишу Enter.

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

5.   Установите флажок Только уникальные записи и щелкните на кнопке OK.

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

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

Проблема

Я отфильтровал список данных (рис. 4.7), но при попытке скопировать оставшиеся записи и вставить их в другой лист Excel также вставляет скрытые записи. Я знаю, что в действительности выделено больше ячеек, чем показано на экране, потому что при выделении в поле имени (находящемся слева от строки формул) сообщается, что выделенная область состоит из 74 строк и 5 столбцов. Как скопировать и вставить только видимые ячейки?

 

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

Решение

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

1.  Отфильтруйте список данных и выделите видимые ячейки.

2.  Щелкните на кнопке Выделить видимые ячейки.

3.  Выполните команду Правка4Копировать.

4.  Выполните команду Правка4Вставить.

Кубик рубика в Excel

Кто когда-нибудь играл с кубиком Рубика, поднимите руку. Ого, сколько рук!

Если вы не хотите раскладывать свои игрушки на рабочем месте, но не прочь время от времени заняться делом, требующим пространственного мышления и сообразительности, загрузите бесплатный эмулятор кубика Рубика (автор — Робин Глинн) с адреса http://www.xl-logic.com/xl_files/games/cube.zip. Все, что от вас требуется, — распаковать архив и открыть книгу cube.xls. Как видно из рисунка, для манипуляций с кубиком достаточно щелкнуть на символическом изображении нужной операции в нижней части экрана. Кнопки в правой части книги предназначены для сохранения текущей позиции, восстановления сохраненной позиции, случайной перестановки граней и решения головоломки в автоматическом режиме (в этом режиме можно управлять скоростью вращения кубика). Только не изменяйте скорость во время работы программы в автоматическом режиме — когда я попытался это сделать, компьютер завис почти на две минуты.

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

 

Классическая головоломка — кубик Рубика

Составление сводок по видимым ячейкам

Проблема

Фильтры удобны, но я не могу понять: как написать формулу, которая бы обрабатывала только видимые ячейки листа? Допустим, я работаю с данными о продажах. Мне нужно просуммировать объемы продаж по каждому представителю, но я не нашел способ просуммировать ячейки каждого представителя без создания формулы со ссылками на конкретные ячейки. Например, работая с листом, представленным на рис. 4.3, для вычисления суммы по первому представителю (SalesID=1) я был вынужден создавать формулу, которая суммирует данные ячеек C2, C5, C9, C12, C17 и C22. Затем для получения данных по второму представителю (SalesID=2) мне пришлось суммировать данные ячеек C3, C10, C13, C18 и C23. Я могу отфильтровать список данных, чтобы в листе отображались ячейки, относящиеся только к одному торговому представителю. Нельзя ли создать формулу, которая бы суммировала только эти ячейки?

Решение

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

1.   Щелкните на любой ячейке списка данных (на рис. 4.3 список данных находится в диапазонеA1:C26).

2.   Выполните команду Данные4Фильтр4Автофильтр.

3.   Щелкните на кнопке со стрелкой в столбце SalesID и выберите код представителя.

4.   Щелкните на ячейке столбца Продажи, находящейся под данными.

5.   Щелкните на кнопке Автосумма на стандартной панели инструментов Excel и нажмите клавишу Enter.

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

Формула промежуточного итога имеет такой синтаксис:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(функция;диапазон)

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

Таблица 4.1. Функции обобщения данных в формулах промежуточного итога

Номер    Функция

1              СРЗНАЧ (среднее арифметическое видимых ячеек)

2              СЧЕТ (количество видимых ячеек)

3              СЧЕТЗ (количество видимых ячеек, содержащих значения)

4              МАКС (максимальное значение в видимых ячейках)

5              МИН (минимальное значение в видимых ячейках)

6              ПРОИЗВЕД (произведение значений в видимых ячейках)

7              СТАНДОТКЛОН (стандартное отклонение значений в видимых ячейках)

8              СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности)

9              СУММ (математическая сумма значений в видимых ячейках)

10            ДИСП (дисперсия значений в видимых ячейках)

Проблемы с функциями поиска

Поиск значений в списках данных

Проблема

Я знаю, что Excel не является системой управления базами данных… это не Access. Понятно, что чудес ждать не приходится, но я все же надеюсь, что Excel поможет найти нужное значение в таблице. Например, у меня есть список торговых представителей, отсортированный по коду работника (рис. 4.8). Если я получаю отчет с кодом работника, нельзя ли узнать, кому принадлежит этот код, не «водя пальцем» по списку?

 

Рис. 4.8. Поиск ассоциированных значений в списке данных

Решение

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

Первая функция, используемая для поиска в листе, — ПРОСМОТР. Принцип ее рабо­ты в общих чертах выглядит так: сначала функция ищет заданное значение (допустим, в столбце A). Определив, какая запись содержит заданное значение в столбце A, функция обращается (например) к столбцу C той же записи, читает содержимое ячейки и «возвращает» его (или отображает в ячейке, содержащей формулу).

Функция ПРОСМОТР имеет следующий синтаксис:

=ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

Здесь:

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

·         просматриваемый_вектор — диапазон, в котором ищется искомое_значение (если список кодов работников хранится в диапазоне A2:A34, вводится именно этот диапазон);

·         вектор_результатов — диапазон, из которого берется ассоциированное значение (если имена работников хранятся в диапазоне B2:B34, вводится именно этот диапазон).

Проще говоря, вы сообщаете функции ПРОСМОТР, что нужно искать, где искать и где находится ассоциированное значение, которое вас интересует. Если в листе на рис. 4.8 в пустую ячейку ввести формулу =ПРОСМОТР(C3;A2:A21,B2:B21), будет взято значение из ячейки C3, найдено ассоциированное значение из столбца A (из строк 2–21) и возвращено значение столбца B, которое соответствует значению, найденному в столбце A. Например, если в ячейке C3 ввести 16, функция вернет строку Эшби, Тернер.

Возможности функции ПРОСМОТР несколько ограничены тем, что диапазоны просматриваемый_вектор и вектор_результатов могут содержать только одну строку или один столбец. Также существует вероятность получить неверный результат: если функция ПРОСМОТР не может найти искомое значение в диапазоне просматриваемый_вектор, она использует максимальное значение, меньшее либо равное искомому. Например, если у вас появился новый представитель с кодом SalesID=21, данные которого еще не были введены в лист, то вызов функции ПРОСМОТР вернет строку Лейтер, Катерина, что в принципе неверно.

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

=ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Здесь:

·         искомое_значение — искомые данные, которыми могут быть конкретное значение (например, число 14), ссылка на ячейку со значением (D2) или текстовая строка (GR083), причем функция ВПР всегда ищет искомое значение в первом столбце массива;

·         таблица — диапазон, в котором Excel ищет искомое значение (на рис. 4.8 это диапазон A2:B21);

·         номер_столбца — номер столбца в диапазоне таблица, в котором функция ищет ассоциированное и возвращает значение (если номер столбца равен 1, функция возвращает значение из левого столбца, если он равен 2 — из второго столбца, и т. д.; если номер столбца превышает количество столбцов в диапазоне, переданном в аргументе таблица, функция ВПР отображает код ошибки #ССЫЛКА!);

·         интервальный_просмотр — необязательный последний аргумент (если он равен TRUE или не указан, ВПР ведет себя как функция ПРОСМОТР и возвращает наибольшее значение, меньшее величины искомое_значение либо равное ей; если он равен FALSE, функция ищет точное совпадение или отображает код ошибки #Н/Д!).

Функция ВПР, эквивалентная упоминавшейся выше формуле =ПРОСМОТР(C3;A2: A21;B2:B21), выглядит так:

=ВПР(C3;A2:B21;2;ИСТИНА)

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

Название ВПР является сокращением от слов «вертикальный просмотр»; Excel ожидает, что данные упорядочены по столбцам. Если данные упорядочены по строкам, как на рис. 4.9, для выборки ассоциированных значений можно воспользоваться функцией ГПР.

 

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

Зачем использовать номер столбца, равный 1?

Вероятно, вы заметили, что функция ВПР позволяет возвращать значения из первого столбца переданной таблицы. Но зачем это нужно, ведь именно в первом столбце мы ищем исходное значение? Приведу лишь один из примеров: допустим, первый столбец содержит список дат вида 15.3.2005, 17.3.2005, 28.3.2005 и т. д. Формула =ВПР(C3;A2:B200;1;ИСТИНА) вернет первую дату, ближайшую к дате в ячейке C3, но не позднее ее. Например, если С3 содержит дату 27.3.2005, то формула =ВПР(C3;A2:B00;1;ИСТИНА) вернет 17.3.2005.

Синтаксис вызова функции ГПР:

=ГПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

Функция работает точно так же, как ВПР, но с разворотом на 90°:

·         искомое_значение — данные, которые ищутся в первой строке таблицы;

·         таблица — диапазон, в котором Excel ищет искомое_значение;

·         номер_строки — номер строки диапазона таблица, в котором функция ищет и возвращает ассоциированное значение, — другими словами, если в первой строке листа перечисляются коды работников, во второй строке — имена, а в третьей — зарплата, то по значению 2 будет проведен поиск имени, а по значению 3 — зарплаты (если номер строки меньше 1, функция ГПР выдает ошибку #ЗНАЧ!, а если он больше количества строк в диапазоне, переданном в аргументе таблица, выводится код ошибки #ССЫЛКА!);

·         интервальный_просмотр — если аргумент равен TRUE или не указан, функции ГПР разрешается поиск приблизительных совпадений; если он равен FALSE, функция ищет точное совпадение или отображает код ошибки #Н/Д!.

Для таблицы, показанной на рисунке, формула для определения зарплаты по коду должности (CEO, CIO и т. д.), находящемуся в ячейке B12, выглядит так:

=ГПР(B12;B2:F3;3;ЛОЖЬ)

Поиск значения в любом столбце

Проблема

Что и говорить, функция ВПР полезна, однако ее можно использовать для поиска значений только в столбце A. Но что делать, если я хочу найти значение в пятом столбце и вернуть ассоциированное значение из третьего столбца? Можно ли обойти ограничения функции ВПР?

Решение

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

Функция ИНДЕКС находит адрес ячейки, удовлетворяющей поставленному критерию, и имеет следующий синтаксис вызова:

=ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области)

Здесь:

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

    (A1:B6;C3:D8;F1:G6)

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

·         номер_столбца — номер столбца диапазона, определяемого аргументом ссылка, в котором функция должна искать значение;

·         номер_области — номер диапазона, определяемого аргументом ссылка, в котором функция должна искать значение, при этом первому выделенному или введенному поддиапазону присваивается номер 1, второму — 2 и т. д., а если аргумент номер_области не указан, используется область 1 (например, если функция ИНДЕКС осуществляет поиск в диапазонах A10:B14, C12:D16 и F14:G18, то поддиапазон A10:B14 считается областью 1, C12:D16 — областью 2, а F14:G18 — областью 3).

Функция ПОИСКПОЗ возвращает относительную позицию значения в диапазоне. Например, если целевое значение находится в третьей ячейке диапазона, состоящего из одного столбца, то функция ПОИСКПОЗ вернет значение 3.

Функция ПОИСКПОЗ имеет следующий синтаксис вызова:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

Здесь:

·         искомое_значение — данные, которые ищутся в таблице;

·         просматриваемый_массив — смежный диапазон ячеек, содержащий набор искомых значений;

·         тип_сопоставления — число –1, 0 или 1, причем если аргумент тип_сопоставления равен 1, функция ПОИСКПОЗ ищет наибольшее значение, меньшее либо равное искомому, и в этом случае аргумент просматриваемый_массив должен быть отсортирован по возрастанию; если аргумент тип_сопоставления равен 0, функция находит первое точное совпадение (элементы массива могут следовать в произвольном порядке); если аргумент тип_сопоставления равен –1, функция ищет наименьшее значение, большее либо равное искомому (в этом случае аргумент просматриваемый_массив должен быть отсортирован по убыванию), а если аргумент тип_сопоставления не указан, Excel считает, что он равен 1.

Функции ИНДЕКС и ПОИСКПОЗ хорошо работают в сочетании друг с другом — функ­ция ПОИСКПОЗ определяет местонахождение ячейки, необходимой функции ИНДЕКС для проведения поиска. Для примера рассмотрим набор данных на рис. 4.10.

 

Рис. 4.10. Просмотр справа налево? Это возможно,
но не с использованием функции ВПР

Если вы хотите найти первую запись, у которой доход превышает сумму в 500 000 долларов, сначала отсортируйте список так, чтобы столбец доходов был упорядочен по убыванию, а затем воспользуйтесь формулой =ИНДЕКС(B3:17;ПОИСКПОЗ(F3; D3:D17,–1)) для определения отдела и формулой =ИНДЕКС(B3:B17;ПОИСКПОЗ(F3;D3: D17;–1)) для определения года. Далее приводятся краткие пояснения относительно того, как работают эти составные формулы.

·         Первый аргумент функции ИНДЕКС определяет диапазон с потенциальными возвращаемыми значениями C3:C17 (отделы фирмы по продаже автомобилей).

·         Функция ИНДЕКС получает свой второй аргумент от функции ПОИСКПОЗ. Функ­ция ПОИСКПОЗ ищет в диапазоне D3:D17 наименьшее значение, большее значения в ячейке F3, и возвращает функции ИНДЕКС его позицию в диапазоне
(в данном случае 5).

·         Функция ИНДЕКС, которая к этому моменту имеет вид =ИНДЕКС(C3:C17;5), возвращает значение из ячейки C7 — пятой ячейки отсортированного диапазона.

Исправление ошибочного результата функции ВПР в Excel 97

Проблема

Я храню информацию о заказах своей компании в книге Excel 97, преобразованной из Lotus 1-2-3. Сведения о заказах хранятся на одном листе, а сведения о продуктах — на другом, но при попытке найти продукт по номеру заказа я иногда получаю неверный результат. Что происходит?

Решение

В Excel 97 имеется ошибка, которая проявляется, когда таблица просмотра и ячейка с формулой, содержащей функцию ВПР, находятся на разных листах, и при этом используется режим вычисления формул по правилам Lotus 1-2-3 (программы Excel и Lotus вычисляют формулы ВПР по разным правилам). Чтобы решить проблему, щелкните на листе с таблицей просмотра, выполните команду Сервис4Параметры, перейдите на вкладку Переход и сбросьте флажок Производить вычисления по правилам Lotus 1-2-3.

Текст и числа при просмотре

Проблема

В фирме, где я работаю, я храню информацию о деталях к автомашинам. Конечно, коды деталей определяются производителем, причем многие коды (такие, как A3000 и T1648B) содержат буквы. Я только что импортировал свой список кодов в Excel (рис. 4.11) и собирался по коду детали определить название и получить другую информацию, но тут же получил ошибку #Н/Д. Я ввожу A2000 вместо A3000 в ячейке поиска, но почему формула выдает ошибку? Ведь если ввести 9815 вместо 9816, формула ВПР возвращает строку «Свеча зажигания» — название из предыдущей ячейки таблицы. Почему же формула не останавливается на детали с кодом 9816, записи, расположенной перед строкой A3000, и не возвращает строку «Фара»? Ничего не понимаю.

 

Рис. 4.11. Коды деталей состоят из букв и цифр, что нарушает работу
стандартных формул просмотра

Решение

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

Чтобы предотвратить эту ошибку, проще всего изначально отказаться от смешения текстовых и числовых значений в списке просмотра. Если это невозможно, перед импортом или вводом данных отформатируйте ячейки как текст (выполните команду Формат4Ячейки, перейдите на вкладку Число и выберите в списке Числовые форматы пункт Текстовый). Если данные уже находятся в книге, воспользуйтесь формулами массивов для текстовой интерпретации значений списка кодов деталей (изменение формата ячейки после ввода данных не поможет). В следующей формуле предполагается, что искомое значение находится в ячейке D2, а список кодов деталей — в ячейках A1:A3:

{=ВПР(ТЕКСТ(D2;"@");ТЕКСТ(A1:A3;"@");1)}

Помните, что для создания формулы массива необходимо нажать клавиши Ctrl+ Shift+Enter. Если просто нажать клавишу Enter, Excel выдаст ошибку #H/Д!.

Поиск с учетом регистра символов

Проблема

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

Решение

Если вы проводите простой поиск, установите флажок Учитывать регистр в диалоговом окне поиска (выполните команду Правка4Найти, щелкните на кнопке Параметры). При использовании поисковых функций (ПРОСМОТР, ВПР, ГПР, ИНДЕКС и ПОИСКПОЗ) обеспечить поиск с учетом регистра несколько сложнее.

Тем не менее комбинация функций ЕСЛИ и СОВПАД позволяет добиться желаемого результата. Допустим, вы работаете с листом, показанным на рис. 4.12.

 

Рис. 4.12. Иногда значения различаются только регистром символов

Попробуйте ввести в ячейке C7 формулу:

=ЕСЛИ(СОВПАД(B7;ГПР(B7;A1:E2;1;ЛОЖЬ))=ИСТИНА;

  ГПР(B7;A1:E2;2;ЛОЖЬ);"Не совпадает")

Затем введите в ячейке B7 строку джо — формула вернет текст Не совпадает, потому что содержимое ячейки D1 не совпадает с введенным текстом с точностью до регистра символов. Но если ввести в ячейке B7 строку Джо, в ячейке отображается почасовая ставка Джо — значение $14.00.

Аналогичное решение может использоваться с функцией ВПР при вертикальной ориентации данных (рис. 4.13). В этом случае формула принимает вид:

=ЕСЛИ(СОВПАД(A7;ВПР(A7;A1:B5;1;ЛОЖЬ))=ИСТИНА;

  ВПР(A7;A1:B5;2;ЛОЖЬ);"Не совпадает")

 

Рис. 4.13. Данные располагаются по столбцам

Просмотр в сериях значений времени

Проблема

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

 

Рис. 4.14. Значения времени выглядят вполне нормально. Почему Excel
не позволяет мне использовать их в функции ВПР?

Решение

Данная проблема встречается во всех версиях Excel (включая Excel 2003), если для ввода серии значений времени используется маркер заполнения. Вероятно, в листе на рис. 4.14 вы ввели 1:00 в ячейке A2, 2:00 в ячейке A3, а затем расширили серию на ячейки A4, A5 и A6 при помощи маркера заполнения. Одно из возможных решений — ввести значения времени вручную, но этот способ вряд ли подойдет, если серия содержит более трех-четырех значений. Существует и дру­гое решение: выполните команду Сервис4Параметры, перейдите на вкладку Вычисления и установите флажок Точность как на экране. После щелчка на кнопке OK на экране появится сообщение Данные будут изменены — точность будет понижена; оно относится ко всем данным книги, не только к текущему листу. Другими словами, если имеется серия чисел с пятью цифрами после запятой, но ячейки отформатированы так, чтобы в них отображались только первые две цифры, Excel усекает базовые значения и забывает исходное содержимое ячеек. Если такой вариант вас не устраивает и вы хотите хранить данные с полной точностью (если вы ученый — скорее всего, так и есть) и использовать функцию ВПР для выборки значений времени, значит, данные придется вводить вручную. Могу только посочувствовать.

Ускорение многократных просмотров

Проблема

Я создал в Excel 97 главную книгу, в которой обобщаются данные из всех 35 проектов моего отдела. Требуется обновить главную книгу и перенести в нее данные о новых ресурсах и человеко-часах, потраченных на разные проекты. Да, моя книга содержит немало формул ВПР для импорта данных из других книг. Но когда я про­шу Excel обновить эти ссылки и формулы, книга открывается целую вечность (а вернее, несколько минут). В Excel 95 таких задержек не было! Что происходит?

Решение

Проблема в том, что перед получением обновленных данных программе приходится открывать все связанные файлы. Excel 97, Excel 2000 (до выхода Service Pack 1) и даже Excel 2002 (до выхода Service Pack 3) плохо справляются с открытием связанных файлов. Лучшее решение проблемы — установка для Excel 2000 и 2002 соответствующего обновления Service Pack (или радикальное решение — установка Excel 2003). Вы можете загрузить последнюю версию Service Pack для своей версии Office по адресу http://office.microsoft.com/OfficeUpdate/default.aspx. Щелкните на ссылке Check for Updates в верхней части страницы — сайт идентифицирует версию Office и выведет список доступных обновлений.

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

Поиск первого или последнего
совпадения в массиве

Проблема

Я храню сведения о заказах в базе данных. Данные из базы импортируются в Excel для построения сводной таблицы. В первом столбце листа хранятся коды клиентов, во втором — даты размещения заказов (рис. 4.15). Каждый раз, когда я использую функцию ПРОСМОТР или ВПР для поиска в таблице, формула находит последнее вхождение искомого элемента в таблицу. Это может быть удобно для поиска последнего заказа, размещенного клиентом, но я также хотел бы иметь возможность найти его первый заказ. Можно ли найти первое вхождение значения в списке вместо последнего?

Решение

Для поиска первого вхождения значения в список можно воспользоваться комбинацией функций ИНДЕКС и ПОИСКПОЗ. Так, если в книге на рис. 4.15 в ячейке D2 введен код клиента 001354, формула =ПРОСМОТР(D2;A1:A6;B1:B6) вернет 05.01.2005, а формула =ИНДЕКС(A1:B6;ПОИСКПОЗ(D2;A1:A6;0);2) вернет 15.10.2004. Эта комбинация функций ИНДЕКС и ПОИСКПОЗ позволяет выполнять операции без предварительной сортировки первого столбца списка по возрастанию или убыванию.

 

Рис. 4.15. Хотите узнать дату последнего заказа, размещенного клиентом? Или первого? Возможно и то и другое — нужно только уметь правильно спрашивать

Проблемы с анализом вида «что, если»

Создание сценария

Проблема

У меня два начальника, а это неминуемо означает, что мне приходится постоянно отвечать на вопросы типа: «А что, если…?» Например, когда я строю лист с оценкой суммарных затрат на проект, каждый начальник спрашивает меня о разных переменных: а что, если один компонент будет стоит дороже или дешевле? Нельзя ли сократить затраты на рабочую силу за счет повышения производительности? Вполне разумные вопросы, но когда я пытаюсь включить ответы на них в один лист, у меня голова идет кругом. Что мне на самом деле нужно, так это возможность отображения альтернативных значений в отдельных ячейках (например, затрат на рабочую силу и стоимости комплектующих) и переключения между ними. Возможно ли это?

Решение

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

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

1.  Выполните команду Сервис4Сценарии. На экране появится диалоговое окно Диспетчер сценариев.

2.  Щелкните на кнопке Добавить и введите имя сценария.

3.  Щелкните на кнопке свертки справа от поля Изменяемые ячейки, выделите ячейки, значения которых требуется изменить, и нажмите клавишу Enter. Помните, что вы можете выделять несмежные группы ячеек, — для этого следует выделять ячейки с нажатой клавишей Ctrl. Завершив выделение, щелкните на кнопке OK.

4.  В диалоговом окне Значения ячеек сценария (рис. 4.16) введите новые значения выделенных ячеек. Закройте диалоговое окно щелчком на кнопке OK и щелкните на кнопке Закрыть, чтобы закрыть диалоговое окно Диспетчер сценариев.

 

Рис. 4.16. Здесь вводятся новые значения сценария…

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

1.  Выполните команду Сервис4Сценарии. На экране появится диалоговое окно Диспетчер сценариев (рис. 4.17).

 

Рис. 4.17. …А здесь отображается список сценариев

Никогда не используйте сценарии в книгах, содержащих единственную копию данных. Вместо этого создайте резервную копию книги и при анализе «что, если» применяйте ее. Почему? Если вы забудете отменить действие последнего активного сценария, Excel оставит его значения в ячейках. Если закрыть лист, не восстановив оригинал щелчком на кнопке Отменить, исходные данные будут потеряны! Другая мера предосторожности — создание «нормального» сценария, который восстанавливает исходные значения всех ячеек, задействованных в каком-либо сценарии. Каждый сценарий ограничивается изменением 32 ячеек, так что, возможно, для восстановления исходных данных придется создать несколько сценариев, но игра стоит свеч: целостность исходных данных будет гарантирована.

2.   Выделите сценарий и щелкните на кнопке Вывести.

3.   Закройте диалоговое окно Диспетчер сценариев щелчком на кнопке Закрыть.

Чтобы скрыть сценарий, щелкните на кнопке Отменить на стандартной панели инструментов Excel.

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

Проблема

Я создал итоговую таблицу с общими доходами от разных отделов (рис. 4.18). Меня огорчает низкий уровень поступлений от отдела сервиса: во многих фирмах сервис приносит не менее 20 % общих доходов. Сколько же должен зарабатывать отдел сервиса, чтобы его доля составила 20 %? Наверное, я сам мог бы создать сложную формулу или подставлять десятки разных чисел, пока не достигну нужного порога в 20 %, но нельзя ли поручить эту работу Excel?

 

Рис. 4.18. Не мучайтесь с подбором данных! Пусть Excel сделает это за вас

Решение

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

1.  Выполните команду Сервис4Подбор параметра. На экране появится диалоговое окно Подбор параметра (рис. 4.19).

 

Рис. 4.19. Задайте требование, а Excel проверит,
можно ли его выполнить

2.   Пока курсор находится в ячейке Установить в ячейке, щелкните на кнопке сверт­ки справа от поля, а затем — на ячейке с формулой, которая должна генерировать конкретный результат (в нашем случае это ячейка E4).

3.   Введите в поле Значение величину, которая должна генерироваться формулой (в нашем примере она составляет 0,2, или 20%).

остерегайтесь ввода процентов при подборе параметра!

Работая с процентами при подборе параметра, многие пользователи ошибаются и вводят неверное значение. В нашем примере можно по ошибке ввести в поле Значение 20 вместо 0,2 или 20 %. Если будет допущена эта ошибка, Excel попытается найти значение E3, которое приведет к 20-кратному (2000 %) увеличению значения F3. Конечно, это невозможно, потому что ячейка E4 генерирует свое значение делением объема продаж по одному отделу на суммарный объем продаж по всем отделам. А это означает, что значение в ячейке F3 всегда будет больше значения в ячейке E3.

4.   Активизируйте поле Изменяя значение ячейки, щелкните сначала на кнопке сверт­ки справа от поля, а затем — на ячейке листа с параметром, который требуется подобрать (в нашем примере это ячейка E3). Разверните окно и щелкните на кнопке OK.

5.   Excel выведет решение задачи (если оно существует). Вы можете либо щелкнуть на кнопке OK, чтобы заменить существующие значения результатом подбора, либо на кнопке Отмена, чтобы вернуться к исходным значениям.

Подбор параметров при работе с диаграммами Excel описан в разделе «Подбор параметра на диаграммах» главы 5.

Поиск решения в задачах
с несколькими переменными

Проблема

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

·         Рекламу должно увидеть как можно больше людей.

·         Бюджет рекламной кампании составляет 200 000 долларов.

·         Я должен разместить не менее 15 рекламных плакатов на автобусах, чтобы выполнить договор с городом.

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

·         Ни в одной области не должно размещаться более 30 реклам.

·         Купить долю рекламной публикации нельзя, поэтому переменные ячейки долж­ны содержать целочисленные значения.

Итак, как подобрать оптимальную комбинацию рекламы для разных расценок и потенциальных аудиторий, показанных на рис. 4.20?

 

Рис. 4.20. Требования должны быть изложены предельно ясно,
чтобы они были понятны Excel

Решение

Механизм подбора параметра работает только при изменении значения в одной ячейке. Для подбора оптимальной комбинации параметров в нескольких ячейках необходимо использовать механизм поиска решения — надстройку для Excel, разработанную в Frontline Systems и бесплатно распространяемую с Excel. Чтобы узнать, установлена ли эта надстройка, раскройте меню Сервис и посмотрите, присутствует ли там команда Поиск решения. Если ее нет, выполните команду Сервис4Надстройки, установите флажок Поиск решения и щелкните на кнопке OK. Если пункт Поиск решения отсутствует в списке диалогового окна Надстройки, в главном меню Windows выберите команду Пуск4Найти4Файлы и папки и проведите поиск файла Solver.xla. Если файл обнаружится, выполните в Excel команду Сервис4Надстройки, щелкните на кнопке Обзор и укажите местонахождение файла. Если файл найти не удастся, вероятно, вам придется запустить программу установки Office и добавить этот компонент.

После того как надстройка будет установлена, команда Сервис4Поиск решения вызывает диалоговое окно Поиск решения, показанное на рис. 4.21.

В диалоговом окне Поиск решения вводятся ограничения задачи. Разместите их в листе рядом с данными — так их будет проще найти при вводе данных.

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

1.  Выделите ячейку G7, в которой хранится максимизируемый показатель (общая аудитория), и выполните команду Сервис4Поиск решения. На экране появится диалоговое окно Поиск решения, в котором в поле Установить целевую ячейку уже будет указана ячейка G7.

 

Рис. 4.21. Надстройка Поиск решения в исходном состоянии

2.  В поле Изменяя ячейки щелкните на кнопке свертки, выделите ячейки E3:E6 и разверните окно.

3.  Щелкните на кнопке Добавить; на экране появится диалоговое окно Добавление ограничения (рис. 4.22).

 

Рис. 4.22. Не предоставляйте данным полной свободы…
Ограничивайте их!

2.4.   Чтобы количество публикаций было целым, щелкните на кнопке свертки, выделите ячейки E3:E6 и разверните окно. Затем раскройте средний список, выберите пункт цел и щелкните на кнопке Добавить.

Если вы собираетесь использовать данное ограничение, всегда добавляйте его первым. Когда я впервые вводил это решение, я создал правило в последнюю очередь, и по какой-то загадочной причине получил значение 29,065 для журнала 1. Тогда я удалил сценарий и ввел его заново, поставив ограничение целочисленности на первое место. На этот раз решение получилось целочисленным, как и предполагалось. Вывод? Excel обрабатывает ограничения в порядке их определения, поэтому ограничения на формат (такое как ограничение целочисленности) следует вводить перед ограничениями на значение.

5.  Установите курсор в поле Ссылка на ячейку, щелкните на кнопке свертки, выделите ячейку F7 и разверните диалоговое окно. Выберите в среднем списке пункт <=, введите в поле Ограничение значение G9 (или выберите эту ячейку на листе, используя кнопку свертки) и щелкните на кнопке Добавить.

6.  Установите курсор в поле Ссылка на ячейку, щелкните на кнопке свертки, выделите ячейки E3:E6 и разверните диалоговое окно. Выберите в среднем списке пункт >=, введите в поле Ограничение значение G11 (или выберите эту ячейку на листе, используя кнопку свертки) и щелкните на кнопке Добавить.

7.  Установите курсор в поле Ссылка на ячейку, щелкните на кнопке свертки, выделите ячейку E3 и разверните диалоговое окно. Выберите в среднем списке пункт >=, введите в поле Ограничение значение G10 (или выберите эту ячейку на листе, используя кнопку свертки) и щелкните на кнопке Добавить.

8.  Установите курсор в поле Ссылка на ячейку, щелкните на кнопке свертки, выделите ячейки E3:E6 и разверните диалоговое окно. Выберите в среднем списке пункт <=, введите в поле Ограничение значение G12 (или выберите эту ячейку на листе, используя кнопку свертки) и щелкните на кнопке OK, чтобы отобразить диалоговое окно Поиск решения с введенными ограничениями.

9.  Щелкните на кнопке Выполнить.

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

 

Рис. 4.23. Все хорошо, что хорошо кончается: надстройка возвращает искомый результат

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

Команды анализа данных в меню

Проблема

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

Решение

Ничего покупать не нужно — достаточно установить надстройку Анализ данных, входящую в комплект поставки Excel. Выполните команду Сервис4Надстройки, установите флажок Пакет анализа, а для полноты картины — еще и флажок Analysis Toolpak-VBA, и щелкните на кнопке OK. Excel включает в меню Сервис команду Анализ данных, и в дальнейшем вы можете использовать дополнительные статистические функции для анализа данных.

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

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

Проблема

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

Решение

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

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

В конфигурации на рис. 4.25 приведены данные по продажам каждого отдела, но вместо группировки по дням в нем проведена группировка по часам (9:00, 10:00 и т. д.). Перед вами наглядное подтверждение мощи сводных таблиц: вы можете быстро переключаться между вариантами представления данных (например, в процессе презентации), демонстрируя структуру продаж по отделам, неделям, дням и даже часам.

 

Рис. 4.24. В этой конфигурации логическая группировка осуществляется
по дням, а не по часам

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

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

·         список не должен содержать пустых строк и пустых столбцов;

·         каждый столбец должен обладать уникальным именем;

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

·         список не может содержать повторяющихся ключей (см. далее).

Обратите внимание: каждая строка списка определяет уникальную совокупность данных. Например, данные записи 2 на рис. 4.26 (строка под заголовками столбцов) содержит информацию о продажах для недели 1, дня 1, часа 9 и отдела кошек. Следующая запись содержит информацию о продажах для недели 1, дня 1, часа 10 и отдела кошек, и т. д., строка за строкой. Каждая строка списка данных соответствует ячейке сводной таблицы.

 

Рис. 4.25. Конфигурация с логической группировкой по часам

 

Рис. 4.26. Данные подготовлены к построению сводной таблицы

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

 

Рис. 4.27. Присутствие в списке двух записей с одинаковыми
данными создает неразрешимый конфликт

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

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

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

Проблема

Все требования выполнены, и мой список данных готов к построению сводной таблицы. Я выполнил команду Данные4Сводная таблица в Excel 97 и попытался пообщаться с мастером сводных таблиц, но некоторые вопросы оказались непонятными, а программа не распознала список данных, которые я собирался использовать. Помогите!

Решение

Чтобы создать сводную таблицу в Excel 97, выполните следующие действия.

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

2.  Установите переключатель В списке или базе данных Microsoft Excel и щелкните на кнопке Далее.

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

4.  Перетащите заголовки полей в нужные позиции макета сводной таблицы. Порядок следования заголовков определяет способ группировки данных в Excel. В нашем случае перетащите поля Неделя, Отдел и День (именно в таком порядке) в область строк, поле Час — в область столбцов, а поле Продажи — в область данных. Завершив перетаскивание, щелкните на кнопке Далее.

5.  Проследите за тем, чтобы в окне был установлен переключатель Новый лист, и щелкните на кнопке Готово.

 

Рис. 4.28. Создание исходного макета сводной таблицы

 

Рис. 4.29. Всего лишь один из вариантов представления
данных сводной таблицы

Процедура создания сводной таблицы в Excel 2000, 2002 и 2003 выглядит так.

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

2.   Установите переключатель В списке или базе данных Microsoft Office Excel и щелк­ните на кнопке Далее.

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

4.   Перетащите заголовки полей в нужные позиции макета сводной таблицы. Порядок следования заголовков определяет способ группировки данных в Excel. Чтобы повторить структуру макета, показанную на рис. 4.29, перетащите поля Неделя, Отдел и День (именно в таком порядке) в область строк, поле Час — в область столбцов, а поле Продажи — в область данных. Завершив перетаскивание, щелкните на кнопке OK.

5.   Проследите за тем, чтобы в следующем окне мастера был установлен переключатель Новый лист, и щелкните на кнопке Готово.

Построение сводных диаграмм описано в главе 5.

 

Рис. 4.30. Серая полоса показывает, где будет находиться заголовок
сводной таблицы при отпускании кнопки мыши

Изменение структуры сводной таблицы

Проблема

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

Решение

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

Фильтрация данных сводной таблицы

Проблема

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

Решение

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

 

Рис. 4.31. Сводные таблицы строятся на базе списков данных.
Их содержимое, как и содержимое списков, можно фильтровать

Чтобы отфильтровать сводную таблицу в Excel 97, сделайте двойной щелчок на заголовке поля — на экране появится диалоговое окно Вычисление поля сводной таблицы (рис. 4.31). Выберите скрываемые значения в списке Скрыть элементы и щелкните на кнопке OK. Чтобы снова отобразить скрытые данные, дважды щелк­ните на заголовке поля и отмените выбор.

Фильтрация данных в сводной таблице не влияет на исходные данные.

Создание расширенных фильтров сводных таблиц

Проблема

Я создал сводную таблицу и хочу показать некоторые варианты представления данных, например 10 дней с максимальными (или минимальными) уровнями продаж для моей фирмы. Я знаю, как решить эту задачу при помощи автофильтра для обычных списков данных; можно ли сделать то же самое со сводной таблицей?

Решение

Начиная с Excel 97, у сводных таблиц появился свой аналог «верхней/нижней десятки», поддерживаемой автофильтрами. Конкретный способ активизации фильтров изменялся по мере развития сводных таблиц, но найти его в любом случае несложно — если, конечно, знаешь, где искать. Допустим, мы используем сводную таблицу, показанную на рис. 4.32.

 

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

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

1.  Щелкните на любой ячейке сводной таблицы и выполните команду Данные4 Сводная таблица.

2.  Сделайте двойной щелчок на заголовке строки или столбца, который будет использоваться для выделения верхнего или нижнего подмножества данных. На экране появится диалоговое окно Вычисление поля сводной таблицы. Щелкните на кнопке Далее, чтобы перейти к окну Дополнительные параметры поля сводной таблицы (рис. 4.33).

 

Рис. 4.33. В окне расширенной настройки присутствуют
элементы управления фильтрацией данных

3.   Установите переключатель Автоматические.

4.   Выберите в списке Отображать пункт Наибольших или Наименьших и укажите количество элементов в поле со счетчиком.

Описанная методика отлично работает в Excel 2002 и 2003, но в этих версиях задача решается и другим, более быстрым способом.

1.    Щелкните на любой ячейке сортируемого поля. Например, чтобы отсортировать показанную на рис. 4.32 сводную таблицу по дням, можно щелкнуть на ячейке C5 (а также на любой ячейке в диапазоне C6:C11 и т. д.).

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

3.    Раскройте меню кнопки Сводная таблица и выберите команду Сортировка и десятка лучших.

4.    Установите переключатель Включено и задайте в поле Отображать количество отображаемых значений в начале или в конце списка.

Фильтрация сводной таблицы по произвольному полю

Проблема

Я создал сводную таблицу со всеми полями, которые должны использоваться для группировки и фильтрации данных. Но затем мне потребовалось отфильтровать сводную таблицу по полям, которые не используются при группировке! Допустим, я хочу создать таблицу вроде показанной на рис. 4.34; при этом значения поля Неделя не будут применяться для группировки данных, но мне хотелось бы сохранить возможность использования поля Неделя для фильтрации данных, отображаемых в сводной таблице. Можно ли это сделать?

Решение

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

 

Рис. 4.34. Сортировка данных по неделям не нужна, но, возможно,
потребуется фильтрация по неделям

 

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

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

Надстройки для работы со сводными таблицами

Проблема

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

Решение

Ниже перечислены надстройки для работы со сводными таблицами.

·         PivotTable AutoFormat XL сохраняет и применяет пользовательские форматы сводных таблиц. Надстройку можно загрузить по адресу http://www.ablebits.com/excel-pivottables-autoformatting-addins/ за 39,95 доллара.

·         PivotTable Helper — бесплатная надстройка для Excel 2000 и более поздних версий, расширяющая возможности PivotTable AutoFormat XL. На панель инструментов Сводная таблица добавляются кнопки для ускоренного выполнения таких операций, как выделение всей таблицы, изменение количества отображаемых разрядов в области данных, добавление и удаление строк с общими итогами. Надстройку можно загрузить по адресу http://www.ablebits.com/excel-pivottables-formatting-assistant-free-addins/index.php.

·         PivotTable Assistant позволяет изменять размеры столбцов для удобства просмотра, задавать заголовки и назначать область печати сводной таблицы, за­креплять строки и столбцы заголовков для упрощения просмотра, а также создавать рамки по правому и нижнему краю каждой печатной страницы сводной таблицы. Надстройку можно загрузить по адресу http://www.ozgrid.com/Services/Excel-pivot-table-assistant.htm за 29,95 доллара.

Автоматическое форматирование сводных таблиц

Проблема

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

Решение

Чтобы применить автоформат к сводной таблице, щелкните на любой ячейке таблицы, выполните команду Формат4Автоформат и выберите применяемый вариант автоформата. Для определения собственного автоформата сводных таблиц воспользуйтесь надстройкой PivotTable AutoFormat XL, о которой говорилось ранее.

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

Проблема

Я создал несколько сводных таблиц в Microsoft Excel 2000 для анализа данных о продажах, однако изменения, вносимые в одной сводной таблице, почему-то отражаются в других таблицах, созданных на базе тех же данных. Что происходит?

Решение

Дело в том, что вы напрасно последовали совету Excel при создании второй
(и последующих) сводных таблиц, базирующихся на тех же данных. При создании второй сводной таблицы на основе прежнего источника данных Excel выводит предупреждение: «Новый отчет будет занимать меньше памяти, если создать его на основе существующего отчета [книга]лист!таблица, имеющего тот же источник данных. Создать отчет на основе того же источника данных, что и существующий отчет?» На первый взгляд предложение выглядит вполне разумно, но в действительности делать этого ни в коем случае не следует.

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

Отображение данных сводных таблиц в виде знаков #

Проблема

Мой список данных занимает немного места, поэтому я создал сводную таблицу на одном листе с исходным списком. Но теперь Excel заменяет часть исходных данных знаками # (#####). Что происходит и как решить эту проблему?

Решение

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

Отключение автоформатирования таблицы в Excel 97 производится следующим образом.

1.   Выделите ячейку, находящуюся в сводной таблице, и выполните команду Дан­ные4Сводная таблица.

2.   Щелкните на кнопке Далее, чтобы перейти к четвертому окну мастера сводных таблиц. Щелкните на кнопке Параметры и сбросьте флажок Автоформат.

В Excel 2000 это делается так.

1.   Щелкните на ячейке, находящейся в сводной таблице, и выполните команду Данные4Сводная таблица.

2.   Щелкните на кнопке Далее, чтобы перейти к третьему окну мастера сводных таблиц.

3.   Щелкните на кнопке Параметры и сбросьте флажок Автоформат.

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

Ошибка «Сводная таблица недействительна»

Проблема

Я работаю администратором в большой фирме, доставляющей товары по почте. В компьютерной службе используется новомодное программное обеспечение, а нам достаются очень старые версии Office. Меня это так раздражало, что я купил собственную копию Office 2000, тогда как все остальные коллеги продолжали использовать Office 97. Я создал сводную таблицу на базе куба OLAP в Excel 2000, отправился в комнату совещаний (где установлен компьютер с Excel 97) и попытался задействовать сводную таблицу в презентации. Но на экране появилось сообщение об ошибке «Сводная таблица недействительна»! Все планы по проведению эффектной презентации пошли прахом. Что же произошло?

OlAP

Технология OLAP (Online Analytical Processing) упрощает анализ и представление больших объемов данных за счет вычисления сводных показателей и структурирования данных в ответ на запросы пользователя. Сводные таблицы Excel умеют вычислять сводные показатели, но не справляются с огромными таблицами баз данных, создаваемыми большинством крупных коммерческих систем.

Решение

Как вы, вероятно, догадались, Excel 97 попросту не умеет работать со сводными таблицами, созданными на базе кубов OLAP. Данная возможность появилась лишь в Excel 2000. Excel 97 отображает сводную таблицу в том состоянии, в котором она была сохранена в Excel 2000, но не производит реструктурирования или обновления данных. Существуют два обходных решения этой проблемы. Первое — создание новой сводной таблицы для каждой конфигурации, которая должна отображаться на компьютере с Excel 97. Я рекомендую размещать сводные таблицы на разных листах и переименовывать листы в соответствии со спецификой каждой таблицы. Другое обходное решение — импортировать данные OLAP в книгу Excel 2000, открыть книгу в Excel 97 и создать новую сводную таблицу.

Потеря форматирования в сводных таблицах

Проблема

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

Решение

Как это ни печально, создание сводной таблицы действительно приводит к потере форматирования данных. Статья Microsoft Knowledge Base #214021 предлагает щелкнуть на кнопке Параметры в предпоследнем (Excel 97 и 2000) или последнем (Excel 2002 и 2003) окне мастера сводных таблиц и установить флажок Сохранять форматирование, но у меня этот флажок не работал ни в одной из версий Excel. Однако я могу сказать, что быстрый способ форматирования данных во всех версиях после Excel 97 все же существует. Щелкните правой кнопкой мыши на любой ячейке области данных и выберите в контекстном меню команду Поле (Excel 97 и 2000) или Параметры поля (Excel 2002 и Excel 2003). На экране появится диалоговое окно Вычисление поля сводной таблицы (рис. 4.36).

 

Рис. 4.36. Вызов диалогового окна форматирования
ячеек сводной таблицы

Щелкните на кнопке Формат и определите формат данных на знакомой вкладке Число диалогового окна Формат ячеек.

Вычисляемые поля в сводных таблицах

Проблема

Сводные таблицы полезны, но мне хотелось бы сделать нечто большее с данными в таблице. Например, 3 % доходов моей компании поступает в фонд накладных расходов, из которого оплачиваются счета за свет, офисные принадлежности
и т. д. Я хочу, чтобы в сводной таблице (рис. 4.37) выводились дополнительные поля с указанием величины 3 % отчислений. Как это сделать?

Решение

В Excel 97 появились понятия вычисляемого поля (пользовательского поля, значение которого определяется заданной формулой) и вычисляемого объекта (пользовательского поля, значение которого строится на базе определенного элемента данных таблицы, например подмножества Машины столбца Отдел). Например, для определения и отображения 3 % отчислений от почасовых объемов продаж создается вычисляемое поле с формулой =Продажи*0,03.

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

1.  Если панель инструментов Сводные таблицы не отображается, вызовите ее коман­дой Вид4Панели инструментов.

2.  Выделите любую ячейку сводной таблицы, затем на панели инструментов Сводные таблицы раскройте меню кнопки Сводная таблица и выберите команду Формулы4Вычисляемое поле. На экране появится диалоговое окно Вставка вычисляемого поля (рис. 4.38).

3.  Введите название вычисляемого поля в поле Имя, введите формулу (например, =Продажи*0,03) в поле Формула, щелкните на кнопках Добавить и OK. Вычисляемое поле появится в области данных сводной таблицы.

 

Рис. 4.37. Сводная таблица отвечает на поставленный вопрос

Удаление вычисляемого поля выполняется следующим образом.

1.  Вызовите панель инструментов Сводные таблицы.

2.  Раскройте меню кнопки Сводная таблица и выберите команду Формулы4Вычисляемое поле. На экране появится диалоговое окно Вставка вычисляемого поля.

 

Рис. 4.38. Создание дополнительного поля
на базе произвольной формулы

3.   Раскройте список Имя, выберите удаляемое поле, щелкните на кнопках Удалить и OK.

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

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

Есть только одна загвоздка. Вернее, две. А еще точнее, три. Во-первых, XLSTAT-Pivot работает только в Excel 2000 и выше, что вполне приемлемо. Во-вторых, эта надстройка требует предварительной установки надстройки XLSTAT-Pro.

Главная же проблема кроется «в-третьих»: эти два приложения обойдутся вам в приличную сумму, равную 1385 долларов (395 долларов за XLSTAT-Pro, 990 долларов за XLSTAT-Pivot). Правда, вы можете загрузить 30-дневные пробные версии обеих программ с сайта компании по адресу http://www.xlstat.com/, так что по крайней мере сможете опробовать их перед покупкой. Также полезно пройти обучающее руководство по XLSTAT-Pivot по адресу http://www.xlstat.com/demo-pivot.htm — оно дает хорошее представление о том, как работает программа.

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

 

Если вы серьезно относитесь к анализу данных, подумайте о включении XLSTAT-Pivot в свой творческий арсенал

Вычисляемые объекты создаются аналогично, с одним исключением: вы должны выбрать уровень группировки для создания вычисляемого объекта. Так, в сводной таблице на рис. 4.37 можно создать вычисляемый объект для суммирования продаж по категориям Аксессуары и Сервис и включения полученных результатов в тело сводной таблицы. Формула создается в формате =Столбец[Значение1]+ Столбец[Значение2], чтобы программа знала, какие элементы должны использоваться в вычислениях. Например, формула для суммирования продаж по отделам Аксессуары и Сервис выглядит так:

=Отдел[Аксессуары]+Отдел[Сервис]

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

1.  Щелкните на заголовке поля, которое будет использоваться в вычислениях (в нашем примере это поле Отдел).

2.  На панели инструментов Сводные таблицы раскройте меню кнопки Сводная таблица и выберите команду Формулы4Вычисляемый объект. На экране появится диалоговое окно Вставка вычисляемого элемента (рис. 4.39).

3.  Введите название вычисляемого объекта в поле Имя, введите формулу для вычисления в поле Формула и щелкните на кнопке Добавить.

 

Рис. 4.39. Создание полей, изменяющихся вместе
с другими данными сводной таблицы

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

Проблема

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

Решение

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

 

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

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

Отсутствие скрытых данных в итогах

Проблема

Мой начальник при помощи сводных таблиц следит за тем, какой вклад вносит каждый отдел в общую прибыль. Однако ему не нравится, что Excel не отображает «отфильтрованный» итог для столбца, — выводится только итог для видимых значений (как показано на рис. 4.42). Можно ли заставить Excel выводить итог для всех данных, как видимых, так и скрытых?

 

Рис. 4.41. Применение другой итоговой операции в сводной таблице

 

Рис. 4.42. Скрытые данные можно включить в вычисления со сводными таблицами

Решение

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

Ошибка «Неверная ссылка»

Проблема

Я использовал форму для заполнения списка данных сводной таблицы. Но когда я попытался создать сводную таблицу на основе этих данных, появилось сообщение об ошибке «Неверная ссылка». Я знаю, что ссылка верна, — в конце концов, это обычный список данных, который ничем не отличается от тех списков, с которыми я работал раньше. Впрочем, я заметил, что во втором окне мастера сводных таблиц Excel почему-то отображает ссылку База_данных (рис. 4.43). Я могу выделить диапазон, прежде чем запускать мастера, но я бы предпочел, чтобы программа распознавала список данных автоматически. Что происходит?

 

Рис. 4.43. Список данных вроде бы должен работать — если бы не одна мелочь

Решение

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

1.   Выделите любую ячейку листа и выполните команду Вставка4Имя4Присвоить. На экране появится диалоговое окно Присвоение имени.

2.   В поле Имя введите текст База_данных.

3.   Сначала щелкните на кнопке Добавить, потом на кнопке Удалить.

Теперь Excel легко распознает список данных.

Отображение данных сводной таблицы в процентах

Проблема

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

 

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

 

Рис. 4.45. Диалоговое окно для выбора способа обобщения
данных сводной таблицы

 

Рис. 4.46. Итоговая сводная таблица с выводом в процентах

Решение

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

1.   Щелкните на любой ячейке сводной таблицы и выполните команду Данные4 Сводная таблица.

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

3.   Раскройте список Дополнительные вычисления и выберите в нем пункт Доля от суммы по столбцу.

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

Криптограммы

Если вам нравятся газетные головоломки с зашифрованными цитатами, обратите внимание на бесплатный генератор криптограмм Аарона Блада (Aaron Blood) по адресу http://www.xl-logic.com/xl_files/games/cryptogram.zip. Правда, вам придется ввести шифруемый текст, как показано на следующем рисунке; договоритесь с группой друзей, чтобы все могли поиграть по очереди.

 

Разгадывание криптограмм — отличный способ убить рабочее время