Створення та ведення списків – ЧАСТИНА 9

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

► заголовок над обчислюваним умовою повинен відрізнятися від заголовка будь-якого із стовпців списку Він може бути порожнім або може містити довільний текст

► посилання на комірки, що знаходяться поза списком, повинні бути абсолютними

► посилання на комірки у списку оформляйте як відносні, якщо не посилаєтеся на весь рядок

Тепер розглянемо декілька прикладів

Посилання на осередки, що знаходяться в списку

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

Рис 2829 Обчислюване умова в комірці А2 повертає список всіх будинків з ціною квадратного фута менше $ 50

Значення, що відображається у клітинці А2, не представляє інтересу У даному випадку ІСТИНА всього лише означає, що умова виконується для першого рядка списку Як ми побачимо надалі, що обчислюється умова може повертати навіть значення помилки

У деяких рядках вихідного списку поле Площа пусте Поділ на порожнє значення завжди повертає помилку # СПРАВ / 0 (# DIV / 0) Але це не створює проблем Коли програма переглядає такий рядок, вона порівнює # СПРАВ / 0 з 50, і результатом порівняння є також значення # СПРАВ / 0. Оскільки умова не є істинним, рядок, що містить пусте значення в полі, не включається в кінцевий набір фільтра

Якщо стовпцях списку присвоєні імена, їх можна підставити в яке обчислюється умова замість посилань на комірки Наприклад, з іменами Ціна і Площа, присвоєними відповідним стовпцях, умова в комірці А2 прийме наступний вигляд:

= Ціна / Площа <50.

Це обчислюється умова поверне помилку # VALUE в осередок А2 (так як формула знаходиться над списком, а не поруч з ним), але фільтр буде працювати прекрасно (Можна позбутися від значення помилки, натиснувши клавіші Ctrl + Shift + Enter і помістивши таким способом в клітинку А2 формулу масиву, але після цього фільтр перестане фільтрувати)

Посилання на клітинку, що знаходиться поза списком

Обчислюване умова в комірці А2 на рис 2830 порівнює вартість будинку з медіаною всіх цін, яка поміщена поза списком в осередок H1 (Медіана обчислюється за допомогою формули = МЕДИАНА (ціна), деціпа –імя, присвоєне всім осередкам в стовпці Ціна) У цьому умови фігурує абсолютне посилання на клітинку H1 Якщо посилання була б відносної, програма порівнювала б ціну в першому рядку списку з H1, ціну у другому рядку – з Н2 і т д

Посилання на весь рядок

Якщо на рис 2830 ви зміните формулу в комірці А2 на = D6> MEflHAHA ($ D $ 6: $ D $ 238), результат застосування фільтра буде тим же У цьому випадку функція МЕДИАНА посилається на комірки всередині списку, але при цьому використовуються абсолютні посилання Інакше Excel при обчисленні медіани буде переглядати діапазон $ D $ 6: $ D $ 238,

$ D $ 7: $ D $ 239 і т д (Абсолютну посилання на стовпець можна замінити відносної,

обовязково зберігши абсолютні посилання на рядки)

Копіювання відібраних даних в інше місце

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

Рис 2830 У обчислюваному умови в комірці А2 використовується абсолютне посилання на клітинку H1, оскільки остання перебуває за межами списку

Найпростіший спосіб вказівки діапазону – клацнути на порожньому осередку листа, з якою програма повинна почати вставляти копійовані дані Тільки переконайтеся, що нижче і правіше цього осередку достатньо вільного простору Excel скопіює заголовки стовпців і всі рядки, що задовольняють заданим умовам відбору Будьте обережні, оскільки дані можуть перекрити собою наявну інформацію без жодного попередження Для більшої безпеки вказуйте діапазон, а не одну клітинку Якщо копійовані дані не поміщаються у вказаний діапазон, Excel запросить у вас дозвіл на продовження запису даних нижче цього діапазону Якщо ви недадіте дозволу, буде скопійовано стільки рядків, скільки здатний вмістити цільовий діапазон

РАДА

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

Щоб скопіювати тільки певні стовпці списку в нове місце, створіть копії заголовків для цих стовпців Потім вкажіть діапазон, що містить всі копії заголовків, в поле Помістити результат в діапазон

Відбір тільки унікальних рядків

Прапорець Лише унікальні записи (Unique Records Only) – це додатковий фільтр до заданих умов відбору Він відкидає рядки, що повністю повторюють уже відібрані рядки (у всіх стовпцях списку, а не тільки в видобутих стовпцях)

Обчислення підсумків для відфільтрованих даних

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

ДИВИСЬ ТАКОЖ

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

Проміжні підсумки в аналізі списків

Звичайно, сортування і фільтрація даних не самоціль Над отриманим списком можна виконувати і інші дії, наприклад представляти різну підсумкову інформацію за допомогою команди Підсумки (Subtotals) меню Дані Типовий приклад застосування команди Підсумки – обчислення підсумкових даних обсягу продажів по продавцям і покупцям або витрат по одержувачам і категоріям Ця команда додає рядки проміжних результатів для кожної групи елементів списку Обчислювати підсумки на рівні групи можна на основі різних функцій: середнє значення, кількість рядків або порожніх клітинок, стандартне відхилення і т д

ПРИМІТКА

Команда Зведена таблиця, розглянута в главі 30 «Аналіз даних за допомогою зведених таблиць», надає більш широкі можливості для вирішення подібних завдань

Продемонструємо роботу команди Підсумки на прикладі списку перерахувань коштів за поточними видатками На рис 2831 представлений такий список, що містить стовпці для дати, одержувача, категорії витрат і суми переказу В даний момент він відсортований за датами

Щоб дізнатися, скільки коштів було витрачено по кожній статті витрат, спочатку отсортируйте список по категорії Потім виберіть команду Підсумки в меню Дані та заповніть діалогове вікно Проміжні підсумки (Subtotals), як показано на рис 2832

Рис 2831 Ми будемо використовувати команду Підсумки для аналізу цього списку витрат

Рис 2832Діалогове вікно Проміжні підсумки заповнене нами для обчислення проміжних підсумків по кожній статті витрат

У нашому випадку ми поставили перед Excel таку задачу:

► створити підсумкову формулу для кожної зміни в стовпці Категорія

► побудувати формулу, що включає в себе функцію СУММ (SUM), і застосувати її до колонку Витрати (тобто скласти всі значення в стовпці Витрати всередині кожної групи)

► помістити проміжні підсумки нижче кожної групи, а загальні підсумки – в самому низу списку (Якщо ви знімете прапорець Підсумки під даними (Summary Below Data), проміжні результати відобразяться над кожною групою, а загальні підсумки будуть поміщені на початку списку)

Результат застосування команди Підсумки продемонстрований на рис 2833

Рис 2833 Тепер список містить подсумми для кожної статті бюджету

Зверніть увагу на знаки рівня рядка ліворуч від списку («плюс» і «мінус») Ви можете використовувати символи структури для зміни складу відображення даних згідно їх рівню Щоб показати тільки загальні підсумки, клацніть на знаку рядка рівня 1 Якщо ви хочете побачити тільки проміжні результати і загальні підсумки, клацніть на знаку рядка рівня 2 (рис 2834)

Ще один варіант корисного застосування структури – сортування списку за значеннями проміжних підсумків Список, показаний на рис 2834, можна змінити таким чином, щоб категорії з найбільшими витратами відображалися у верхній частині списку

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*