Ми маємо дві таблиці замовлень, скопійованих в один робочий лист. Необхідно виконати порівняння даних двох таблиць в Excel і перевірити, які позиції є першою таблицею, але немає в другій. Немає сенсу вручну порівнювати значення кожного осередку.
Порівняння двох стовпців на збіги в Excel
Як зробити порівняння значень у Excel двох стовпців? Для вирішення цього завдання рекомендуємо використовувати умовне форматування, яке швидко виділити кольором позиції, що знаходяться лише в одному стовпчику. Робочий лист із таблицями:
Насамперед необхідно присвоїти імена обом таблицям. Завдяки цьому легше зрозуміти, які порівнюються діапазони осередків:
- Виберіть інструмент «ФОРМУЛИ»-«Визначені імена»-«Присвоїти ім'я».
- У вікні, що з'явилося в полі «Ім'я:» введіть значення – Таблиця_1.
- Лівою клавішею миші зробіть клацання по полю введення «Діапазон:» та виділіть діапазон: A2:A15. І натисніть OK.
Для другого списку виконайте ті ж дії тільки назву присвойте – Таблица_2. А діапазон вкажіть C2: C15 відповідно.
Корисна порада! Імена діапазонів можна надавати швидше за допомогою поля імен. Воно знаходиться ліворуч від рядка формул. Просто виділяйте діапазони осередків, а в полі імен введіть відповідне ім'я для діапазону та натисніть Enter.
Тепер скористаємося умовним форматуванням, щоб порівняти два списки в Excel. Нам потрібно отримати наступний результат:
Позиції, які є в Таблиці_1, але немає в Таблиці_2, будуть відображатися зеленим кольором. У той же час, позиції, що знаходяться в Таблиці_2, але відсутні в Таблиці_1, будуть підсвічені синім кольором.
Принцип порівняння даних двох стовпців в Excel
При визначенні умов для форматування осередків стовпців ми використовували функцію РАХУНКИ. У цьому прикладі ця функція перевіряє скільки разів зустрічається значення другого аргументу (наприклад, A2) у списку першого аргументу (наприклад, Таблица_2). Якщо кількість разів = 0 у такому разі формула повертає значення ІСТИНА. У такому випадку осередку присвоюється формат користувача, зазначений у параметрах умовного форматування.
Посилання у другому аргументі відносне, отже по черзі будуть перевірені всі осередки виділеного діапазону (наприклад, A2:A15). Наприклад, для порівняння двох прайсів в Excel навіть на різних аркушах. Друга формула діє аналогічно. Цей принцип можна застосовувати для різних подібних завдань.
Ви можете скористатися версіями workbook, аналізувати workbook для проблем або inconsistencies, або повідомлень між workbooks або worksheets. Якщо Microsoft Office 365 або Office Professional Plus 2013 є налаштованим на вашому комп'ютері, Spreadsheet Inquire add-in available in Excel.
Ви можете використовувати комбінації в потрібний tab для всіх цих дій, і більше. Потрібний tab на Excel Ribbon має кнопки для повідомлень, що описані нижче.
If you don"t see the Inquire tab in the Excel ribbon, more Turn on Spreadsheet Inquire add-in .
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, між 2 workbooks. Ви потребуєте дві workbooks Open в Excel для виконання цієї команди.
Результати є кольором кодованим з малюнком вмісту, такими як введені значення, формули, названі розміри, і форми. There's even a window that can show VBA code changes line by line.
The Compare Files Command uses Microsoft Spreadsheet Compare до compare the two files. У Windows 8, ви можете запустити Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. У Windows 7, натисніть на Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013.
Більше про Spreadsheet Compare and comparing files, read Compare two versions of a workbook .
Analyze a workbook
The Workbook AnalysisКоманда створить interactive report showing detailed information of the workbook and its structure, formulas, cells, ranges, and warnings. На цій сторінці показано дуже простий workbook, що містить дві формули і зв'язки з цією програмою в Access Database і текстовий файл.
Show workbook links
Workbooks з'єднані з іншими workbooks через стільникові посилання можуть отримати відхилення. Використовуйте для створення функцій, графічної мапи workbook dependencies, створених за допомогою з'єднань (links) між файлами. Типи повідомлень в діаграмі можуть включати інші workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. У відносинах diagram, ви можете виділити елементи і find more information o them, and drag connection lines to change the shape of the diagram.
Цей diagram показує поточний workbook on left and connections між it і іншими workbooks and data sources. Вона також показує додаткові рівні workbook connections, витрачаючи вас на зображення даних оригіналів для workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Використовуйте для створення функціональних, графічної мапи з'єднань (links) між роботоюзаписів у самій роботодавці і в інших роботу. Це вказує на те, що clearer зображення про те, як ваші дані might depend on cells in other places.
Цей diagram показує відносини між роботами в чотирьох різних workbooks, з залежністю між роботами в самих workbook як добре, як links між worksheets в різних workbooks. Коли ви робите свій плакат над node в діаграмі, так, як робота натиснута "Вест" в діаграмі, в balloon розміщення інформації про appears.
Show cell relationships
Для того, щоб отримати детальні, interactive diagram of all links from selected cell to cells in other worksheets or even other workbooks, use the Cell Relationshipінструмент. Ці відносини з іншими клітинами можуть існувати в формулах, або посилання на намічені межі. Diagram може cross worksheets and workbooks.
Цей діаграм показує два рівні стовпових відносин для селища A10 на Sheet5 в Book1.xlsx. Це програмне забезпечення dependiendo on cell C6 on Sheet 1 in another workbook, Book2.xlsx. Цей осередок є послідовним для декількох вузлів на інших роботахзаписів в одному файлі.
Для того, щоб дізнатися більше про viewing cell relationships, read See links between cells .
Clean excess cell formatting
Чи можна відкрити workbook і шукати його loads slowly, або has become huge? It might have formatting applied to rows or columns you aren"t aware of. Use the Clean Excess Cell Formatting Command to remove excess formatting and greatly reduce file size. Це означає, що ви збираєтеся "сприймати шлунок," який спрощує Excel"s speed.
Manage passwords
Якщо ви використовуєте потрібні ознаки для аналізу або compare workbooks, які є словом, які ви помітили, ви повинні використовувати wordbook password до вашого password list для того, щоб запитати, що може бути отримана копія вашого workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, які будуть вилучені на вашому комп'ютері. Ці passwords є encrypted і тільки accessible by you.
Стаття дає відповіді такі питання:
- Як порівняти дві таблиці в Excel?
- Як порівнювати складні таблиці в Excel?
- Як порівняти таблиці в Excel з використанням функції ВПР()?
- Як формувати унікальні ідентифікатори рядків, якщо їхня унікальність спочатку визначається набором значень у кількох стовпцях?
- Як фіксувати значення осередків у формулах при копіюванні формул?
При роботі з великими обсягами інформації користувач може мати справу з таким завданням, як порівняння двох табличних джерел даних. При зберіганні даних у єдиній системі обліку (наприклад, системи на базі 1С Підприємство, системи, що використовують SQL бази даних), для порівняння даних можуть використовуватися вбудовані в систему або СУБД можливості. Як правило, для цього достатньо залучити програміста, який напише запит до бази даних або програмний механізм звіту. З запитом може впоратися і досвідчений користувач, який володіє навичкою написання запитів 1C або SQL.
Проблеми починаються, коли потрібно виконати завдання порівняння даних терміново, а залучення програміста і написання ним запиту чи програмного звіту у часі може перевищити встановлені вирішення завдання терміни. Ще однією не менш поширеною проблемою є потреба порівняння інформації із різних джерел. У такому разі постановка задачі для програміста звучатиме як інтеграція двох систем. Вирішення такої задачі вимагатиме вищої кваліфікації програміста і займе більше часу, ніж розробка в єдиній системі.
Для вирішення зазначених проблем ідеальним прийомом є використання порівняння даних табличного редактора Microsoft Excel. Більшість поширених систем управлінського та регламентованого обліку підтримують вивантаження у формат Excel. Це завдання потребує лише певної кваліфікації користувача по роботі з цим офісним пакетом і не вимагатиме навичок програмування.
Розглянемо розв'язання задачі порівняння таблиць Excel на прикладі. Ми маємо дві таблиці, що містять переліки квартир. Джерела вивантаження - 1С Підприємство (облік будівництва) та таблиця в Excel (облік продажів). Таблиці розміщені в робочій книзі Excel на першому та другому аркушах відповідно.
Перед нами постає завдання порівняти ці списки за адресою. У першій таблиці – усі квартири будинку. У другій таблиці - лише продані квартири та ім'я покупця. Кінцева мета - відобразити в першій таблиці по кожній квартирі ім'я покупця (для квартир, які були продані). Завдання ускладнюється тим, що адреса квартири в кожній таблиці є будівельною і складається з кількох полів: 1) адреса корпусу (будинку), 2) секція (під'їзд), 3) поверх, 4) номер на поверсі (наприклад, від 1 до 4) .
Для порівняння двох таблиць Excel нам потрібно добитися того, щоб у обох таблицях кожен рядок ідентифікувався одним полем, а не чотирма. Отримати таке поле можна, об'єднавши значення чотирьох полів адреси функцією Зчепити(). Призначення функції Зчепити() - об'єднання кількох текстових значень в один рядок. Значення функції перераховуються через символ ";". Як значення можуть виступати як адреси осередків, і довільний текст, заданий у лапках.
Крок 1.Вставимо на початку першої таблиці порожню колонку "A" і пропишемо в комірці цієї колонки навпроти першого рядка з даними формулу:
=ЗЧЕПИТИ(B3;"-";C3;"-";D3;"-";E3)
Для зручності візуального сприйняття між значеннями комірок, що об'єднуються, ми встановили символи "-".
Крок 2Скопіюємо формулу в нижченаведені комірки колонки А.
Крок 4.Для порівняння таблиць Excel за значеннями слід користуватися функцією ВПР(). Призначення функції ВПР() - пошук значення в крайньому лівому стовпці таблиці та повернення значення комірки, що знаходиться у вказаному стовпці того ж рядка. Перший параметр - потрібне значення. Другий параметр - таблиця, у якій здійснюватиметься пошук значення. Третій параметр - номер стовпця, з комірки якого у знайденому рядку буде повернуто значення. Четвертий параметр – тип пошуку: брехня – точний збіг, істина – наближений збіг.
Оскільки вихідна інформація має бути розміщена в першій таблиці (саме до неї потрібно додати імена покупців), то формулу будемо прописувати в ній. Сформуємо у вільній колонці праворуч від таблиці навпроти першого рядка даних формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;БРЕХНЯ)
При копіюванні формул "розумний" Excel автоматично змінює адресацію осередків. У нашому випадку потрібне значення для кожного рядка буде змінюватися: A3, A4 і т.д., а адреса таблиці, в якій ведеться пошук, має залишатися незмінною. Для цього зафіксуємо комірки у параметрі адреси таблиці символами "$". Замість "Лист2!A3:F10" робимо "Лист2!$A$3:$F$10".
Щомісяця працівник відділу кадрів отримує список співробітників разом із їхніми окладами. Він копіює список на новий аркуш робочої книги Excel. Завдання наступне: порівняти зарплату співробітників, яка змінилася по відношенню до попереднього місяця. Для цього необхідно виконати порівняння даних у Excel на різних аркушах. Скористаємося умовним форматуванням. Таким чином, ми не тільки автоматично знайдемо всі відмінності у значеннях осередків, але й виділимо їх кольором.
Порівняння двох листів у Excel
У фірмі може бути понад сто співробітників, серед яких одні звільняються, інші працевлаштовуються, треті йдуть у відпустку або на лікарняну тощо. Внаслідок чого можуть виникнути труднощі з порівнянням даних із зарплати. Наприклад, прізвища співробітників будуть постійно різною послідовністю. Як порівняти дві таблиці Excel на різних аркушах?
Вирішити це непросте завдання нам допоможе умовне форматування. Наприклад, візьмемо дані за лютий і березень, як показано на малюнку:
Щоб знайти зміни на зарплатних листах:
Після введення всіх умов форматування Excel автоматично виділив кольором тих співробітників зарплати яких змінилися порівняно з попереднім місяцем.
Принцип порівняння двох діапазонів даних в Excel на різних аркушах:
У певній умові важливе значення має функція ПОШУКПОЗ. У її першому аргументі міститься пара значень, яка має бути знайдена на вихідному аркуші наступного місяця, тобто «Березень». Перегляд діапазон визначається як з'єднання значень діапазонів, визначених іменами, в пари. Таким чином виконується порівняння рядків за двома ознаками – прізвище та зарплата. Для знайдених збігів повертається число, що насправді для Excel є істиною. Тому слід використовувати функцію =НЕ(), яка дозволяє замінити значення ІСТИНА на брехню. Інакше буде застосовано форматування для осередків, значення яких збіглися. Для кожної не знайденої пари значень (тобто – невідповідність) &B2&$C2 у діапазоні Прізвище&Зарплата, функція ПОШУКПОЗ повертає помилку. Помилкове значення не є логічним значенням. Тому виповзаємо функцію ЕСЛИПОМИЛКА, яка надасть логічне значення для кожної помилки - ІСТИНА. Це сприяє присвоєнню нового формату тільки для осередків без збігів значень із зарплати щодо наступного місяця – березня.
Потрібно порівняти два файли Microsoft Excel? Ось два простих способи зробити це.
Існує безліч причин, через які вам може знадобитися взяти один документ Excel і порівняти його з іншим. Це може бути трудомістким завданням
це вимагає великої концентрації, але є способи полегшити життя.
Чи потрібно вам уважно подивитися вручну, чи ви хочете, щоб Excel виконував деякі важкі роботи
від вашого імені, ось два простих способи порівняти кілька аркушів.
Як порівняти файли Excel
Excel дозволяє користувачам відразу виводити на екран дві версії документа, щоб швидко встановити різницю між ними:
- Спершу відкрийте робочі книги, які потрібно порівняти.
- Перейдіть до Вид> Вікно> Вид поруч.
Порівняння файлів Excel на око
Для початку відкрийте Excel та всі робочі книги, які ви хочете порівняти. Ми можемо використовувати ту ж техніку для порівняння аркушів в одному документі
або різні файли.
Якщо з однієї й тієї книги отримано більше одного аркуша, її необхідно відокремити заздалегідь. Для цього перейдіть до Вид> Вікно> Нове вікно.
Це не розділить окремі аркуші назавжди, просто відкриє новий екземпляр документа.
У цьому меню будуть перелічені всі таблиці, які наразі відкриті. Якщо у вас є лише два відкриті, вони будуть вибрані автоматично.
Зробіть свій вибір та натисніть добре. Ви побачите, що обидві таблиці з'являться на екрані.
Якщо це зручніше, ви можете використати Розставити всекнопка для перемикання між вертикальною та горизонтальною конфігурацією.
Один важливий варіант, про який слід знати, це Синхронне прокручуванняперемикання.
Увімкнення цього параметра гарантує, що при прокручуванні одного вікна інше рухатиметься синхронно. Це важливо, якщо ви працюєте з великою таблицею
і ви хочете продовжувати перевіряти одне проти одного. Якщо з якоїсь причини два листи не вирівняні, просто натисніть Скинути положення вікна.
Порівняння файлів Excel з використанням умовного форматування
У багатьох випадках найкращий спосіб порівняння двох електронних таблиць може полягати в тому, щоб вивести їх на екран одночасно. Однак можна дещо автоматизувати процес.
Використання умовного форматування
Ми можемо перевірити Excel на наявність розбіжностей між двома аркушами. Це може заощадити багато часу, якщо все, що вам потрібно знайти, це різниця між однією версією та іншою.
Для цього методу потрібно переконатися, що два аркуші, з якими ми працюємо, є частиною однієї робочої книги. Для цього клацніть правою кнопкою миші ім'я аркуша, який ви хочете перенести, та виберіть Перемістити або скопіювати.
Тут ви можете використовувати меню, що випадає, щоб вирішити, в який документ він буде вставлений.
Виберіть усі осередки, які заповнені на аркуші, де ви хочете, щоб будь-які відмінності були виділені. Швидкий спосіб зробити це - клацнути комірку у верхньому лівому кутку, а потім використовувати ярлик
Ctrl+Shift+End.
Перейдіть до Головна> Стилі> Умовне форматування> Нове правило.
Вибрати Використовуйте формулу, щоб визначити, які осередки форматуватиі введіть таке:
A1sheet_name!A1
Просто не забудьте викласти "sheet_name" для будь-якого імені іншого листа. Ця формула виконує тільки перевірку, коли осередок на одному аркуші не точно відповідає відповідному осередку на іншому аркуші, і позначає кожен екземпляр.
Вище ви можете побачити результати. Всі комірки, що містять зміни, були виділені червоним, що дозволяє швидко та легко порівняти два аркуші.
Нехай Excel зробить важку роботу
Наведена вище методика демонструє один спосіб, яким можна дозволити Excel впоратися з деякими важкими завданнями. Навіть якщо ви приділяєте пильну увагу, є ймовірність, що ви можете пропустити зміну, якщо ви виконуватимете це завдання вручну. Завдяки умовному форматуванню ви можете бути впевнені, що нічого не прослизає в мережу
Excel гарний у монотонних та детально орієнтованих роботах. Як тільки ви оволодієте його можливостями, ви зможете заощадити трохи часу та зусиль, використовуючи таку техніку, як умовне форматування та трохи винахідливості.
У вас є порада порівняння документів в Excel? Чи потрібна вам допомога з процесами, описаними в цьому посібнику? У будь-якому випадку, чому б не приєднатися до розмови у розділі коментарів нижче?