Создание и ведение списков – ЧАСТЬ 9

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

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

? ссылки на ячейки, находящиеся  вне списка, должны быть абсолютными;

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

Теперь рассмотрим несколько примеров.

Ссылки на ячейки, находящиеся в списке

В  ячейку  А2  на  рис. 28.29  мы  ввели   условие   =D6/B6<50,  чтобы   найти дома  со стоимостью квадратного фута ниже $50. Обратите внимание, что заголовок над условием (в ячейке А1) не совпадает  ни с одним  из заголовков в списке  и в формуле используются относительные ссылки на  ячейки  внутри  списка. Excel  сначала  вычисляет формулу, отталкиваясь от  значений в  ячейках D6 и  В6, и  сравнивает результат с 50. Поскольку ссылки относительные, Excel продолжает вычисления с ячейками D7 и В7, D8 и В8 и т. д.

Рис.  28.29.  Вычисляемое  условие  в  ячейке  А2  возвращает  список  всех  домов  с  ценой квадратного фута меньше $50

Значение, отображаемое  в   ячейке  А2,  не  представляет интереса. В   данном  случае ИСТИНА всего лишь означает, что условие выполняется для первой строки списка. Как мы  увидим   в   дальнейшем,  вычисляемое   условие   может  возвращать даже  значение ошибки.

В  некоторых  строках  исходного списка поле  Площадь  пустое.   Деление на  пустое значение всегда возвращает ошибку #ДЕЛ/0! (#DIV/0!). Но это не создает проблем. Когда программа просматривает  такую строку,  она сравнивает  #ДЕЛ/0! с 50, и  результатом сравнения является также значение #ДЕЛ/0!. Поскольку условие не является  истинным, строка, содержащая пустое значение в поле, не включается в конечный набор фильтра.

Если столбцам списка присвоены имена, их  можно подставить в  вычисляемое  условие вместо ссылок  на  ячейки. Например, с  именами  Цена  и   Площадь,  присвоенными соответствующим  столбцам,    условие     в    ячейке    А2    примет   следующий   вид:

=Цена/Площадь<50.

Это  вычисляемое  условие  возвратит  ошибку #ЗНАЧ! в   ячейку  А2 (так  как  формула находится над списком, а не рядом с ним), но фильтр будет работать прекрасно. (Можно избавиться  от  значения   ошибки,  нажав  клавиши  Ctrl+Shift+ Enter и   поместив  таким способом в ячейку А2 формулу массива, но после этого фильтр перестанет фильтровать!)

Ссылка на ячейку, находящуюся вне списка

Вычисляемое условие в  ячейке А2 на рис. 28.30 сравнивает стоимость  дома с медианой всех цен, которая помещена вне списка   в ячейку H1. (Медиана вычисляется с помощью формулы  =МЕДИАНА(цена),  где  цепа  —  имя,  присвоенное  всем ячейкам  в   столбце Цена). В этом условии фигурирует абсолютная ссылка на ячейку H1. Если ссылка была бы относительной, программа сравнивала бы цену в первой   строке списка с H1, цену во второй строке — с Н2 и т. д.

Ссылка на все ячейки столбца

Если на рис. 28.30 вы измените формулу в ячейке А2 на =D6>MEflHAHA($D$6:$D$238), результат  применения  фильтра будет  тем  же.  В  данном  случае  функция МЕДИАНА ссылается  на  ячейки  внутри  списка, но  при  этом  используются  абсолютные  ссылки. Иначе  Excel  при вычислении  медианы будет  просматривать  диапазон  $D$6:$D$238,

$D$7:$D$239 и  т. д. (Абсолютную ссылку на столбец можно заменить  относительной,

обязательно сохранив абсолютные ссылки на строки.)

Копирование отобранных данных в другое место

Диалоговое  окно    Расширенный   фильтр   предоставляет    возможность    копировать отобранные строки  в другое место рабочего листа вместо отображения отфильтрованного списка.  Чтобы   скопировать отобранные   данные,   в    окне   Расширенный   фильтр установите переключатель в положение Скопировать результат в другое место (Copy To  Another  Location)  и   затем  укажите имя или   адрес  конечного  диапазона в   поле Поместить результат в диапазон (Сору То).

Рис.  28.30. В  вычисляемом  условии  в  ячейке А2  используется  абсолютная  ссылка на ячейку H1, поскольку последняя находится за пределами списка

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

СОВЕТ

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

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

Отбор только уникальных строк

Флажок Только уникальные записи (Unique Records Only) — это  дополнительный фильтр к заданным условиям отбора. Он отбрасывает  строки, полностью повторяющие уже отобранные строки (во всех столбцах списка, а не только в извлекаемых столбцах).

Вычисление итогов для отфильтрованных данных

Для    вычисления  итогов  по    отфильтрованным  данным    используйте     функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ   (SUBTOTAL),   а    не    обыкновенные   статистические функции. Дело в  том, что обычные статистические функции, такие как СУММА (SUM), СРЕДНЕЕ   (AVERAGE),   производят вычисления   над  всеми строками в   указанном диапазоне, включая строки, скрытые  вами   вручную или   в   результате   фильтрации. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ сканирует только видимые строки.

СМОТРИ ТАКЖЕ  

Информация   о   функции   ПРОМЕЖУТОЧНЫЕ.ИТОГИ   приведена   в   разделе   «Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ» данной главы.

Промежуточные итоги в анализе списков

Конечно, сортировка и фильтрация данных не самоцель. Над полученным списком можно выполнять и другие действия, например представлять различную итоговую информацию с помощью  команды  Итоги  (Subtotals)  меню  Данные.  Типичный  пример применения команды  Итоги  —   вычисление   итоговых данных  объема  продаж  по  продавцам и покупателям или расходов по получателям и  категориям. Эта команда добавляет строки промежуточных результатов для каждой группы  элементов списка. Вычислять итоги на уровне группы можно на основе различных функций: среднее значение, количество строк или пустых ячеек, стандартное отклонение и т. д.

ПРИМЕЧАНИЕ

Команда  Сводная  таблица,  рассматриваемая  в  главе  30  «Анализ  данных  при  помощи сводных  таблиц»,  предоставляет  более  широкие  возможности  для  решения  подобных задач.

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

Чтобы  узнать,  сколько  средств было  затрачено  по  каждой  статье  расходов, сначала отсортируйте список по категории.  Затем выберите  команду Итоги в  меню Данные и заполните диалоговое  окно  Промежуточные итоги (Subtotals),  как  показано  на  рис. 28.32.

Рис. 28.31. Мы будем использовать команду Итоги для анализа этого списка расходов

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

В нашем случае мы поставили перед Excel следующую задачу:

? создать итоговую формулу для каждого изменения  в столбце Категория;

? построить формулу, включающую в  себя функцию СУММ (SUM), и  применить ее к столбцу Затраты (то есть сложить все значения в  столбце Затраты внутри  каждой группы);

? поместить промежуточные итоги ниже каждой группы, а общие итоги — в самом низу списка. (Если вы снимете флажок  Итоги  под  данными  (Summary  Below Data), промежуточные результаты отобразятся над  каждой группой, а общие итоги  будут помещены в начале списка.)

Результат применения команды Итоги продемонстрирован на рис. 28.33.

Рис. 28.33. Теперь список содержит подсуммы для каждой статьи бюджета

Обратите внимание  на знаки уровня  строки слева от списка («плюс» и  «минус»). Вы можете использовать  символы структуры для изменения  состава отображения данных согласно их  уровню. Чтобы показать только  общие итоги,  щелкните на знаке строки уровня 1. Если вы хотите увидеть  только промежуточные результаты и  общие итоги, щелкните  на знаке строки уровня 2 (рис. 28.34).

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

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий