Щомісяця працівник відділу кадрів отримує список співробітників разом з їх окладами. Він копіює список на новий лист робочої книги Excel. Завдання наступна: порівняти зарплату співробітників, яка змінилася по відношенню до попереднього місяця. Для цього необхідно виконати порівняння даних в Excel на різних аркушах. Скористаємося умовним форматуванням. Таким чином ми не тільки автоматично знайдемо всі відмінності в значеннях осередків, а й виділимо їх кольором.
Порівняння двох аркушів в Excel
У фірмі може бути понад сто співробітників, серед яких одні звільняються інші працевлаштовуються, треті йдуть у відпустку або на лікарняний і т.п. У слідстві чого можуть виникнути складності з порівнянням даних по зарплаті. Наприклад, прізвища співробітників будуть постійно в різній послідовності. Як зробити порівняння двох таблиць Excel на різних аркушах?
Вирішити цю непросту задачу нам допоможе умовне форматування. Для прикладу, візьмемо дані за лютий і березень, як показано на малюнку:
Щоб знайти зміни на зарплатних аркушах:
Після введення всіх умов для форматування Excel автоматично виділив кольором тих співробітників зарплати яких змінилися в порівнянні з попереднім місяцем.
Принцип порівняння двох діапазонів даних в Excel на різних аркушах:
У певному умови істотне значення має функція ПОИСКПОЗ. В її першому аргументі знаходиться пара значень, яка повинна бути знайдена на вихідному аркуші наступного місяця, тобто «Март». Проглядається діапазон визначається як з'єднання значень діапазонів, визначених іменами, в пари. Таким чином виконується порівняння рядків за двома ознаками - прізвище та зарплата. Для запропонованих варіантів повертається число, що по суті для Excel є істиною. Тому слід використовувати функцію \u003d НЕ (), яка дозволяє замінити значення ІСТИНА на БРЕХНЯ. Інакше буде застосовано форматування для осередків значення яких збіглися. Для кожної невіднайденій пари значень (тобто - невідповідність) & B2 & $ C2 в діапазоні Прізвище & Зарплата, функція ПОИСКПОЗ повертає помилку. Помилкове значення не є логічним значенням. Тому ісползаем функцію ЕСЛІОШІБКА, яка присвоїть логічне значення для кожної помилки - ІСТИНА. Це сприяє присвоєнню нового формату тільки для осередків без збігів значень із зарплати в відношенні до наступного місяця - березня.
Досить часто перед користувачами Excel стоїть завдання порівняння двох таблиць або списків для виявлення в них відмінностей або відсутніх елементів. Кожен користувач справляється з цим завданням за своїм, але частіше за все на вирішення зазначеного питання витрачається досить багато часу, так як далеко не всі підходи до даної проблеми є раціональними. У той же час, існує кілька перевірених алгоритмів дій, які дозволять порівняти списки або табличні масиви в досить стислі терміни з мінімальною затратою зусиль. Давайте детально розглянемо ці види.
Існує досить багато способів порівняння табличних областей в Excel, але всі їх можна розділити на три великі групи:
Саме виходячи з цієї класифікації, перш за все, підбираються методи порівняння, а також визначаються конкретні дії і алгоритми для виконання завдання. Наприклад, при проведенні порівняння в різних книгах потрібно одночасно відкрити два файли Excel.
Крім того, слід сказати, що порівнювати табличні області має сенс тільки тоді, коли вони мають схожу структуру.
Спосіб 1: проста формула
Найпростіший спосіб порівняння даних у двох таблицях - це використання простої формули рівності. Якщо дані збігаються, то вона видає показник ІСТИНА, а якщо немає, то - БРЕХНЯ. Порівнювати можна, як числові дані, так і текстові. Недолік даного методу полягає в тому, що ним можна користуватися тільки в тому випадку, якщо дані в таблиці впорядковані або відсортовані однаково, синхронізовані і мають рівну кількість рядків. Давайте подивимося, як використовувати даний спосіб на практиці на прикладі двох таблиць, розміщених на одному аркуші.
Отже, маємо дві прості таблиці зі списками працівників підприємства і їх окладами. Потрібно порівняти списки співробітників і виявити невідповідності між стовпцями, в яких розміщені прізвища.
- Для цього нам знадобиться додатковий стовпець на аркуші. Вписуємо туди знак «=»
. Потім натискаємо на першу найменуванню, яке потрібно порівняти в першому списку. Знову ставимо символ «=»
з клавіатури. Далі натискаємо по першій клітинці колонки, яку ми порівнюємо, у другій таблиці. Вийшло вираз наступного типу:
Хоча, звичайно, в кожному конкретному випадку координати будуть відрізнятися, але суть залишиться однаковою.
- Клацаємо по клавіші Enter, Щоб отримати результати порівняння. Як бачимо, при порівнянні перших осередків обох списків програма вказала показник «ІСТИНА», Що означає збіг даних.
- Тепер нам потрібно провести аналогічну операцію і з іншими осередками обох таблиць в тих колонках, які ми порівнюємо. Але можна просто провести копіювання формули, що дозволить істотно заощадити час. Особливо даний фактор важливий при порівнянні списків з великою кількістю рядків.
Процедуру копіювання найлегше виконати за допомогою маркера заповнення. Наводимо курсор на правий нижній кут комірки, де ми отримали показник «ІСТИНА». При цьому він повинен перетворитися в чорний хрестик. Це і є маркер заповнення. Тиснемо ліву кнопку миші і тягнемо курсор вниз на кількість рядків у порівнюваних табличних масивах.
- Як бачимо, тепер в додатковому стовпці відобразилися всі результати порівняння даних у двох колонках табличних масивів. У нашому випадку не співпали дані тільки в одному рядку. При їх порівнянні формула видала результат «БРЕХНЯ». За всіма іншими рядками, як бачимо, формула порівняння видала показник «ІСТИНА».
- Крім того, існує можливість за допомогою спеціальної формули підрахувати кількість розбіжностей. Для цього виділяємо той елемент листа, куди воно буде виводитися. Потім клацаємо по значку «Вставити функцію».
- У вікні майстри функцій в групі операторів «Математичні» виділяємо найменування СУММПРОИЗВ. Клацаємо по кнопці «OK».
- Активується вікно аргументів функції СУММПРОИЗВ, Головним завданням якої є обчислення суми творів виділеного діапазону. Але цю функцію можна використовувати і для наших цілей. Синтаксис у неї досить простий:
СУММПРОИЗВ (массив1; массів2; ...)
Всього в якості аргументів можна використовувати адреси до 255 масивів. Але в нашому випадку ми будемо використовувати всього два масиви, до того ж, як один аргумент.
Ставимо курсор в поле «Массів1» і виділяємо на аркуші порівнюваний діапазон даних в першій області. Після цього в поле ставимо знак "не дорівнює" (<> ) І виділяємо порівнюваний діапазон другий області. Далі обворачіваем отриманий вираз дужками, перед якими ставимо два знака «-» . У нашому випадку вийшло такий вислів:
- (A2: A7<>D2: D7)
Клацаємо по кнопці «OK».
- Оператор проводить розрахунок і виводить результат. Як бачимо, в нашому випадку результат дорівнює числу «1», Тобто, це означає, що в порівнюваних списках було знайдено одне розбіжність. Якби списки були повністю ідентичними, то результат був би дорівнює числу «0» .
Таким же чином можна проводити порівняння даних в таблицях, які розташовані на різних аркушах. Але в цьому випадку бажано, щоб рядки в них були пронумеровані. В іншому процедура порівняння практично точно така, як була описана вище, крім того факту, що при внесенні формули доведеться перемикатися між листами. У нашому випадку вираз матиме такий вигляд:
B2 \u003d Аркуш2! B2
Тобто, як бачимо, перед координатами даних, які розташовані на інших аркушах, відмінних від того, де виводиться результат порівняння, вказується номер листа і знак оклику.
Спосіб 2: виділення груп осередків
Порівняння можна зробити за допомогою інструменту виділення груп осередків. З його допомогою також можна порівнювати тільки синхронізовані і впорядковані списки. Крім того, в цьому випадку списки повинні розташовуватися поруч один з одним на одному аркуші.
Спосіб 3: умовне форматування
Провести порівняння можна, застосувавши метод умовного форматування. Як і в попередньому способі, порівнювані області повинні знаходитися на одному робочому аркуші Excel і бути синхронізованими між собою.
Існує ще один спосіб застосування умовного форматування для виконання поставленого завдання. Як і попередні варіанти, він вимагає розташування обох порівнюваних областей на одному аркуші, але на відміну від раніше описаних способів, умова синхронізації або сортування даних не буде обов'язковим, що вигідно відрізняє даний варіант від раніше описаних.
При бажанні можна, навпаки, пофарбувати неспівпадаючі елементи, а ті показники, які збігаються, залишити з заливкою колишнім кольором. При цьому алгоритм дій практично той же, але в вікні налаштування виділення повторюваних значень в першому полі замість параметра «Що повторюються» слід вибрати параметр «Унікальні». Після цього натиснути на кнопку «OK».
Таким чином, будуть виділені саме ті показники, які не збігаються.
Спосіб 4: комплексна формула
Також порівняти дані можна за допомогою складної формули, основою якої є функція СЧЁТЕСЛІ. За допомогою даного інструменту можна зробити підрахунок того, скільки кожен елемент з вибраного стовпця другий таблиці повторюється в першій.
оператор СЧЁТЕСЛІ відноситься до статистичної групі функцій. Його завданням є підрахунок кількості осередків, значення в яких задовольняють заданій умові. Синтаксис цього оператора має такий вигляд:
СЧЁТЕСЛІ (діапазон; критерій)
аргумент "Діапазон" є адреса масиву, в якому проводиться підрахунок співпадаючих значень.
аргумент «Критерій»задає умову збігу. У нашому випадку він буде являти собою координати конкретних осередків першої табличній області.
Звичайно, цей вислів для того, щоб порівняти табличні показники, можна застосовувати і в існуючому вигляді, але є можливість його удосконалити.
Зробимо так, щоб ті значення, які є в другій таблиці, але відсутні в першій, виводилися окремим списком.
- Перш за все, трохи переробимо нашу формулу СЧЁТЕСЛІ, А саме зробимо її одним з аргументів оператора ЯКЩО. Для цього виділяємо перший осередок, в якій розташований оператор СЧЁТЕСЛІ. У рядку формул перед нею дописуємо вираз «ЯКЩО» без лапок і відкриваємо дужку. Далі, щоб нам легше було працювати, виділяємо в рядку формул значення «ЯКЩО» і тиснемо на іконку «Вставити функцію».
- Відкривається вікно аргументів функції ЯКЩО. Як бачимо, перше поле вікна вже заповнене значенням оператора СЧЁТЕСЛІ. Але нам потрібно дописати дещо ще в це поле. Встановлюємо туди курсор і до вже існуючого висловом дописуємо «=0»
без лапок.
Після цього переходимо до поля «Значення якщо істина». Тут ми скористаємося ще однією вкладеної функцією - СТРОКА. вписуємо слово «СТРОКА» без лапок, далі відкриваємо дужки і вказуємо координати першого осередку з прізвищем в другій таблиці, після чого закриваємо дужки. Саме в нашому випадку в поле «Значення якщо істина» вийшло такий вираз:
СТРОКА (D2)
тепер оператор СТРОКА буде повідомляти функції ЯКЩО номер рядка, в якій розташована конкретне прізвище, і в разі, коли умова, заданий в першому полі, буде виконуватися, функція ЯКЩО буде виводити цей номер в клітинку. Тиснемо на кнопку «OK».
- Як бачимо, перший результат відображається, як «БРЕХНЯ». Це означає, що значення не задовольняє умовам оператора ЯКЩО. Тобто, перша прізвище присутній в обох списках.
- За допомогою маркера заповнення, вже звичним способом копіюємо вираз оператора ЯКЩО на весь стовпець. Як бачимо, по двох позиціях, які присутні в другій таблиці, але відсутні в першій, формула видає номера рядків.
- Відступаємо від табличній області вправо і заповнюємо колонку номерами по порядку, починаючи від 1 . Кількість номерів має збігатися з кількістю рядків у другій порівнюєш таблиці. Щоб прискорити процедуру нумерації, можна також скористатися маркером заповнення.
- Після цього виділяємо перший осередок праворуч від колонки з номерами і клацаємо по значку «Вставити функцію».
- відкривається Майстер функцій. Переходимо в категорію «Статистичні» і виробляємо вибір найменування «НАЙМЕНШИЙ». Клацаємо по кнопці «OK».
- функція НАЙМЕНШИЙ, Вікно аргументів якої було розкрито, призначена для виведення зазначеного за рахунком найменшого значення.
У полі «Масив» слід вказати координати діапазону додаткового стовпця «Кількість збігів», Який ми раніше перетворили за допомогою функції ЯКЩО. Робимо все посилання абсолютними.
У полі «K» вказується, яке за рахунком найменше значення потрібно вивести. Тут вказуємо координати першої комірки стовпчика з нумерацією, який ми недавно додали. Адреса залишаємо відносним. Клацаємо по кнопці «OK».
- Оператор виводить результат - число 3 . Саме воно найменше з нумерації незбіжних рядків табличних масивів. За допомогою маркера заповнення копіюємо формулу до самого низу.
- Тепер, знаючи номера рядків незбіжних елементів, ми можемо вставити в клітинку і їх значення за допомогою функції ІНДЕКС. Виділяємо перший елемент листа, що містить формулу НАЙМЕНШИЙ. Після цього переходимо до рядка формул і перед найменуванням «НАЙМЕНШИЙ» дописуємо назву «ІНДЕКС» без лапок, тут же відкриваємо дужку і ставимо крапку з комою ( ; ). Потім виділяємо в рядку формул найменування «ІНДЕКС» і натискаємо на піктограму «Вставити функцію».
- Після цього відкривається невелике віконце, в якому потрібно визначити, контрольний вид повинна мати функція ІНДЕКС або призначений для роботи з масивами. Нам потрібен другий варіант. Він встановлений за замовчуванням, так що в даному віконці просто клацаємо по кнопці «OK».
- Запускається вікно аргументів функції ІНДЕКС. Даний оператор призначений для виведення значення, яке розташоване в певному масиві в зазначеному рядку.
Як бачимо, поле "Номер строки" вже заповнене значеннями функції НАЙМЕНШИЙ. Від вже існуючого там значення слід відняти різницю між нумерацією листа Excel і внутрішньої нумерацією табличній області. Як бачимо, над табличними значеннями у нас тільки шапка. Це означає, що різниця становить один рядок. Тому дописуємо в поле "Номер строки" значення «-1» без лапок.
У полі «Масив» вказуємо адресу діапазону значень другої таблиці. При цьому всі координати робимо абсолютними, тобто, ставимо перед ними знак долара вже раніше описаним нами способом.
Тиснемо на кнопку «OK».
- Після виведення результат на екран простягаємо функцію за допомогою маркера заповнення до кінця стовпчика вниз. Як бачимо, обидва прізвища, які присутні в другій таблиці, але відсутні в першій, виведені в окремий діапазон.
Спосіб 5: порівняння масивів в різних книгах
При порівнянні діапазонів в різних книгах можна використовувати перераховані вище способи, виключаючи ті варіанти, де потрібне розміщення обох табличних областей на одному аркуші. Головна умова для проведення процедури порівняння в цьому випадку - це відкриття вікон обох файлів одночасно. Для версій Excel 2013 і пізніше, а також для версій до Excel 2007 з виконанням цієї умови немає ніяких проблем. Але в Excel 2007 і Excel 2010 для того, щоб відкрити обидва вікна одночасно, потрібно провести додаткові маніпуляції. Як це зробити розповідається в окремому уроці.
Як бачимо, існує цілий ряд можливостей порівняти таблиці між собою. Який саме варіант використовувати залежить від того, де саме розташовані табличні дані щодо один одного (на одному аркуші, в різних книгах, на різних аркушах), а також від того, як саме користувач бажає, щоб це порівняння виводилося на екран.
Друзі, радий вас вітати на сторінках мого блогу. Сьогодні хочу розповісти вам про 4 способи порівняння файлів по вмісту. Необхідність знайти відмінності виникає досить часто. Доводиться робити порівняння файлів за типом або розміром. Порівняння файлів в Word і Excel теж вельми поширена процедура в офісах і конторах, де постійно доводиться працювати з текстовими документами і таблицями.
Але для веб-майстра або власника сайту часто буває необхідно порівняти два файли за вмістом. І мова йде не про текстових файлах, хоча і вони теж аналізуються так само, а про форматах.html, .css, .php і так далі. Тих файлах, де потрібно знайти зміни в коді. І часом зміна одного лише знака може забезпечити безліч труднощів з досягненням результату.
Так, останній приклад, коли я використовував порівняння двох файлів, - це проблема з роботою коментарів. У своєму я згадував про це. Причина була всього лише в одному прогалині. Ви тільки уявіть, як можна знайти таке несуттєве відмінність вручну.
І, взагалі, працюючи з файлами сайту, досить часто доводиться аналізувати код. Робити це вручну неможливо. Куди простіше скористатися підручними засобами, інструментами, які повинні бути у кожного веб-майстра і власника сайту.
Спосіб 1. Порівняння файлів в програмі Total Commander.
Цією програмою я користуюся давно. Для роботи з файлами, на мій погляд, це одна з кращих програм. Програма Total Commander платна, але можна протягом 30 днів користуватися і пробною версією, яка після закінчення часу також буде працювати, але з пропозицією про покупку. Завантажити пробну версію можна.
Раніше я розповідав, як налаштувати через TC, яке дозволяє легко управляти файлами сайту і здійснювати всі необхідні операції прямо з комп'ютера, минаючи особистий кабінет хостингу. Тільки через одну цієї можливості вже варто використовувати цю програму.
А тепер розберемо інструмент порівняння файлів по вмісту, який також дозволяє редагувати і копіювати дані в процесі аналізу.
Отже, запускаєте Total Commander - в одній з панелей вибираєте перший файл для порівняння - у другій панелі відкриваєте папку з другим файлом.
Відкриється нове вікно, також розділене на дві панелі. У самому верху, в кожній панелі буде терміну для вибору файлу. В одній з них перший файл буде вже обраний (але може бути і порожнім). Для вибору файлу потрібно натиснути на невелику кнопочку зі стрілками (>>) .
Вибравши файли для порівняння, натискаєте на кнопку «Порівняти». У кожній панелі з'явиться вміст вибраного файлу. Відмінності будуть відзначені фоновим кольором.
Для внесення змін до файл досить натиснути на кнопку "Редагувати". І можна приступати до внесення змін. Також тут будуть доступні функції копіювання і відкату, пошуку і зміна кодування.
Якщо ви внесли зміни в файл, то після закриття вікна порівняння, буде запропоновано зберегти результат.
Спосіб 2. Порівняння файлів в Notepad ++.
Якщо вам доводилося, хоч коли-небудь, редагувати файли html, css і php. То напевно ви чули про безкоштовне редакторі коду Notepad ++. Завантажити програму можна.
Ця прекрасна програма, яка до незліченним її достоїнств дозволяє ще й файли порівнювати з їх вмісту.
Але для цього буде потрібно встановити невеликий плагін. Робиться все це безпосередньо всередині самого редактора.
Отже, запускаєте редактор - переходите до пункту меню «Модулі» — «Plugin Manager» — «Show Plugin Manager».
У новому вікні вибираєте плагін « Compare » і тиснете кнопку « Install ».
Після встановлення плагіну можна приступати до порівняння вмісту файлів. Для цього потрібно відкрити два файли - перейти в пункт меню «Модулі» — « Compare » — « Compare (Alt +D) ».
Результат порівняння файлів буде представлений в окремих панелях. Тобто в лівій буде перший файл, а в правій другий. Навпаки рядків, в яких знайдені відмінності буде застережливий знак і підсвічування фоновим кольором.
Рядок, де знайдені малі відмінності, буде підсвічуватися жовтим кольором, і позначена знаком оклику.
Рядок, яка повністю відрізняється, буде відзначена на одній панелі червоним мінусом на інший зеленим плюсом. І підсвічена відповідним кольором.
Зрозуміло, знайшовши відміну, ви відразу можете внести необхідні поправки і зберегти зміни.
Завершити порівняння файлів можна комбінацією клавіш Ctrl +Alt +D.
Спосіб 3. Порівняння вмісту двох файлів в програмі WinMerge.
Ця програма дозволяє порівнювати не тільки вміст файлів, але і вміст цілих папок. Цим вона схожа на Тотал Коммандер, тільки програма безкоштовна. Завантажити її можна.
Установка програми стандартна і не складе труднощів. Тому на цьому зупинятися не будемо, а відразу перейдемо до процесу порівняння двох файлів.
Запустивши програму, потрібно вибрати файли для порівняння. Відкриваєте пункт меню «Файл» — «Відкрити».
Результат порівняння представлений в двох панелях, так само як і в інших прикладах. Тільки тут ще й виділяється саме той фрагмент коду, який відрізняється в конкретній рядку.
У цій програмі можна також редагувати файли. А коли будете закривати вікно порівняння, програма запропонує зберегти зміни в файлах.
Спосіб 4. Порівняння файлів за допомогою командного рядка Windows.
Цей спосіб дозволяє порівнювати вміст файлів без сторонніх програм. Досить скористатися стандартним інструментом операційної системи Windows.
Також цей спосіб не дозволяє редагувати файли. Для цього потрібно використовувати інші інструменти. Але, тим не менше з поставленим завданням ви впораєтеся без додаткових програм.
Для цього відкриваєте «Пуск» — «Усі програми» — «Стандартні» — "Командний рядок". І вводите ось цю команду:
Fc / N шлях до першого файлу шлях до другого файлу
виглядає це так:
Fc / N C: \\ Мої документи \\ file1.txt C: \\ Мої документи \\ file2.txt
Готовий результат буде виглядати так:
Коли відмінностей мало, то їх досить швидко можна виправити використовую для цього вже програми редагування. Але коли відмінностей багато, то аналізувати інформацію за допомогою цього способу досить важко.
Отже, про способи порівняння файлів я вам розповів, а тепер пропоную подивитися відеоурок в, якому я показую порівняння двох файлів усіма способами.
Упевнений, тепер якщо вам необхідно буде порівняти файли, то ви легко зможете це зробити, використовую один із запропонованих способів.
На цьому сьогодні все, всім бажаю успіхів і гарного настрою і до зустрічі в нових статтях і відеоуроку!
З повагою, Максим Зайцев.
У нас є дві таблиці замовлень, скопійованих в один робочий лист. Необхідно виконати порівняння даних двох таблиць в Excel і перевірити, які позиції є першою таблиці, але немає в другій. Немає сенсу вручну порівнювати значення кожного осередку.
Порівняння двох стовпців на збіги в Excel
Як зробити порівняння значень в Excel двох стовпців? Для вирішення даного завдання рекомендуємо використовувати умовне форматування, яке швидко виділити кольором позиції, що знаходяться тільки в одному стовпці. Робочий лист з таблицями:
В першу чергу необхідно присвоїти імена обом таблицями. Завдяки цьому легше зрозуміти, які порівнюються діапазони осередків:
- Виберіть інструмент «ФОРМУЛИ» - «Певні імена» - «Присвоїти ім'я».
- У вікні в полі «Ім'я:» введіть значення - Табліца_1.
- Лівою клавішею мишки зробіть клацання по полю введення «Діапазон:» і виділіть діапазон: A2: A15. І натисніть ОК.
Для другого списку виконайте ті ж дії тільки ім'я надайте - Табліца_2. А діапазон вкажіть C2: C15 - відповідно.
Корисна порада! Імена діапазонів можна привласнювати швидше за допомогою поля імен. Воно знаходиться лівіше від рядка формул. Просто виділяйте діапазони осередків, а в поле імен вводите відповідне ім'я для діапазону і натисніть Enter.
Тепер скористаємося умовним форматуванням, щоб виконати порівняння двох списків в Excel. Нам потрібно отримати наступний результат:
Позиції, які є в Табліце_1, але немає в Таблціе_2 будуть відображатися зеленим кольором. У той же час позиції, що знаходяться в Табліце_2, але відсутні в Табліце_1, будуть підсвічені синім кольором.
Принцип порівняння даних двох стовпців в Excel
При визначенні умов для форматування осередків стовпців ми використовували функцію СЧЕТЕСЛІ. В даному прикладі ця функція перевіряє скільки разів зустрічається значення другого аргументу (наприклад, A2) в списку першого аргументу (наприклад, Табліца_2). Якщо кількість разів \u003d 0 в такому випадку формула повертає значення ІСТИНА. В такому випадку осередку присвоюється призначений для користувача формат, зазначений в параметрах умовного форматування.
Посилання в другому аргументі відносна, значить по черзі будуть перевірятися всі осередки виділеного діапазону (наприклад, A2: A15). Наприклад, для порівняння двох прайсів в Excel навіть на різних аркушах. Друга формула діє аналогічно. Цей же принцип можна застосовувати для різних подібних завдань.
Іноді потрібно переглянути в таблиці Access тільки ті записи, яким відповідають записи з іншої таблиці, що містять поля з однаковими даними. Наприклад, вам може бути потрібно переглянути записи співробітників, обробити хоча б одне замовлення, щоб визначити, хто з них заслуговує заохочення. Або ж вам може знадобитися переглянути контактні дані клієнтів, які проживають в одному місті з співробітником, щоб організувати особисту зустріч.
Якщо вам потрібно порівняти дві таблиці Access і знайти збігаються дані, можливі два варіанти дій.
Створіть запит, який об'єднує поля з кожної таблиці, які містять відповідні дані, використовуючи для цього існуючий зв'язок або об'єднання, створене для запиту. Цей спосіб оптимальний за швидкістю повернення результатів запиту, але не дозволяє об'єднувати поля з даними різного типу.
Створити запит для порівняння полів, в якому одне поле використовується в якості умови для іншого. Цей спосіб зазвичай вимагає більше часу, оскільки при об'єднанні рядки виключаються з результатів запиту до читання базових таблиць, тоді як умови застосовуються до результатів запиту після читання цих таблиць. Зате поле можна використовувати в якості умови для порівняння полів з даними різного типу, чого не можна зробити при використанні об'єднань.
У цій статті розглядається порівняння двох таблиць для виявлення співпадаючих даних і наводиться зразок даних, які можна використовувати в прикладах процедур.
В цій статті
Порівняння двох таблиць за допомогою об'єднань
Для порівняння двох таблиць за допомогою об'єднань потрібно створити запит на вибірку, що включає обидві таблиці. Якщо між таблицями ще немає зв'язку по полях, що містить потрібні дані, вам потрібно створити об'єднання по ним. Об'єднань можна створити скільки завгодно, але кожна пара об'єднаних полів повинна містити дані одного і того ж або сумісного типу.
Припустимо, ви працюєте в університеті і хочете дізнатися, як недавні зміни в навчальному плані з математики вплинули на оцінки учнів. Зокрема, вас цікавлять оцінки тих студентів, у яких профілюючим предметом є математика. У вас вже є таблиця, яка містить дані про профілюючих предметах, і таблиця, яка містить дані про студентів, які їх вивчають. Дані про оцінки зберігаються в таблиці "Учні", а дані про профілюючих предметах - в таблиці "Спеціалізації". Щоб побачити, як після недавніх змін в навчальному плані змінилися оцінки у тих, хто спеціалізується на математиці, вам потрібно переглянути записи з таблиці "Учні", відповідні записам в таблиці "Спеціалізації".
Підготовка прикладу даних
В даному прикладі ви створюєте запит, який визначає, як недавні зміни в навчальному плані з математики вплинули на оцінки студентів з відповідним профілюючим предметом. Використовуйте дві наведені нижче таблиці: "Спеціалізації" і "Учні". Додайте їх в базу даних.
Access надає декілька способів додавання цих таблиць зразок бази даних. Можна ввести дані вручну, скопіюйте кожну таблицю в електронну таблицю програми (наприклад, Microsoft Office Excel 2007) і потім імпортуйте листи в Access або можна вставляти дані в текстовому редакторі, наприклад Блокнот і потім імпортувати дані з результуючого текстові файли.
У покрокових інструкціях цього розділу пояснюється, як вводити дані вручну на порожній лист, а також як копіювати приклади таблиць в Excel і потім імпортувати їх в Access.
спеціалізації
код учня | спеціалізація |
|
|---|---|---|
учні
код учня | семестр | Навчальний план | номер предмета | оцінка |
|
|---|---|---|---|---|---|
Якщо ви збираєтеся вводити приклад даних в електронній таблиці, можете.
Введення прикладів даних вручну
Якщо вас не цікавить створення листа на основі прикладу даних, пропустіть наступний розділ ( "Створення листів з прикладами даних").
Створення листів з прикладами даних
Створення таблиць бази даних на основі листів
Порівняння зразків таблиць і пошук відповідних записів з використанням об'єднань
Тепер все готово для порівняння таблиць "Учні" і "Спеціалізації". Так як зв'язку між двома таблицями не визначені, вам необхідно створити об'єднання відповідних полів в запиті. Таблиці містять по кілька полів, і вам буде потрібно створити об'єднання для кожної пари загальних полів: "Код учня", "Рік", а також "Навчальний план" (в таблиці "Учні") і "Спеціалізація" (в таблиці "Спеціалізації") . В даному випадку нас цікавить тільки математика, тому можна обмежити результати запиту за допомогою умови поля.
на вкладці створення натисніть кнопку конструктор запитів.
У діалоговому вікні Додавання таблиці двічі клацніть таблицю, яка містить потрібні записи ( учні), А потім двічі клацніть таблицю, з якої її порівнюєте ( спеціалізації).
Закрийте діалогове вікно Додавання таблиці.
перетягніть поле код учня з таблиці учні у полі код учня таблиці спеціалізації. У бланку запиту між двома таблицями з'явиться лінія, яка показує, що створено об'єднання. Двічі клацніть лінію, щоб відкрити діалогове вікно параметри об'єднання.
Зверніть увагу на три варіанта в діалоговому вікні параметри об'єднання. За умовчанням вибраний варіант 1. У деяких випадках потрібно додати в параметри об'єднання додаткові рядки з однієї таблиці. Так як вам потрібно знайти тільки збігаються дані, залиште для об'єднання значення 1. Закрийте діалогове вікно параметри об'єднання, Натиснувши кнопку скасування.
Вам потрібно створити ще два об'єднання. Для цього перетягніть поле рік з таблиці учні у полі рік таблиці спеціалізації, А потім - поле Навчальний план з таблиці учні у полі спеціалізація таблиці спеціалізації.
В таблиці учні двічі клацніть зірочку ( * ), Щоб додати всі поля таблиці в бланк запиту.
Примітка: Учні. *.
В таблиці спеціалізації двічі клацніть поле спеціалізація, Щоб додати його в бланк.
Показати стовпчика спеціалізація.
В рядку Условие отбора стовпчика спеціалізація введіть матем.
на вкладці конструктор у групі результати натисніть кнопку виконати.
Порівняння двох таблиць з використанням поля в якості умови
Іноді необхідно порівняти таблиці на основі полів з співпадаючими значеннями, але різними типами даних. Наприклад, поле в одній таблиці може мати числовий тип даних, а порівняти його потрібно з полем з іншої таблиці, яке має текстовий тип даних. Поля, що містять аналогічні дані різного типу, можуть з'явитися при збереженні чисел в якості тексту (наприклад, при імпорті даних з іншої програми). Так як створити об'єднання полів з даними різного типу неможливо, вам буде потрібно використовувати інший спосіб. Для порівняння двох полів з даними різного типу можна використовувати одне поле в якості умови для іншого.
Припустимо, ви працюєте в університеті і хочете дізнатися, як недавні зміни в навчальному плані з математики вплинули на оцінки учнів. Зокрема, вас цікавлять оцінки тих студентів, у яких профілюючим предметом є математика. У вас вже є таблиці "Спеціалізації" і "Учні". Дані про оцінки зберігаються в таблиці "Учні", а дані про профілюючих предметах - в таблиці "Спеціалізації". Щоб побачити, як змінилися оцінки у тих, хто спеціалізується на математиці, вам потрібно переглянути записи з таблиці "Учні", відповідні записам в таблиці "Спеціалізації". Однак у одного з полів, які ви хочете використовувати для порівняння таблиць, тип даних не такий, як у поля, з яким воно зіставляється.
Для порівняння двох таблиць з використанням поля в якості умови потрібно створити запит на вибірку, що включає обидві таблиці. Включіть в запит поля, які хочете відобразити, а також поле, відповідне тому полю, яке буде використовуватися в якості умови. Потім створіть умова для порівняння таблиць. Ви можете створити стільки умов, скільки буде потрібно для порівняння полів.
Щоб проілюструвати цей спосіб, ми використовуємо, але в поле "Код учня" таблиці "Спеціалізації" змінимо числовий тип даних на текстовий. Так як не можна створити об'єднання двох полів з різними типами даних, нам доведеться порівняти два поля "Код учня", використовуючи одне поле в якості умови для іншого.
Зміна типу даних в поле "Код учня" таблиці "Спеціалізації"
Відкрийте базу даних, в якій ви зберегли приклади таблиць.
В області навігації клацніть таблицю "Спеціалізації" правою кнопкою миші і виберіть пункт конструктор.
Таблиця "Спеціалізації" відкриється в режимі конструктора.
У стовпці Тип даних змініть для поля код учня тип даних число на текст.
Закрийте таблицю "Спеціалізації". Натисніть кнопку Так, Коли вам буде запропоновано зберегти зміни.
Порівняння прикладів таблиць і пошук відповідних записів з використанням умови поля
Нижче показано, як порівняти два поля "Код учня", використовуючи поле з таблиці "Учні" як умову для поля з таблиці "Спеціалізації". За допомогою ключового слова Like ви можете порівняти два поля, навіть якщо вони містять дані різного типу.
на вкладці створити у групі інше натисніть кнопку конструктор запитів.
У діалоговому вікні Додавання таблиці двічі клацніть таблицю учні, А потім таблицю спеціалізації.
Закрийте діалогове вікно Додавання таблиці.
перетягніть поле рік з таблиці учні у полі рік таблиці спеціалізації, А потім - поле Навчальний план з таблиці учні у полі спеціалізація таблиці спеціалізації. Ці поля містять дані одного типу, тому для їх порівняння можна використовувати об'єднання. Для порівняння полів з даними одного типу рекомендується використовувати об'єднання.
Двічі клацніть зірочку ( * ) в таблиці учні, Щоб додати всі поля таблиці в бланк запиту.
Примітка: При використанні зірочки для додавання всіх полів в бланку відображається тільки один стовпець. Ім'я цього стовпця включає ім'я таблиці, за яким слідують точка (.) І зірочка (*). У цьому прикладі стовпець отримує ім'я Учні. *.
В таблиці спеціалізації двічі клацніть поле код учня, Щоб додати його в бланк.
У бланку запиту зніміть прапорець в рядку Показати стовпчика код учня. В рядку Условие отбора стовпчика код учня введіть Like [Учні]. [Код учня].
В таблиці спеціалізації двічі клацніть поле спеціалізація, Щоб додати його в бланк.
У бланку запиту зніміть прапорець в рядку Показати стовпчика спеціалізація. В рядку Условие отбора введіть матем.
на вкладці конструктор у групі результати натисніть кнопку виконати.
Запит виконується, і відображаються оцінки з математики тільки тих учнів, у яких цей предмет профілюючий.