Нещодавно я написав статтю про те, як використовувати функції зведення в Excel для легкого підсумовування великих обсягів даних, але в цій статті були враховані всі дані на аркуші. Що робити, якщо ви хочете переглянути лише підмножину даних і узагальнити підмножину даних?
В Excel ви можете створювати фільтри для стовпців, які приховуватимуть рядки, які не відповідають фільтру. Крім того, ви також можете використовувати спеціальні функції в Excel для підсумовування даних, використовуючи лише відфільтровані дані.
Зміст
У цій статті я розповім вам про кроки для створення фільтрів у Excel, а також за допомогою вбудованих функцій для узагальнення відфільтрованих даних.
Створення простих фільтрів у Excel
В Excel можна створювати прості фільтри та складні фільтри. Почнемо з простих фільтрів. Працюючи з фільтрами, ви завжди повинні мати один рядок угорі, який використовується для міток. Наявність цього рядка не є обов’язковою, але це полегшує роботу з фільтрами.
Вище я маю деякі підроблені дані, і я хочу створити фільтр у стовпці Місто . В Excel це дуже легко зробити. Перейдіть на вкладку « Дані » на стрічці, а потім натисніть кнопку « Фільтр ». Вам також не потрібно виділяти дані на аркуші або клацати в першому рядку.
Коли ви натискаєте «Фільтр», праворуч до кожного стовпця в першому рядку автоматично додається маленька кнопка спадного меню.
Тепер клацніть стрілку спадного списку в стовпці Місто. Ви побачите кілька різних варіантів, які я поясню нижче.
Угорі ви можете швидко відсортувати всі рядки за значеннями в стовпці Місто. Зауважте, що під час сортування даних буде переміщено весь рядок, а не лише значення в стовпці «Місто». Це гарантує, що ваші дані залишаться недоторканими, як і раніше.
Крім того, доцільно додати спереду стовпець під назвою «Ідентифікатор» і пронумерувати його від одного до будь-якої кількості рядків, які є на аркуші. Таким чином, ви завжди можете відсортувати дані за стовпцем ID і повернути свої дані в тому ж порядку, у якому вони були спочатку, якщо це важливо для вас.
Як бачите, усі дані в електронній таблиці тепер відсортовано на основі значень у стовпці Місто. Поки що жодного рядка не приховано. Тепер давайте подивимося на прапорці внизу діалогового вікна фільтра. У моєму прикладі я маю лише три унікальні значення в стовпці Місто, і ці три відображаються в списку.
Я пішов вперед і зняв позначки з двох міст, а одне залишив позначеним. Зараз у мене лише 8 рядків даних, а решта приховані. Ви можете легко визначити, що переглядаєте відфільтровані дані, якщо перевірите номери рядків у крайньому лівому куті. Залежно від того, скільки рядків приховано, ви побачите кілька додаткових горизонтальних ліній, а колір цифр буде синім.
Тепер, скажімо, я хочу відфільтрувати другий стовпець, щоб ще більше зменшити кількість результатів. У стовпці C у мене є загальна кількість членів у кожній сім’ї, і я хочу бачити лише результати для сімей, у яких більше двох членів.
Далі клацніть стрілку спадного меню в стовпці C, і ви побачите однакові прапорці для кожного унікального значення в стовпці. Однак у цьому випадку ми хочемо натиснути «Фільтри чисел » , а потім натиснути «Більше» . Як бачите, є ще купа інших варіантів.
З’явиться нове діалогове вікно, у якому ви зможете ввести значення для фільтра. Ви також можете додати більше ніж один критерій за допомогою функції І або АБО. Ви можете сказати, що вам потрібні рядки, у яких значення більше 2 і не дорівнює 5, наприклад.
Зараз у мене лише 5 рядків даних: сім’ї лише з Нового Орлеану та з 3 або більше членів. Досить легко? Зауважте, що ви можете легко очистити фільтр для стовпця, клацнувши розкривне меню, а потім клацнувши посилання Очистити фільтр із «Назви стовпця» .
Ось і все для простих фільтрів у Excel. Вони дуже прості у використанні, а результати досить очевидні. Тепер давайте розглянемо складні фільтри за допомогою діалогового вікна « Розширені фільтри».
Створення розширених фільтрів у Excel
Якщо ви бажаєте створити розширені фільтри, скористайтеся діалоговим вікном розширеного фільтра. Наприклад, скажімо, я хочу переглянути всі родини, які живуть у Новому Орлеані з більш ніж 2 членами в сім’ї АБО всі сім’ї в Кларксвіллі з більш ніж 3 членами в сім’ї І лише ті, адреса електронної пошти яких закінчується .EDU . Тепер ви не можете зробити це за допомогою простого фільтра.
Для цього нам потрібно трохи по-іншому налаштувати аркуш Excel. Вставте кілька рядків над набором даних і скопіюйте мітки заголовків точно в перший рядок, як показано нижче.
Ось як працюють розширені фільтри. Спершу потрібно ввести критерії у стовпці вгорі, а потім натиснути кнопку « Додатково » у розділі « Сортування та фільтр » на вкладці « Дані ».
Отже, що саме ми можемо ввести в ці комірки? Гаразд, почнемо з нашого прикладу. Ми хочемо бачити лише дані з Нового Орлеана чи Кларксвілла, тому давайте введемо їх у клітинки E2 і E3.
Коли ви вводите значення в різні рядки, це означає АБО. Тепер нам потрібні сім’ї Нового Орлеана з більш ніж двома членами та сім’ї Кларксвіля з більш ніж 3 членами. Для цього введіть >2 у C2 та >3 у C3.
Оскільки >2 і Новий Орлеан знаходяться в одному рядку, це буде оператор І. Те саме стосується рядка 3 вище. Нарешті, нам потрібні лише сім’ї, адреса електронної пошти яких закінчується .EDU. Для цього просто введіть *.edu в D2 і D3. Символ * означає будь-яку кількість символів.
Зробивши це, клацніть будь-де в наборі даних, а потім натисніть кнопку Додатково . Поле «Діапазон списку » автоматично визначає ваш набір даних, оскільки ви клацнули його перед натисканням кнопки «Додатково». Тепер натисніть маленьку кнопку праворуч від кнопки діапазону критеріїв .
Виберіть усе від A1 до E3, а потім знову натисніть ту саму кнопку, щоб повернутися до діалогового вікна розширеного фільтра. Натисніть OK, і ваші дані тепер будуть відфільтровані!
Як бачите, зараз у мене є лише 3 результати, які відповідають усім цим критеріям. Зауважте, що мітки для діапазону критеріїв мають точно збігатися з мітками для набору даних, щоб це працювало.
Ви, очевидно, можете створювати набагато складніші запити, використовуючи цей метод, тому поекспериментуйте з ним, щоб отримати бажані результати. Нарешті, давайте поговоримо про застосування функцій підсумовування до відфільтрованих даних.
Узагальнення відфільтрованих даних
Тепер, скажімо, я хочу підсумувати кількість членів сім’ї за моїми відфільтрованими даними. Як я можу це зробити? Ну, давайте очистимо наш фільтр, натиснувши кнопку Очистити на стрічці. Не хвилюйтеся, дуже легко повторно застосувати розширений фільтр, просто натиснувши кнопку «Додатково» та знову натиснувши «ОК».
У нижній частині нашого набору даних давайте додамо клітинку під назвою Total , а потім додамо функцію sum, щоб підсумувати загальну кількість членів родини. У моєму прикладі я щойно ввів =SUM(C7:C31) .
Отже, якщо я перегляну всі сім’ї, у мене всього 78 членів. Тепер давайте знову застосуємо наш розширений фільтр і подивимось, що станеться.
Ой! Замість показу правильного числа, 11, я все ще бачу загальну суму 78! Чому так? Ну, функція SUM не ігнорує приховані рядки, тому вона все ще виконує обчислення з використанням усіх рядків. На щастя, є кілька функцій, за допомогою яких можна ігнорувати приховані рядки.
Перший – ПРОМІЖНИЙ ПІДСУМОК . Перш ніж використовувати будь-яку з цих спеціальних функцій, ви захочете очистити фільтр, а потім ввести функцію.
Після того, як фільтр очищено, введіть =SUBTOTAL( , і ви побачите, що з’явиться спадне вікно з купою параметрів. Використовуючи цю функцію, ви спочатку вибираєте тип функції підсумовування, яку хочете використовувати, використовуючи число.
У нашому прикладі я хочу використовувати SUM , тож я б ввів число 9 або просто клацнув на ньому зі спадного меню. Потім введіть кому та виділіть діапазон клітинок.
Коли ви натиснете Enter, ви побачите, що значення 78 таке ж, як і раніше. Однак, якщо зараз застосувати фільтр знову, ми побачимо 11!
Чудово! Це саме те, чого ми хочемо. Тепер ви можете налаштувати свої фільтри, і значення завжди відображатиме лише ті рядки, які зараз відображаються.
Друга функція, яка працює практично так само, як і функція SUBTOTAL, це АГРЕГАТ . Єдина відмінність полягає в тому, що у функції AGGREGATE є інший параметр, де ви повинні вказати, що ви хочете ігнорувати приховані рядки.
Перший параметр – це функція підсумовування, яку ви бажаєте використати, і, як і у випадку з ПРОМІЖНИМ ПІДСУМКОМ, 9 представляє функцію СУММА. У другому варіанті вам потрібно ввести 5, щоб ігнорувати приховані рядки. Останній параметр такий самий і є діапазоном клітинок.
Ви також можете прочитати мою статтю про підсумкові функції, щоб дізнатися, як використовувати функцію AGGREGATE та інші функції, такі як MODE, MEDIAN, AVERAGE тощо.
Сподіваємось, ця стаття дасть вам хорошу відправну точку для створення та використання фільтрів у Excel. Якщо у вас виникли запитання, не соромтеся залишати коментарі. Насолоджуйтесь!
Текстові процесори пройшли довгий шлях з початку 1980-х років, коли Microsoft вперше випустила Microsoft Word для MS-DOS. Його новаторською особливістю було те, що він був розроблений для використання з мишею.
Діаграми Ганта є популярним способом відстеження проектів, особливо для команд, яким потрібно пам’ятати про тривалість завдання. Ефективно використовуючи діаграму Ганта, ви можете переконатися, що різні завдання не заважають одне одному, і навіть визначити, коли одне має закінчитися, щоб інше могло почати.
Ось поширений сценарій: ви отримали документ Word електронною поштою, який потрібно підписати та надіслати назад. Ви можете роздрукувати, підписати, відсканувати та повернути документ, але є простіший, кращий і швидший спосіб вставити підпис у Word.
Якщо ви хочете створювати та друкувати етикетки будь-якого типу, шукайте не далі, ніж Microsoft Word і Excel. Ви можете зберігати дані про етикетки в Excel, а потім отримати ці дані в Word, щоб зберегти або надрукувати етикетки.
Злиття — це функція Microsoft Word, яка допомагає спростити створення персоналізованих листів, етикеток, конвертів, електронних листів і каталогу. Оскільки злиття не входить до числа найпоширеніших функцій MS Word, деякі користувачі можуть не знати, як виконати злиття в Word для створення листів, наклейок і конвертів.
Це завжди жахлива трагедія, коли хтось втрачає щось важливе, над чим працював, тому що не зберіг свій документ належним чином. З користувачами Excel і Word це трапляється частіше, ніж ви думаєте.
Коли більшість людей думають про сортування тексту в програмі, вони думають про сортування клітинок в електронній таблиці Excel. Однак ви можете сортувати текст у Word, якщо є щось, що повідомляє Word, де починаються та закінчуються різні частини тексту.
Гістограма — це тип діаграми, який можна створити з даних у Excel. Це полегшує узагальнення частоти певних значень у вашому наборі даних.
Microsoft Word може зробити набагато більше, ніж просто складання звітів і резюме. Він має потужний набір графічних інструментів, які допоможуть вам створювати документи, збагачені графікою, як-от вітальні листівки.
Асоціація сучасної мови (MLA) — це організація, яка надає рекомендації для професійних та академічних письменників. Зараз багато університетів, роботодавців і професійних агентств вимагають від авторів відповідності стилю MLA, оскільки він простий у використанні та послідовний.
За умовчанням, якщо ви ввійшли в свої програми Office за допомогою облікового запису Microsoft, ваші програми зберігають ваші документи в сховищі OneDrive. Це спонукає вас зберігати свої файли в хмарі, щоб мати доступ до них на інших синхронізованих пристроях.
Якщо ви використовуєте OneDrive для резервного копіювання бібліотеки фотографій, він регулярно надсилає вам спогади електронною поштою — зображення та відео того самого дня минулих років. Ось як вимкнути їх на Windows, Android, iPhone та iPad.
Якщо ви коли-небудь працювали з кількома книгами в Excel, ви знаєте, що іноді можуть виникнути проблеми, якщо всі книги відкриті в одному екземплярі Excel. Наприклад, якщо ви перераховуєте всі формули, це буде зроблено для всіх відкритих робочих книг в одному екземплярі.
Якщо ви хочете надіслати кореспонденцію професійного вигляду, нехай перше, що побачить ваш одержувач, буде брудним рукописним конвертом. Візьміть конверт, помістіть його в принтер і введіть ім’я та адресу за допомогою Microsoft Word.
Чи знаєте ви, що в Word можна створювати форми, які люди зможуть заповнювати. Коли ви чуєте про заповнювані форми, це майже завжди стосується документів Adobe і PDF, оскільки це найпопулярніший формат.
Microsoft Word необхідний кожному, хто виконує будь-яку роботу з документами. Слова існують так давно, що без них неможливо уявити роботу в офісі, у школі чи будь-яку іншу цифрову роботу.
Виконання одних і тих самих дій знову і знову не просто нудно, це також може бути марною тратою часу та зниженням продуктивності. Це особливо вірно для початківців користувачів Excel, які можуть не усвідомлювати, що легко автоматизувати типові завдання, записавши макрос.
Математично ви обчислюєте діапазон, віднімаючи мінімальне значення від максимального значення певного набору даних. Він представляє розкид значень у наборі даних і корисний для вимірювання мінливості – чим більший діапазон, тим більш розкиданими та мінливими є ваші дані.
Якщо у вас є аркуш Excel із багатьма датами, швидше за все, вам знадобиться обчисли��и різницю між деякими з цих дат. Можливо, ви хочете дізнатися, скільки місяців вам знадобилося, щоб погасити свій борг, або скільки днів вам знадобилося, щоб схуднути.
Якщо ви використовуєте Excel щодня, то, напевно, ви стикалися з ситуаціями, коли вам потрібно було щось приховати на робочому аркуші Excel. Можливо, у вас є додаткові аркуші даних, на які є посилання, але їх не потрібно переглядати.
Дізнайтеся, як ефективно заблокувати чи розблокувати користувачів на LinkedIn з нашими зрозумілими інструкціями.
Дізнайтеся, як приховати свою активність на Facebook, щоб інші не бачили, за ким ви стежите. Відкрийте налаштування конфіденційності та захистіть свою інформацію.
Дізнайтеся, як приховати своє прізвище у Facebook за допомогою налаштувань мови та конфіденційності для збереження вашої анонімності.
Дізнайтеся, як вирішити проблему «Відео на Facebook не відтворюється» за допомогою 12 перевірених методів.
Дізнайтеся, як додати фігури в Google Docs за допомогою Google Drawings, зображень та таблиць. Використовуйте ці методи для покращення ваших документів.
Google Таблиці є чудовою альтернативою дорогим програмам для підвищення продуктивності, як-от Excel. Наш посібник допоможе вам легко видалити лінії сітки з Google Таблиць.
Дізнайтеся, чому YouTube не зберігає вашу історію переглядів і як можна це виправити. Вимкніть блокувальник реклами та перевірте інші рішення для відновлення історії.
Дізнайтеся, як налаштувати дзеркальне відображення веб-камери в Zoom для природного вигляду вашого відео під час дзвінків.
У Twitter у вас є два способи ідентифікувати себе: за допомогою ідентифікатора Twitter і відображуваного імені. Дізнайтеся, як правильно змінити ці дані для кращого використання Twitter.
Якщо ваша PlayStation 5 перегрівається, дізнайтеся 6 ефективних способів охолодження для запобігання пошкодженням.
