Створіть спеціальні підсумки для набору даних Excel, де ви будете фільтрувати дані. Також у цьому відео: Використання SUBTOTAL (109,) для підсумовування даних, які були приховані вручну. Скопіюйте точну формулу вниз на один рядок за допомогою Ctrl + Ditto.
Після застосування фільтра було б чудово побачити загальну кількість видимих комірок.
Виділіть порожню комірку під кожним із ваших числових стовпців. Натисніть кнопку AutoSum.
Замість того, щоб вставляти формули SUM, Excel вставляє =SUBTOTAL(9,… )формули. Формула нижче показує загальну кількість лише видимих комірок.
Вставте кілька порожніх рядків над вашими даними. Виріжте формули знизу даних і вставте в рядок 1 із позначкою Загальний видимий.
Тепер, коли ви змінюєте фільтри, навіть якщо дані заповнюють більше одного повного екрана даних, ви побачите підсумки у верхній частині робочого аркуша.
Дякуємо Сему Радаковіцу в команді Excel для фільтрування за виділенням - не за те, що запропонував фільтр за виділенням, а за формалізацію фільтру за виділенням!
Переглянути відео
- Створіть спеціальні підсумки для набору даних Excel, де ви будете фільтрувати дані. Також у цьому відео: Використання SUBTOTAL (109,) для підсумовування даних, які були приховані вручну. Скопіюйте точну формулу вниз на один рядок за допомогою Ctrl + Ditto.
Стенограма відео
Спонсор подкасту - "MrExcel XL", книга, що містить понад 40 підказок Excel, мультфільми Excel, коктейлі, твіти та жарти.
Дізнайтеся Excel з подкасту, серія 1976 - Загальні видимі клітини!
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Звичайно, кожна людина, найперша формула, яку створює більшість людей, - це Автосума. Ви знаходитесь тут, нижче стовпця чисел, ви отримуєте кнопку AutoSum, і вона використовуватиме функцію підсумовування для підсумовування чисел над нею, поки не зустріне формулу або текст. Правда? Або Alt + = робить точно те саме. Або, ще краще, виділіть кілька клітинок, а потім натисніть AutoSum, і він помістить усі ці формули без необхідності підтверджувати формули.
Але ось класний фокус, добре, тож давайте увімкнемо AutoFilter. Тож ми будемо використовувати фокус «Фільтрувати за вибором» із вчорашнього подкасту. Дані фільтруються. Я збираюся перейти до самого першого порожнього рядка під даними, а потім натиснути кнопку AutoSum. Але бачите щось зовсім інше. Замість функції SUM ми отримуємо = SUBTOTAL (9! І що це таке, це загальна кількість видимих комірок. Гаразд, дуже круто, що це працює.
Ось у чому річ: я майже десять років тому у Форт-Вейні, штат Індонезія, проводив семінар з питань енергетичного Excel для Інституту внутрішніх аудиторів. І хтось показав мені цей фокус, і я дуже люблю цей фокус. Я ніколи раніше не бачив фокусу, він був крутий, я сказав, що це чудово, я збираюся додати це до семінару. А потім наступного року я повернувся у Форт-Вейн, і з’являється менеджер цього хлопця, правда, він прийшов не перший рік, бо вважав, що знає все про Excel. Але він повернувся, щоб заявити про цей трюк, і каже: "Слухайте, я чую, що мій фінансовий аналітик розповів вам цей фокус, але він не сказав вам найкращої частини, підніміться вище ваших даних і вставте там два рядки. І тоді як у вас є загальна кількість видимих комірок внизу, виріжте, CTRL + X, і вставте, CTRL + V, добре, що це робить,якщо ви перейдете до іншого клієнта, "Гаразд, то я виберу тут ще кількох клієнтів." А тепер у мене більше клієнтів, ніж поміститься на екрані! І мені не потрібно прокручувати вниз донизу, щоб побачити підсумки, підсумки завжди там, вгорі! "
Гаразд, цей трюк, використовуючи автосуму під даними фільтра, працює, коли ви приховуєте рядки з фільтром. Що робити, якщо ви приховуєте рядки вручну? Отже, тут я вибрав кілька рядків, вже перейшов до Формат, Приховати та показати, Приховати рядки! Отже, тепер у нас є рядки, приховані. І я опускаюсь до дна, і натискаю Alt + = або AutoSum, і це не дає мені функції ПІДВИЩА, це дає мені функцію SUM.
Гаразд, зараз, я збираюся зробити особливу справу, я буду робити CTRL + '(так само, апостроф). Це призведе до точно такої ж формули, не змінюючи посилання на клітинку. Отже, E2: E8 змінився або не змінився, як правило, якщо скопіювати та вставити до зміни E3: E9. Я збираюся змінити це на SUBTOTAL (9, і ви побачите, що воно не працює, воно все одно дає нам усі клітинки. 1000 + 400 + 800 - це не 4500, це включає приховані рядки. Але ось що вам потрібно зробити, увійти в CTRL + 'і змінити 9 на 109, і ви отримаєте: Справжній загальний видимий на основі прихованих рядків, що приховуються вручну!
Гаразд, ось що, тож кілька різних трюків, AutoSum, Alt + = просто надає нам функцію SUM. Якщо ви знаходитесь нижче даних і натискаєте кнопку AutoSum, ви отримаєте ПІДЗНАК (9, який чудово підходить для відфільтрованого), або ви можете просто вручну ввести СУБТОТАЛ (109, щоб отримати загальну кількість видимого, коли Ви вручну відфільтрували.
Добре, ось короткий зміст концепцій у сьогоднішньому відео: Це, до речі, навіть не підказка №3 у книзі, це бонусна підказка No2b, так, між підказкою 2 та підказкою 3. Книга охоплює всі ці предметів. Ну, дякую усім, хто запропонував це, люди з Форт-Уейн Індіана Глава IIA, де я вперше почув це. Це чудова книга, ви можете придбати її в друкованому вигляді або в електронній книзі, клацніть на "i" у верхньому правому куті. Ви отримаєте більше інформації про книгу.
Добре, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
