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

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

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

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

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

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

1. В панели инструментов Стандартная нажмите кнопку Открыть (Open). Появится диалоговое окно Открытие документа (Open).
2. Дважды щелкните на файле Calculations.xls. Документ Calculations.xls откроется.
3. Перейдите к листу Январь, щелкнув на соответствующем ярлычке.
4. Щелкните на ячейке Р5.
5. В меню Данные (Data) укажите на пункт Фильтр (Filter) и выберите Автофильтр (AutoFilter).

   В ячейке Р5 появится кнопка списка.
6. Нажмите кнопку списка в ячейке Р5 и выберите из списка Первые 10: (Top 10:).

   Откроется диалоговое окно Первые 10 - Автофильтр (Top 10 AutoFilter).
7. Нажмите OK. Окно Первые 10 - Автофильтр (Top 10 AutoFilter) закроется, и в столбце Р отобразятся строки с наибольшими десятью значениями.
   Совет. Нажатие кнопки OK в данном окне установит опцию по умолчанию - отображение первых десяти значений в выделенных ячейках.
8. Щелкните на ячейке Р6 и перетащите указатель на ячейку Р27.

   Ячейки выделятся, и в области автовычисления в правом нижнем углу окна Excel отобразится СУММ=36781.

   

   
   
9. Щелкните на ячейке Р37 и затем на панели инструментов Стандартная нажмите кнопку Автосумма. В строке формул отобразится формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9,Р6:Р36).
10. Нажмите (Enter). Значение "36781" отобразится в ячейке Р37. Значение в ячейке Р38 также изменится на "134706", но это значение включает в себя сумму отфильтрованных ячеек в столбце.
11. Щелкните на ячейке Р37 и затем в строке формул отредактируйте формулу таким образом, чтобы она имела вид =СУММ(1,Р6:Р36) и нажмите (Enter).

    После замены "9" на "1" функция СУММ будет подсчитывать среднее значение вместо суммы. В ячейке Р37 отобразится среднее из десяти наибольших значений ячеек с Р6 по Р36 - "3678.1". Значение в ячейке Р38 также сменится на "101603.1", но этот результат включает в себя среднее значение отфильтрованных ячеек в столбце.
12. Щелкните на ячейке Р37 и нажмите (Delete). Excel удалит формулу СУММ из ячейки Р37, и сумма в ячейке Р38 изменится на "97925".
13. На панели инструментов Стандартная нажмите кнопку Сохранить (Save). Excel сохранит ваши изменения.
14. Нажмите кнопку Закрыть (Close). Документ Calculations.xls закроется.