Під час роботи з великими наборами даних у Microsoft Excel розбіжності в даних неминучі. Виявлення та усунення цих невідповідностей має вирішальне значення для забезпечення точного звітування та аналізу.
Хороші новини? містить різноманітні інструменти та методи, які допоможуть вам знайти та вирішити ці проблеми. У цій статті ви дізнаєтеся про деякі з найефективніших методів. Це включає фільтрування, умовне форматування та використання розширених формул.
Впровадивши ці методи у свій повсякденний робочий процес, ви станете більш ефективними в управлінні та аналізі своїх даних. Зрештою це веде до кращого прийняття рішень і покращення результатів ваших проектів.
Зміст
Розуміння розбіжностей у Excel
Розбіжності в рядках і стовпцях можуть бути наслідком помилок, введених людиною, повторюваних записів або неправильного форматування даних.
Зверніть увагу на наступні дані. Ви помітите, що стать Волтера Вайта та Вейна Сіни написана з помилкою – Mael замість Male. Дійсно, помітити та виправити помилки в цьому сценарії просто. Але це не стосується електронних таблиць, які містять тисячі рядків і стовпців.
Щоб знайти й усунути ці розбіжності, ви можете скористатися такими функціями:
Розуміючи, як використовувати ці функції в Excel, ви зможете ефективно виявляти та виправляти розбіжності у своїх наборах даних.
5 способів знайти розбіжності в Excel
Існує 5 основних способів усунення невідповідностей у Excel:
Давайте пробіжимося по кожному з них, щоб ви могли знайти найкращий спосіб, який вам підходить.
№1 – Пошук розбіжностей за допомогою перевірки вручну в Excel
Під час роботи з , перевірка вручну може бути корисним методом виявлення розбіжностей у ваших даних. Цей процес вимагає ретельного вивчення наборів даних, щоб виявити будь-які невідповідності або помилки.
Нижче наведено кілька дій, які можна виконати, щоб виконати перевірку вручну.
Коли вручну перевіряєте дані на наявність розбіжностей в електронній таблиці Excel, ключем є терпіння. Цей процес займає багато часу. Але це хороша відправна точка для виявлення потенційних помилок.
№2 – Використання фільтра в Excel для виявлення та виправлення суперечливих даних
Один із найпростіших способів виявити суперечливі дані — це використовувати фільтр Excel .
За допомогою фільтра ми виділимо неправильні дані в стовпці «Стать» у такому наборі даних:
1. Виберіть «Сортування та фільтр» на вкладці «Головна» .
2. Виберіть «Фільтр» — переконайтеся, що курсор знаходиться в діапазоні даних.
3. Клацніть стрілку розкривного списку в стовпці з невідповідними даними.
4. Тепер зніміть вибір усіх правильних відповідних даних. У нашому випадку це Male . Потім натисніть OK , щоб зберегти зміни.
5. Ваш аркуш Excel тепер відображатиме лише рядки з неправильними значеннями. Щоб виправити їх, натисніть «Знайти та вибрати» на вкладці «Домашня сторінка».
6. Натисніть «Замінити» , коли з’явиться розкривний список.
7. Введіть неправильне значення в поле Знайти, а правильне – у поле Замінити на . Потім натисніть «Замінити все» .
8. З’явиться діалогове вікно з підтвердженням змін. Натисніть OK , щоб продовжити.
3# – Виділення невідповідностей за допомогою умовного форматування
Ще один спосіб виявити відмінності стовпців і рядків — це використовувати умовне форматування.
Ви можете отримати доступ до таких правил, як виділення клітинок, верх/низ, панелі даних, колірні шкали та набори заліза. Більше того, ви можете створити нове правило та керувати або очистити попередні правила.
Ми використаємо зразок таблиці, щоб показати вам можливості умовного форматування:
1. Виберіть свою таблицю або потрібні рядки та стовпці. Ми вибираємо стовпець A (компанія) і стовпець B (країна). Потім виберіть «Умовне форматування» на вкладці «Домашня сторінка».
2. Виберіть тип правила, а потім правило. Ми вибираємо правила виділення клітинок і повторювані значення…
3. Тепер нам потрібно вибрати, чи потрібно виділяти повторювані чи унікальні значення, а також колір заливки та шрифт для форматування клітинок. Після завершення натисніть OK .
4. Усі дубльовані значення тепер виділено світло-червоною заливкою з темно-червоним текстом.
№4 – Пошук невідповідностей за допомогою розширених функцій Excel
Функції IF та IS
Щоб порівняти комірки та виявити відмінності, ви можете використовувати функції IF та IS.
Наприклад, ми використовували формулу =IF(A2=B2,"MATCH","NOT MATCHED")для порівняння клітинок A2 і B2. І ми перетягнули формулу, щоб застосувати її до інших клітинок. Отже, ідентичні комірки повертають MATCH , інакше NOT MATCHED .
Ви можете поєднати IF з функціями IS, як-от ISNUMBER або ISTEXT, щоб перевірити певні типи даних. Наприклад, формула =IF(ISNUMBER(B2), "Number", "NaN")поверне число в стовпці c, якщо B2 є числом, і NaN в іншому випадку.
VLOOKUP, HLOOKUP & XLOOKUP
Функції VLOOKUP (по вертикалі) і HLOOKUP (по горизонталі) корисні під час порівняння значень у різних стовпцях або рядках двох таблиць. Для цього скористайтеся такою формулою: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) або =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
Замініть lookup_value на значення, яке ви хочете знайти в іншій таблиці, table_array на діапазон клітинок у другій таблиці, col_index_num або row_index_num на номер індексу стовпця чи рядка, з якого ви хочете повернути значення, а [range_lookup] на FALSE для точних збігів або TRUE для приблизного збігу.
У наступному підручнику ми використовуємо функцію VLOOKUP у стовпці E, щоб порівняти імена працівників у стовпцях A та D, щоб отримати їхні зарплати зі стовпця B, якщо знайдено відповідність.
XLOOKUP — це оновлена версія VLOOKUP і HLOOKUP, доступна в Excel 2021 і Excel 365. Формула XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).
Замініть lookup_value на значення, яке ви хочете знайти в іншій таблиці, lookup_array на діапазон комірок для пошуку в другій таблиці, return_array на масив, з якого ви хочете повернути значення, і [if_nout_found] на текстове значення, якщо відповідності немає значення знайдені.
Ось короткий зразок XLOOKUP із тими самими таблицями:
МАТЧ
Функцію MATCH також можна використовувати для порівняння двох списків на розбіжності.
Функція MATCH =MATCH(lookup_value, lookup_array, [match_type])шукає вказаний елемент у діапазоні та повертає відносну позицію елемента в цьому діапазоні.
Тут ми знаходимо позицію кавунів у масиві A2:A8 за допомогою MATCH. Ми використовуємо match_type як 0 , щоб знайти перше значення, яке точно дорівнює lookup_value .
№5 – Використання надбудов Excel для виявлення розбіжностей
Excel пропонує різні надбудови та вбудовані інструменти, які можуть допомогти вам ефективніше виявляти й аналізувати розбіжності. У цьому розділі ми обговоримо деякі з цих корисних надбудов.
По-перше, розгляньте можливість використання надбудови Spreadsheet Inquire . Це може допомогти вам порівняти дві книги та виділити відмінності клітинку за клітинкою. Щоб увімкнути надбудову Spreadsheet Inquire:
1. Натисніть вкладку Файл .
2. Виберіть Параметри , а потім клацніть категорію Надбудови .
3. У полі «Керування» виберіть надбудови COM і натисніть «Перейти» .
4. Поставте прапорець у полі Запит електронної таблиці та натисніть OK .
5. Після ввімкнення перейдіть на вкладку «Запит» , щоб скористатися командою «Порівняти файли» .
Іншим корисним інструментом для виявлення розбіжностей є Analysis ToolPak . Ця надбудова надає розширені статистичні функції та інструменти аналізу даних, які можуть бути корисними під час аналізу розбіжностей.
Щоб увімкнути Analysis ToolPak, виконайте ті самі дії, що й для ввімкнення надбудови Spreadsheet Inquire. Але виберіть «Надбудови Excel» у полі «Керування» та встановіть прапорець «Пакет інструментів аналізу» .
Підсумовуючи, Excel пропонує різні надбудови, які допоможуть вам виявити й усунути розбіжності в даних. Ви можете підвищити точність своїх даних, ознайомившись із цими інструментами та формулами.
Як ви можете створити правила перевірки даних, щоб запобігти розбіжностям?
Щоб створити правила перевірки даних у Excel, які допоможуть запобігти розбіжностям, виконайте такі дії:
1. Виберіть клітинки, які потрібно обмежити. Це може бути одна клітинка, діапазон клітинок або цілий стовпець.
2. Перейдіть на вкладку «Дані» на панелі інструментів і натисніть кнопку «Перевірка даних» (позначену двома горизонтальними прямокутниками, зеленою галочкою та червоним кружком).
3. У вікні перевірки даних переконайтеся, що ви перебуваєте на вкладці Параметри . Тут ви матимете можливість визначити критерії підтвердження відповідно до ваших потреб.
В Excel доступні різні типи критеріїв перевірки, деякі з яких включають:
Після вибору відповідних критеріїв перевірки вкажіть необхідні параметри. Наприклад, якщо ви вибрали «Ціле число», вам потрібно буде встановити мінімум і максимум за допомогою засобу перевірки діапазону даних , як-от між, дорівнює, менше тощо.
Окрім критеріїв перевірки, ви також можете налаштувати власні повідомлення про помилки та підказки щодо введення, щоб допомогти користувачам зрозуміти та дотримуватися ваших правил перевірки. Для цього перейдіть на вкладки «Вхідне повідомлення» та «Попередження про помилку », щоб ввести потрібні повідомлення.
Впроваджуючи правила перевірки даних, ви значно зменшуєте ймовірність невідповідності даних і покращуєте загальну точність ваших робочих аркушів Excel.
Наше заключне слово
У цій статті ви дізналися про різні методи пошуку та усунення розбіжностей у даних Excel. Давайте підсумуємо ці ключові стратегії:
Завдяки цим методам у вашому наборі навичок Excel ваші дані будуть точнішими та надійнішими.
Не забувайте практикувати ці методи регулярно, щоб ефективніше виявляти розбіжності у ваших електронних таблицях.
Продовжуйте вдосконалювати свої навички, зареєструвавшись на нашій програмі, і продовжуйте вивчати нові функції та можливості, щоб стати ще більш досконалим у роботі з Excel.
У цьому блозі представлено функцію LuckyTemplates TOPN DAX, яка дає вам змогу отримувати унікальну статистику з ваших даних, допомагаючи вам приймати кращі маркетингові рішення.
Дізнайтеся про деякі фантастичні аналітичні прийоми, які ми можемо використовувати для моделювання даних у LuckyTemplates за допомогою таблиць підтримки DAX.
Тут ми зануримося в LuckyTemplates Advanced DAX і запровадимо логіку ранжирування, щоб отримати дуже унікальну інформацію. У цьому прикладі я також демонструю розгалуження міри.
У цьому блозі представлено нову функцію в LuckyTemplates — параметр аналізу «Що, якщо». Ви побачите, як це робить усе швидким і легким для аналізу сценарію.
Дізнайтеся, як визначити, чи добре зростає ваш дохід, перевіривши, чи зросла ваша маржа за допомогою розгалуження міри LuckyTemplates.
Дізнайтеся та зрозумійте, як можна створювати та використовувати параметри LuckyTemplates, які є потужною функцією редактора запитів.
У цьому підручнику буде обговорено створення круглої гістограми за допомогою Charticulator. Ви дізнаєтеся, як створити їх для звіту LuckyTemplates.
Дізнайтеся, як використовувати функції та формули PowerApps, щоб покращити функціональність і змінити поведінку в нашій програмі Canvas Power Apps.
У цьому підручнику ви дізнаєтесь, як об’єднати функції разом за допомогою оператора каналу dplyr мовою програмування R.
RANKX від LuckyTemplates дозволяє повернути рейтинг певного числа в кожному рядку таблиці, який є частиною списку чисел.
