Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.
You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.
If you don "t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in.
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.
Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There "s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:
The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then\u003e All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013.
To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook.
Analyze a workbook
The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.
Show workbook links
Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.
This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.
This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.
Show cell relationships
To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell Relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.
This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.
To learn more about viewing cell relationships, read See links between cells.
Clean excess cell formatting
Ever open a workbook and find it loads slowly, or 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. This helps you avoid "spreadsheet bloat," which improves Excel "s speed.
Manage passwords
If you "re using the Inquire features to analyze or compare workbooks that are password protected, you" ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.
Читання цієї статті займе у Вас близько 10 хвилин. У наступні 5 хвилин Ви зможете легко порівняти два стовпці в Excel і дізнатися про наявність в них дублікатів, видалити їх або виділити кольором. Отже, час пішов!
Excel - це дуже потужний і дійсно круте додаток для створення і обробки великих масивів даних. Якщо у Вас є кілька робочих книг з даними (або тільки одна величезна таблиця), то, ймовірно, Ви захочете порівняти 2 стовпці, знайти повторювані значення, а потім зробити з ними будь-які дії, наприклад, видалити, виділити кольором або очистити вміст . Стовпці можуть перебувати в одній таблиці, бути суміжними або не суміжні, можуть бути розташовані на 2-х різних аркушах або навіть в різних книгах.
Уявіть, що у нас є 2 стовпці з іменами людей - 5 імен в стовпці A і 3 імені в стовпці B. Необхідно порівняти імена в цих двох стовпчиках і знайти повторювані. Як Ви розумієте, це вигадані дані, взяті виключно для прикладу. У реальних таблицях ми маємо справу з тисячами, а то і з десятками тисяч записів.
Варіант А: обидва стовпці знаходяться на одному аркуші. Наприклад, стовпець A і стовпець B.
Варіант В: Стовпці розташовані на різних аркушах. Наприклад, стовпець A на аркуші Sheet2 і стовпець A на аркуші Sheet3.
В Excel 2013, 2010 і 2007 є вбудований інструмент Remove Duplicate (Видалити дублікати), але він безсилий в такій ситуації, оскільки не може порівнювати дані в 2 шпальтах. Більш того, він може тільки видалити дублікати. Інших варіантів, таких як виділення або зміна кольору, не передбачено. І крапка!
Порівнюємо 2 стовпці в Excel і знаходимо повторювані записи за допомогою формул
Варіант А: обидва стовпці знаходяться на одному аркуші
Підказка: У великих таблицях скопіювати формулу вийде швидше, якщо використовувати комбінації клавіш. виділіть клітинку C1 і натисніть Ctrl + C (Щоб скопіювати формулу в буфер обміну), потім натисніть Ctrl + Shift + End (Щоб виділити все не порожні клітинки в стовпі С) і, нарешті, натисніть Ctrl + V (Щоб вставити формулу в усі виділені осередки).
Варіант В: два стовпці знаходяться на різних аркушах (в різних книгах)
Обробка знайдених дублікатів
Відмінно, ми знайшли записи в першому стовпці, які також присутні в другому стовпці. Тепер нам потрібно щось з ними робити. Переглядати всі повторювані записи в таблиці вручну досить неефективно і займає надто багато часу. Існують шляхи получше.
Показати тільки повторювані рядки в стовпці А
Якщо Ваші стовпці не мають заголовків, то їх необхідно додати. Для цього помістіть курсор на число, що позначає перший рядок, при цьому він перетвориться в чорну стрілку, як показано на малюнку нижче:
Клацніть правою кнопкою миші і в контекстному меню виберіть Insert (Вставити):
Дайте назви стовпцями, наприклад, " Name"І" Duplicate?"Потім відкрийте вкладку Data (Дані) і натисніть Filter (Фільтр):
Після цього натисніть меленькую сіру стрілку поруч з " Duplicate?", Щоб розкрити меню фільтра; зніміть галочки з усіх елементів цього списку, крім Duplicate, і натисніть ОК.
Ось і все, тепер Ви бачите тільки ті елементи стовпця А, Які дублюються в стовпці В. В нашій навчальної таблиці таких осередків всього дві, але, як Ви розумієте, на практиці їх зустрінеться набагато більше.
Щоб знову відобразити всі рядки стовпця А, Клікніть символ фільтра в стовпці В, Який тепер виглядає як воронка з маленької стрілочкою і виберіть Select all (Виділити все). Або Ви можете зробити те ж саме через Стрічку, натиснувши Data (Дані)\u003e Select & Filter (Сортування і фільтр)\u003e Clear (Очистити), як показано на знімку екрана нижче:
Зміна кольору або виділення знайдених дублікатів
Якщо позначки " Duplicate"Мало для Ваших цілей, і Ви хочете відзначити повторювані осередку іншим кольором шрифту, заливки або будь-яким іншим способом ...
В цьому випадку отфильтруйте дублікати, як показано вище, виділіть всі відфільтровані осередки і натисніть Ctrl + 1, Щоб відкрити діалогове вікно Format Cells (Формат ячеек). Як приклад, давайте змінимо колір заливки осередків в рядках з дублікатами на яскраво-жовтий. Звичайно, Ви можете змінити колір заливки за допомогою інструменту Fill (Колір заливки) на вкладці Home (Головна), але перевага діалогового вікна Format Cells (Формат ячеек) в тому, що можна налаштувати одночасно всі параметри форматування.
Тепер Ви точно не пропустите жодної клітинки з дублікатами:
Видалення повторюваних значень з першого стовпчика
Фільтрувати таблицю так, щоб показані були тільки осередки з повторюваними значеннями, і виділіть ці осередки.
Якщо 2 стовпці, які Ви порівнюєте, знаходяться на різних аркушах, Тобто в різних таблицях, клікніть правою кнопкою миші виділений діапазон і в контекстному меню виберіть Delete Row (Видалити рядок):
натисніть ОК, Коли Excel попросить Вас підтвердити, що Ви дійсно хочете видалити весь рядок листа і після цього очистіть фільтр. Як бачите, залишилися тільки рядки з унікальними значеннями:
Якщо 2 стовпці розташовані на одному аркуші, Впритул один одному (суміжні) або НЕ впритул один до одного (не суміжні), то процес видалення дублікатів буде трохи складніше. Ми не можемо видалити весь рядок з повторюваними значеннями, оскільки так ми видалимо осередки і з другого шпальти теж. Отже, щоб залишити тільки унікальні записи в стовпці А, Зробіть наступне:
Як бачите, видалити дублікати з двох стовпців в Excel за допомогою формул - це не так вже й складно.
Питання від користувача
Добридень!
У мене є одне завдання, і вже третій день ламаю голову - не знаю, як її виконати. Є 2 таблиці (приблизно 500-600 рядків в кожній), потрібно взяти стовпець з назвою товару з однієї таблиці і порівняти його з назвою товару з іншої, і, якщо товари співпадуть - скопіювати і підставити значення з таблиці 2 в таблицю 1. Заплутано пояснив , але думаю, по фотке завдання зрозумієте ( прим. : Фотка вирізана цензурою, все-таки особиста інформація).
Наперед дякую. Андрій, Москва.
Доброго дня всім!
Те, що ви описали - відноситься до досить популярним завданням, які відносно легко і швидко вирішувати за допомогою Excel. Досить загнати в програму дві ваші таблиці, і скористатися функцією ВВР. Про її роботі нижче ...
Приклад роботи з функцією ВВР
Як приклад я взяв дві невеликих таблички, представлені вони на скріншоті нижче. У першій таблиці (стовпчики A, B - товар і ціна) немає даних по стовпцю B; в другій - заповнені обидва стовпці (товар і ціна). Тепер потрібно перевірити перші стовпчики в обох таблицях і автоматично, при знайденому збігу, скопіювати ціну в першу табличку. Начебто, завдання проста ...
Як це зробити...
Ставимо курсор мишки в клітинку B2 - чи то пак в першу комірки стовпчика, де у нас немає значення і пишемо формулу:
\u003d ВПР (A2; $ E $ 1: $ F $ 7; 2; ЛОЖЬ)
A2 - значення з першого стовпця першої таблиці (то, що ми будемо шукати в першому стовпці другої таблиці);
$ E $ 1: $ F $ 7 - повністю виділена друга таблиця (в якій хочемо щось знайти і скопіювати). Зверніть увагу на значок "$" - він необхідний, щоб при копіюванні формули не змінювалися осередки виділеної другий таблиці;
2 - номер стовпця, з якого буем копіювати значення (зверніть увагу, що у нас виділена друга таблиця має всього 2 стовпчика. Якби у неї було 3 стовпці - то значення можна було б копіювати з 2-го або 3-го стовпця);
БРЕХНЯ - шукаємо точний збіг (інакше буде підставлено першу схоже, що явно нам не підходить).
Власне, можете готову формулу підігнати під свої потреби, злегка змінивши її. Результат роботи формули представлений на зображенні нижче: ціна була знайдена в другій таблиці і підставлена \u200b\u200bв авто-режимі. Все працює!
Щоб ціна була проставлена \u200b\u200bі для інших найменувань товару - просто розтягніть (скопіюйте) формулу на інші осередки. Приклад нижче.
Після чого, як бачите, перші стовпчики у таблиць будуть зрівняні: з рядків, де значення осередків збіглися - будуть скопійовані і підставлені потрібні дані. В общем-то, зрозуміло, що таблиці можуть бути набагато більше!
Примітка : Повинен сказати, що функція ВПР досить вимоглива до ресурсів комп'ютера. У деяких випадках, при надмірно великому документі, щоб порівняти таблиці може знадобитися досить тривалий час. У цих випадках, варто розглянути або інші формули, або зовсім інші рішення (кожен випадок індивідуальний).
На цьому все, удачі!
Щомісяця працівник відділу кадрів отримує список співробітників разом з їх окладами. Він копіює список на новий лист робочої книги Excel. Завдання наступна: порівняти зарплату співробітників, яка змінилася по відношенню до попереднього місяця. Для цього необхідно виконати порівняння даних в Excel на різних аркушах. Скористаємося умовним форматуванням. Таким чином ми не тільки автоматично знайдемо всі відмінності в значеннях осередків, а й виділимо їх кольором.
Порівняння двох аркушів в Excel
У фірмі може бути понад сто співробітників, серед яких одні звільняються інші працевлаштовуються, треті йдуть у відпустку або на лікарняний і т.п. У слідстві чого можуть виникнути складності з порівнянням даних по зарплаті. Наприклад, прізвища співробітників будуть постійно в різній послідовності. Як зробити порівняння двох таблиць Excel на різних аркушах?
Вирішити цю непросту задачу нам допоможе умовне форматування. Для прикладу, візьмемо дані за лютий і березень, як показано на малюнку:
Щоб знайти зміни на зарплатних аркушах:
Після введення всіх умов для форматування Excel автоматично виділив кольором тих співробітників зарплати яких змінилися в порівнянні з попереднім місяцем.
Принцип порівняння двох діапазонів даних в Excel на різних аркушах:
У певному умови істотне значення має функція ПОИСКПОЗ. В її першому аргументі знаходиться пара значень, яка повинна бути знайдена на вихідному аркуші наступного місяця, тобто «Март». Проглядається діапазон визначається як з'єднання значень діапазонів, визначених іменами, в пари. Таким чином виконується порівняння рядків за двома ознаками - прізвище та зарплата. Для запропонованих варіантів повертається число, що по суті для Excel є істиною. Тому слід використовувати функцію \u003d НЕ (), яка дозволяє замінити значення ІСТИНА на БРЕХНЯ. Інакше буде застосовано форматування для осередків значення яких збіглися. Для кожної невіднайденій пари значень (тобто - невідповідність) & B2 & $ C2 в діапазоні Прізвище & Зарплата, функція ПОИСКПОЗ повертає помилку. Помилкове значення не є логічним значенням. Тому ісползаем функцію ЕСЛІОШІБКА, яка присвоїть логічне значення для кожної помилки - ІСТИНА. Це сприяє присвоєнню нового формату тільки для осередків без збігів значень із зарплати в відношенні до наступного місяця - березня.
Мабуть, кожен, хто працює з даними в Excel стикається з питанням як порівняти два стовпці в Excel на збіги і відмінності. Існує кілька способів як це зробити. Давайте розглянемо детальніше кожен з них.
Як порівняти два стовпці в Excel по рядках
Порівнюючи два стовпці з даними часто необхідно порівнювати дані в кожному окремому рядку на збіги або відмінності. Зробити такий аналіз ми можемо за допомогою функції. Розглянемо як це працює на прикладах нижче.
Приклад 1. Як порівняти два стовпці на збіги і відмінності в одному рядку
Для того, щоб порівняти дані в кожному рядку двох стовпців в Excel напишемо просту формулу. Вставляти формулу слід в кожен рядок в сусідньому стовпці, поруч з таблицею, в якій розміщені основні дані. Створивши формулу для першого рядка таблиці, ми зможемо її протягнути / скопіювати на інші рядки.
Для того щоб перевірити, чи містять два стовпці одного рядка однакові дані нам буде потрібно формула:
\u003d ЕСЛИ (A2 \u003d B2; "Чи збігаються"; "")
Формула, що визначає відмінності між даними двох стовпців в одному рядку буде виглядати так:
\u003d ЕСЛИ (A2<>B2; "Не збігаються"; "")
Ми можемо вмістити перевірку на збіги і відмінності між двома стовпцями в одному рядку в одній формулі:
\u003d ЕСЛИ (A2 \u003d B2; "Чи збігаються"; "Чи не збігаються")
\u003d ЕСЛИ (A2<>B2; "Не збігаються"; "Чи збігаються")
Приклад результату обчислень може виглядати так:
Для того щоб порівняти дані в двох стовпчиках одного рядка з урахуванням регістру слід використовувати формулу:
\u003d ЕСЛИ (збігаючись (A2, B2); "Чи співпадає"; "Унікальне")
Як порівняти декілька стовпців на збіги в одному рядку Excel
В Excel є можливість порівняти дані в кілька стовпчиків одного рядка за наступними критеріями:
- Знайти рядки з однаковими значеннями у всіх шпальтах таблиці;
- Знайти рядки з однаковими значеннями в будь-яких двох стовпчиках таблиці;
Приклад 1. Як знайти збіги в одному рядку через кілька стовпчиків таблиці
Уявімо, що наша таблиця складається з декількох стовпців з даними. Наше завдання знайти рядки в яких значення збігаються у всіх шпальтах. У цьому нам допоможуть функції Excel і. Формула для визначення збігів буде наступною:
\u003d ЕСЛИ (И (A2 \u003d B2; A2 \u003d C2); "Чи збігаються"; "")
Якщо в нашій таблиці дуже багато стовпців, то більш просто буде використовувати функцію в поєднанні з:
\u003d ЕСЛИ (СЧЁТЕСЛІ ($ A2: $ C2; $ A2) \u003d 3; "Чи збігаються"; "")
У формулі як "5" зазначено число стовпців таблиці, для якої ми створили формулу. Якщо у вашій таблиці стовпців більше або менше, то це значення має дорівнювати кількості стовпців.
Приклад 2. Як знайти збіги в одному рядку в будь-яких двох стовпчиках таблиці
Уявімо, що наша задача виявити з таблиці з даними в декілька стовпців ті рядки, в яких дані збігаються або повторюються як мінімум в двох стовпчиках. У цьому нам допоможуть функції і. Напишемо формулу для таблиці, що складається з трьох стовпців з даними:
\u003d ЕСЛИ (ИЛИ (A2 \u003d B2; B2 \u003d C2; A2 \u003d C2); "Чи збігаються"; "")
У тих випадках, коли в нашій таблиці занадто багато стовпців - наша формула з функцією буде дуже великий, так як в її параметрах нам потрібно вказати критерії збігу між кожним стовпцем таблиці. Ще простіше, в цьому випадку, використовувати функцію.
\u003d ЕСЛИ (СЧЁТЕСЛІ (B2: D2; A2) + СЧЁТЕСЛІ (C2: D2; B2) + (C2 \u003d D2) \u003d 0; "Унікальна рядок"; "Чи не унікальна рядок")
\u003d ЕСЛИ (СЧЁТЕСЛІ ($ B: $ B; $ A5) \u003d 0; "Немає збігів в стовпці B"; "Є збіги в стовпці В")
Ця формула перевіряє значення в стовпці B на збіг з даними комірок в стовпці А.
Якщо ваша таблиця складається з фіксованого числа рядків, ви можете вказати у формулі чіткий діапазон (наприклад, $ B2: $ B10 ). Це дозволить прискорити роботу формули.
Як порівняти два стовпці в Excel на збіги і виділити кольором
Коли ми шукаємо збіги між двома стовпцями в Excel, нам може знадобитися візуалізувати знайдені збіги або відмінності в даних, наприклад, за допомогою виділення кольором. Найпростіший спосіб для виділення кольором збігів і відмінностей - використовувати "Умовне форматування" в Excel. Розглянемо як це зробити на прикладах нижче.
Пошук і виділення збігів кольором через кілька стовпчиків в Ексель
У тих випадках, коли нам потрібно знайти збіги через кілька стовпчиків, то для цього нам потрібно:
- Виділити стовпці з даними, в яких потрібно обчислити збіги;
- На вкладці "Головна" на Панелі інструментів натискаємо на пункт меню "Умовне форматування" -\u003e "Правила виділення осередків" -\u003e "Повторювані значення";
- У спливаючому діалоговому вікні виберіть у лівому випадаючому списку пункт "повторюються", в правому випадаючому списку виберіть яким кольором будуть виділені повторювані значення. Натисніть кнопку "ОК":
- Після цього в виділеної колонці будуть підсвічені кольором збіги:
Пошук і виділення кольором збігаються рядків в Excel
Пошук співпадаючих осередків з даними в двох, через кілька стовпчиків і пошук збігів цілих рядків з даними це різні поняття. Зверніть увагу на дві таблиці нижче:
У таблицях вище розміщені однакові дані. Їх відмінність в тому, що на прикладі зліва ми шукали збігаються осередки, а праворуч ми знайшли цілі повторюють рядки з даними.
Розглянемо як знайти збігаються рядки в таблиці:
- Праворуч від таблиці з даними створимо допоміжний стовпець, в якому навпроти кожного рядка з даними проставимо формулу, що об'єднує всі значення рядка таблиці в одну клітинку:
\u003d A2 & B2 & C2 & D2
У допоміжній колонці ви побачите об'єднані дані таблиці:
Тепер, для визначення збігаються рядків в таблиці зробіть наступні кроки:
- Виділіть область з даними в допоміжній колонці (в нашому прикладі це діапазон комірок E2: E15 );
- На вкладці "Головна" на Панелі інструментів натискаємо на пункт меню "Умовне форматування" -\u003e "Правила виділення осередків" -\u003e "Повторювані значення";
- У спливаючому діалоговому вікні виберіть у лівому випадаючому списку "повторюються", в правому випадаючому списку виберіть яким кольором будуть виділені повторювані значення. Натисніть кнопку "ОК":
- Після цього в виділеної колонці будуть підсвічені дублюються рядки:
