Як зробити зв'язок в excel. Зв'язок багато-до-багатьох: приклад в Access, в SQL

Раніше описаний спосіб вставки таблиці як графічного об'єкта має певні обмеження, такі, як: неможливість зміни даних, спотворення при масштабуванні, нечитаність дрібного шрифту і т.д.

Другий запропонований спосіб - вставляти листи Excel безпосередньо в документ, - призводить до невиправданого «роздування» файлу, крім того, впроваджені об'єкти іноді поводяться непередбачувано.

Однак, використовуючи технологію Automation, об'єкти можна не тільки впроваджувати, а й пов'язувати.

Зараз ми зв'яжемо між собою таблиці Еxcel і Word, причому так, щоб зміни у вихідній таблиці приводили до змін в таблиці документа. Отже, у нас є вихідна таблиця з розрахунками:

Створюємо в Word точно таку ж, але порожню таблицю:

Заголовки не набирає, а спеціально залишаємо порожні рядки.

Після виконаних операцій, на екрані має бути відкриті обидва вікна: вікно MS Word із заготівлею і вікно MS Excel з розрахунковою таблицею. Чи можемо приступати до зв'язування. Суть його полягає в тому, щоб послідовно, одна за одною, зв'язати елементи таблиці з необхідними позиціями в текстовому документі.

Для цього:

Перейдіть на лист Еxcel і зробіть поточної осередок, яку необхідно пов'язати з документом (почнемо з В1).
Виконуємо копіювання комірки (меню Виправлення-Копіювати або поєднання клавіш Ctrl + C).
Переходимо у вікно з документом Word і поміщаємо курсор в позицію для вставки. В даному випадку це буде заголовок, розташований через один рядок над таблицею.
В меню Word вибираємо Виправлення-Спеціальна вставка. В результаті відкриється діалогове вікно спеціальна вставка, Де в області джерело (В лівому верхньому кутку) вказані координати виділеної комірки:
Встановлюємо перемикач в положення зв'язати.
В списку як вибираємо звичайний текст.
Клацанням по кнопці ОК закриваємо вікно.

Таким чином ми встановили зв'язок між текстової позицією в документі і осередком в робочому аркуші. Необхідно відразу зазначити, що з об'єднаними осередками зв'язування працює не зовсім коректно. Аналогічно необхідно пов'язати залишилися осередки. Остаточно документ повинен виглядати так:

Тепер ми маємо можливість форматувати таблицю, як нам зручно, при цьому зміна даних буде відбуватися автоматично. Також можна відзначити, що таблицю в Word можна використовувати як зведену, заносячи в неї значення з різних листів і навіть книг Еxcel.

Режим оновлення також можна налаштовувати. Для цього необхідно:

Вибрати будь-який вставлене значення в Word і натиснути праву кнопку миші:
У контекстному меню вибрати Пов'язаний об'єкт Лист-Зв'язки.
У вікні можна вибирати Спосіб поновлення зв'язку:

Спосіб оновлення можна змінювати для кожного зв'язку окремо, а можна виділити змінити для всіх значень або для декількох відразу.

Даний спосіб при разовому використанні підходить мало, так як він досить трудомісткий на початковому етапі. Однак при частих обчисленнях (курсові, дипломні роботи, фінансові звіти і т.д.) він окупається. Також при зміні структури таблиці в Excel відбувається порушення зв'язків, що тягне за собою руйнування цілісності таблиці в документі. Тому зв'язування краще виконувати після остаточного формування розрахункових таблиць.

Дуже багато в своїй роботі стикаються з необхідністю формувати звіти, зведення, узагальнюючі документи на основі якихось обчислень, розрахунків, таблиць з вихідними даними і їх аналізу. І часто для виконання цих завдань доводиться вдаватися до комбінації таких програмних продуктів як Word (де формується підсумковий звіт для подальшої роздруківки або пересилання кому-небудь) і Excel (в якому збираються і обробляються вихідні дані для звіту).

Якщо разові завдання не викликають особливих питань, то формування періодичних звітів часом завдає чимало проблем. Рутинне копіювання одних і тих же табличок або графіків (з трохи зміненими даними) в одні і ті ж документи вкрай стомлює. Але цей процес можна автоматизувати. Як це зробити - читайте нижче.

Створення зв'язку між Word та Excel

Найшвидший і ефективний спосіб створити зв'язки між файлами - скопіювати потрібну інформацію з одного і вставити в інший, скориставшись інструментом «Спеціальна вставка».Для цього потрібно виділити таблицю, осередок або діаграму в Excel, перейти в Word, поставити курсор в потрібну частину документа і:

В результаті вставлений об'єкт буде пов'язаний з тим файлом, звідки він був скопійований, і при оновленні вихідних даних можна буде швидко оновити зв'язку в документі Word.

Вибір способу вставки даних

Як видно на малюнку 2, є кілька способів вставки даних в документ. У повсякденній роботі доведеться вибирати, по суті, між двома варіантами - вставити таблицю як картинку (без можливості її форматування) або як текст (а значить, з можливістю вносити зміни). Для діаграм варіант всього один - картинка.

Крім того, важливо відрізняти пов'язані об'єкти від впроваджених. Впровадження - операція, яка не розглядається в цій статті. Суть її в тому, що в документ Word впроваджується об'єкт Excel, який дозволяє працювати з даними в табличному процесорі прямо з документа Word. При цьому вихідний файл і текстовий документ ніяк не пов'язані. Впровадження використовується рідко, так як сильно збільшує розмір файлу.

У більшості випадків при виборі способу вставки таблиці слід вибирати текстовий варіант, щоб мати можливість налаштувати форматування під себе. Картинки можна вставляти, якщо форматування в вихідному документі Excel Вас повністю влаштовує.

Бувають ситуації, коли доводиться вставляти не всю таблицю, а дані з окремих осередків. Наприклад, коли потрібно вставити в пропозицію якусь цифру або текст, які є в файлі Excel і будуть змінюватися, або ж коли таблиця в Word складається з декількох розрізнених осередків Excel (з різних таблиць, листів або навіть книг). У таких випадках копіюємо дані осередків в потрібне місце і вибираємо спосіб вставки «Неформатований текст».

Налаштування способу поновлення зв'язків і робота з диспетчером зв'язків

Вставлені зв'язку можна оновлювати декількома способами. Наприклад, якщо на вставленому об'єкті натиснути правою кнопкою миші, то в меню буде кнопка «Оновити зв'язок», натискання якої призведе до оновлення обраної зв'язку.

Можна задати параметри оновлення відразу для всіх об'єктів. Для цього потрібно викликати диспетчер зв'язків документа. Проходимо по шляху «Файл» - «Відомості» - «Змінити зв'язку з файлами» (Дана команда доступна, коли в документі є як мінімум одна зв'язок, а сам документ збережений).

У диспетчері показані всі зв'язки файлу. У групі «Спосіб поновлення зв'язку» можна вибрати той варіант, який більш кращий або взагалі відключити оновлення зв'язків. Також для кожного зв'язку можна задати налаштування «Зберігати формат при оновленні». Вона відповідає за те, щоб обраний Вами форматування не злітало, коли ви відновите зв'язок. У цьому ж диспетчері можна розірвати зв'язки, відкрити або змінити джерело.

Робота зі зв'язками через коди полів Word

За своєю суттю кожна зв'язок являє собою спеціальне поле Word. Якщо в документі зі зв'язками Ви натиснете ALT + F9, То побачите, що замість вставлених даних відобразяться коди цих полів.

Робота з ними відкриває можливості, які не надає стандартний диспетчер зв'язків. І хоча коди полів Word - найширша тема, що виходить далеко за рамки даної статті, пару прикладів їх корисного використання ми наведемо:

1) Зміна посилань на документи відразу у всіх зв'язках

Зв'язки з вихідними документами здійснюються через прямі посилання. Якщо Ви переклали вихідний файл, то потрібно буде переподтянуть всі зв'язки. У диспетчері зв'язків доведеться робити це для кожного зв'язку окремо (навіть якщо вибрати кілька - диспетчер буде по черзі пропонувати замінювати кожне посилання). Якщо ж ви перейдете в режим відображення кодів полів ( ALT + F9), То старі посилання можна поміняти на нові простою заміною ( CTRL + H або «Головна-Замінити»), Вказавши стару адресу і новий.

2) Використання іменованих діапазонів для вставки таблиць

Якщо ви вставляєте в Word таблицю з Excel, то посилання на неї буде містити конкретний діапазон комірок. Тобто, якщо ви додасте в таблицю рядки або стовпці - то дані оновляться тільки в межах жорстко зазначеного діапазону. Диспетчер зв'язків дозволяє змінити діапазон вручну. Автоматичне ж зміна можна налаштувати, задавши в Excel іменований динамічний діапазон (урок тут і тут, що не розберетеся - пишіть нашому боту в Telegram, Допоможемо), а в файлі Word в режимі редагування кодів полів після цього потрібно буде замінити прямі посилання на осередки на назву іменованого діапазону. Тепер при зміні розміру вихідної таблиці в Excel документ в Word оновиться коректно.

Ваші питання за статтею можете задавати через нашого бота зворотного зв'язку в Telegram:@KillOfBot

З повагою, команда tDots.ru

Зв'язок між таблицями Excel - це формула, яка повертає дані з комірки інший робочої книги. Коли ви відкриваєте книгу, яка містить зв'язку, Excel зчитує останню інформацію з книги-джерела (оновлення зв'язків)

Міжтабличних зв'язку в Excel використовуються для отримання даних як з інших листів робочої книги, так і з інших робочих книг Excel. Наприклад, у вас є таблиця з розрахунком підсумкової суми продажів. У розрахунку використовуються ціни на продукт і обсяг продажів. В такому випадку має сенс створити окрему таблицю з даними за цінами, які будуть підтягуватися за допомогою зв'язків першої таблиці.

Коли ви створюєте зв'язок між таблицями, Excel створює формулу, яка включає в себе ім'я вихідної книги, укладену в дужки, ім'я листа зі знаком оклику на кінці і посилання на осередок.

Створення зв'язків між робочими книгами

Відкриваємо обидві робочі книги в Excel
У вихідній книзі вибираємо осередок, яку необхідно зв'язати, і копіюємо її (поєднання клавіш Ctrl + С)
Переходимо в кінцеву книгу, клацаємо правою кнопкою миші по комірці, куди ми хочемо помістити зв'язок. З випадного меню вибираємо спеціальна вставка
спеціальна вставкавибираємо Вставити зв'язок.

Є ще один, більш простий, варіант створення зв'язку між таблицями. У осередок, куди ми хочемо вставити зв'язок, ставимо знак рівності (так само як і для звичайної формули), переходимо в вихідну книгу, вибираємо осередок, яку хочемо зв'язати, клацаємо Enter.

Ви можете використовувати інструменти копіювання і автозаповнення для формул зв'язку так само, як і для звичайних формул.

Перш ніж створювати зв'язку між таблицями

Перш ніж ви почнете поширювати знання на свої грандіозні ідеї, прочитайте кілька порад по роботі зі зв'язками в Excel:

Робіть легко відслідковують зв'язку.Не існує автоматичного пошуку всіх осередків, які містять посилання. Тому, використовуйте певний формат, щоб швидко ідентифікувати зв'язку з іншими таблицями, в іншому випадку, документ, що містить посилання, може розростися до такої міри, що його буде важко підтримувати.

Автоматичні обчислення.Вихідна книга повинна працювати в режимі автоматичного обчислення (встановлено за умовчанням). Для перемикання параметра обчислення перейдіть по вкладці формулив групу Обчислення.Оберіть Параметри обчислень -\u003e Автоматично.

оновлення зв'язків

Для ручного поновлення зв'язку між таблицями, перейдіть по вкладці дані в групу підключення. Клацніть по кнопці Змінити зв'язку.

У діалоговому вікні Зміна зв'язків, Оновити.

Розірвати зв'язку в книгах Excel

Розрив зв'язку з джерелом призведе до заміни існуючих формул зв'язку на значення, які вони повертають. Наприклад, зв'язок \u003d Ціни! $ B $ 4 буде замінена на 16. Розрив зв'язку не можна скасувати, тому перш ніж зробити операцію, рекомендую зберегти книгу.

Перейдіть по вкладці дані в групу підключення. Клацніть по кнопці Змінити зв'язку.У діалоговому вікні Зміна зв'язків,виберіть цікаву для вас зв'язок і клацніть по кнопці Розірвати зв'язок.

Microsoft Access 2007

2.4. Microsoft Access 2007

2.4.3. Установка логічних зв'язків в БД Access 2007

логічні зв'язки встановлюються між однойменними полями таблиць бази даних Access 2007. Зв'язок даних в одній таблиці з даними в інших таблицях здійснюється через унікальні ідентифікатори (ключі) або ключові поля. У нашому випадку ми повинні встановити логічні зв'язки між таблицями: Групи студентів, Студенти, Дисципліни і Успішність.

Для встановлення зв'язків використовуємо ключові поля: КодГруппи, КодСтудентов і КодДісціпліни. Наприклад, між первинним ключем (КодГруппи) tables Групи студентів і вторинним ключем (КодГруппи) tables Студенти встановлюємо зв'язок один - до - багатьох.

Перш ніж приступити до створення логічних зв'язків треба в Вікні редагування закрити всі tables і перейти на вкладку Робота з базами даних. Потім клацнути на піктограмі Схема даних, у вікні редагування з'явиться активне діалогове вікно "Додавання таблиці" на тлі неактивного вікна Схема даних (рис. 1).

Мал. 1.

У вікні Додавання таблиць необхідно виділити імена таблиць і натиснути кнопку Додати, при цьому у вікні "Схема даних" з'являться всі tables (рис. 2). Після цього необхідно закрити вікно діалогу.

Мал. 2.

Далі необхідно встановити зв'язки між табл. у вікні Схема даних. Для цього у вікні Схема даних необхідно відбуксирувати (перемістити) поле КодГруппи з таблиці Групи студентів на відповідне поле tables Студенти, в результаті цієї операції з'явиться вікно "Зміна зв'язків" (рис. 3).

Мал. 3.

У вікні діалогу "Зміна зв'язків" (рис. 3) необхідно встановити прапорці: "Забезпечити цілісність даних", "каскадне оновлення пов'язаних полів" і "каскадне видалення зв'язаних записів", переконатися в тому, що встановлений тип відносин один-ко-многим і натиснути кнопку Створити.

У вікні Схема даних з'явиться зв'язок один-ко-многим між таблицями Групи студентів і Студенти. Аналогічним чином треба зв'язати поля КодСтудента в таблицях Студенти і Успішність, а потім поля КодДісціпліни в таблицях Успішність і Дисципліни. В результаті отримаємо Схему даних, представлену на малюнку 4.

Мал. 4.

Після установки зв'язків між таблицями, вікно Схема даних необхідно закрити. Далі необхідно здійснити заповнення всіх таблиць. Заповнення доцільно починати з табл. Групи студентів, так як поле КодГруппи табл. Студенти використовується в якості стовпця підстановки для заповнення відповідного поля табл. Студенти.

Потім встановити зв'язку між табл. "Студенти" і "Успішність", "Дисципліни" і "Успішність", так як поля КодСтуденти і КодДісціпліни табл. Успішність використовується в якості стовпця підстановки для заповнення відповідних полів таблиці Успішність.

Іноді необхідно створити незмінну зв'язок одного осередку зі значенням в іншому осередку, введеному в неї вручну або отриманим в результаті обчислення формули.

У вікні відкритого листа виділіть клітинку з початковим значенням.
Використовуйте будь-який відомий спосіб копіювання (кнопка «Копіювати» на вкладці «Головна», Ctrl + C і т. Д.).
Виділіть клітинку, яка буде пов'язана з вихідною.
Перейдіть до вкладки «Головна» і в групі «Буфер обміну» розкрийте меню кнопки «Вставити».
У списку команд виберіть пункт «Вставити зв'язок» (рис. 4.4).
Закріпіть результат клацанням по клавіші Esc.

Примітка

Подальше зміни значень у вихідній клітинці будуть автоматично відображатися в пов'язаної осередку.

Мал. 4.4. Вкладка «Головна». Меню кнопки «Вставити». Пункт «Вставити зв'язок»

Створення зв'язків між робочими книгами

Відкриваємо обидві робочі книги в Excel
У вихідній книзі вибираємо осередок, яку необхідно зв'язати, і копіюємо її (поєднання клавіш Ctrl + С)
Переходимо в кінцеву книгу, клацаємо правою кнопкою миші по комірці, куди ми хочемо помістити зв'язок. З випадного меню вибираємо спеціальна вставка
спеціальна вставкавибираємо Вставити зв'язок.

Перш ніж створювати зв'язку між таблицями

оновлення зв'язків

У діалоговому вікні Зміна зв'язків, Оновити.

Розірвати зв'язку в книгах Excel

При виконанні певних завдань в Excel іноді доводиться мати справу з декількома таблицями, які до того ж пов'язані між собою. Тобто, дані з однієї таблиці підтягуються в інші і при їх зміні перераховуються значення у всіх пов'язаних табличних діапазонах.

Пов'язані таблиці дуже зручно використовувати для обробки великого обсягу інформації. Розташовувати всю інформацію в одній таблиці, до того ж, якщо вона не однорідна, не дуже зручно. З подібними об'єктами важко працювати і виробляти по ним пошук. Зазначену проблему якраз покликані усунути пов'язані таблиці, інформація між якими розподілена, але в той же час є взаємопов'язаної. Пов'язані табличні діапазони можуть перебувати не тільки в межах одного аркуша або однієї книги, але і розташовуватися в окремих книгах (файлах). Останні два варіанти на практиці використовують найчастіше, тому що метою зазначеної технології є як раз піти від скупчення даних, а нагромадження їх на одній сторінці принципово проблему не вирішує. Давайте дізнаємося, як створювати і як працювати з таким видом управління даними.

Створення пов'язаних таблиць

Перш за все, давайте зупинимося на питанні, якими способами існує можливість створити зв'язок між різними табличними діапазонами.

Спосіб 1: пряме зв'язування таблиць формулою

Найпростіший спосіб зв'язування даних - це використання формул, в яких є посилання на інші табличні діапазони. Він називається прямим зв'язуванням. Цей спосіб інтуїтивно зрозумілий, так як при ньому зв'язування виконується практично точно так же, як створення посилань на дані в одному табличному масиві.

Подивимося, як на прикладі можна утворити зв'язок шляхом прямого зв'язування. Маємо дві таблиці на двох аркушах. На одній таблиці проводиться розрахунок заробітної плати за допомогою формули шляхом множення ставки працівників на єдиний для всіх коефіцієнт.

На другому аркуші розташований табличний діапазон, в якому знаходиться перелік співробітників з їх окладами. Список співробітників в обох випадках представлений в одному порядку.

Потрібно зробити так, щоб дані про ставки з другого листа підтягувалися до відповідних осередку першого.

На першому аркуші виділяємо перший осередок шпальти «Ставка». Ставимо в ній знак «\u003d». Далі натискаємо по ярличку «Лист 2», який розміщується в лівій частині інтерфейсу Excel над рядком стану.
Відбувається переміщення в другу область документа. Клацаємо по першій клітинці в стовпці «Ставка». Потім натискаємо на кнопку Enter на клавіатурі, щоб зробити введення даних в осередок, в якій раніше встановили знак «дорівнює».
Потім відбувається автоматичний перехід на перший лист. Як бачимо, в відповідному полі підтягується величина ставки першого співробітника з другої таблиці. Встановивши курсор на клітинку, яка містить ставку, бачимо, що для виведення даних на екран застосовується звичайна формула. Але перед координатами осередки, звідки виводяться дані, варто вираз «Аркуш2!», Яке вказує найменування області документа, де вони розташовані. Загальна формула в нашому випадку виглядає так:
Тепер потрібно перенести дані про ставки всіх інших працівників підприємства. Звичайно, це можна зробити тим же шляхом, яким ми виконали поставлене завдання для першого працівника, але з огляду на, що обидва списки співробітників розташовані в однаковому порядку, завдання можна істотно спростити і прискорити її рішення. Це можна зробити, просто скопіювавши формулу на діапазон нижче. Завдяки тому, що посилання в Excel за замовчуванням є відносними, при їх копіюванні відбувається зрушення значень, що нам і потрібно. Саму процедуру копіювання можна зробити за допомогою маркера заповнення.
Отже, ставимо курсор в нижню праву область елемента з формулою. Після цього курсор повинен перетворитися в маркер заповнення у вигляді чорного хрестика. Виконуємо затиск лівої кнопки миші і тягнемо курсор до самого низу стовпця.
Всі дані з аналогічного стовпчика на Листі 2 були підтягнуті в таблицю на Листі 1. При зміні даних на Листі 2 вони автоматично будуть змінюватися і на першому.

Спосіб 2: використання зв'язки операторів ІНДЕКС - ПОИСКПОЗ

Але що робити, якщо перелік співробітників в табличних масивах розташований не в однаковому порядку? У цьому випадку, як говорилося раніше, одним з варіантів є установка зв'язку між кожною з тих осередків, які слід пов'язати, вручну. Але це підійде хіба що для невеликих таблиць. Для масивних діапазонів подібний варіант в кращому випадку відніме дуже багато часу на реалізацію, а в гіршому - на практиці взагалі буде неможливий. Але вирішити цю проблему можна за допомогою зв'язки операторів ІНДЕКС - ПОИСКПОЗ. Подивимося, як це можна здійснити, зв'язавши дані в табличних діапазонах, про які йшла мова в попередньому способі.

Виділяємо перший елемент стовпця «Ставка». Переходимо в Майстер функцій, клікнувши по піктограмі «Вставити функцію».
У Майстрі функцій в групі «Посилання та масиви» знаходимо і виділяємо найменування «ІНДЕКС».
Даний оператор має дві форми: форму для роботи з масивами і кількість посилань. У нашому випадку потрібно перший варіант, тому в наступному віконці вибору форми, яке відкриється, вибираємо саме його і тиснемо на кнопку «OK».
Виконано запуск віконця аргументів оператора ІНДЕКС. Завдання зазначеної функції - висновок значення, що знаходиться в обраному діапазоні в рядку з зазначеним номером. Загальна формула оператора ІНДЕКС така:
ІНДЕКС (масив; номер_рядка;)

«Масив» - аргумент, що містить адресу діапазону, з якого ми будемо отримувати інформацію за номером зазначеного рядка.

«Номер рядка» - аргумент, який є номером цієї самої рядки. При цьому важливо знати, що номер рядка слід вказувати не щодо всього документа, а тільки щодо виділеного масиву.

«Номер стовпця» - аргумент, що носить необов'язковий характер. Для вирішення конкретно нашої задачі ми його використовувати не будемо, а тому описувати його суть окремо не потрібно.

Ставимо курсор в поле «Масив». Після цього переходимо на Лист 2 і, затиснувши ліву кнопку миші, виділяємо весь вміст стовпчика «Ставка».
Після того, як координати відобразилися в віконці оператора, ставимо курсор в полі «Номер рядка». Даний аргумент ми будемо виводити за допомогою оператора ПОИСКПОЗ. Тому натискаємо по трикутнику, який розташований зліва від рядка функцій. Відкривається перелік недавно використаних операторів. Якщо ви серед них знайдете найменування «ПОИСКПОЗ», то можете клікати по ньому. У зворотному випадку клікайте по самому останньому пункту переліку - «Інші функції ...».
Запускається стандартне вікно Майстра функцій. Переходимо в ньому в ту ж саму групу «Посилання та масиви». На цей раз в переліку вибираємо пункт «ПОИСКПОЗ». Виконуємо клацання по кнопці «OK».
Проводиться активація віконця аргументів оператора ПОИСКПОЗ. Зазначена функція призначена для того, щоб виводити номер значення в певному масиві по його найменуванню. Саме завдяки цій можливості ми обчислимо номер рядка певного значення для функції ІНДЕКС. Синтаксис ПОИСКПОЗ представлений так:
ПОИСКПОЗ (шукане_значення; просматріваемий_массів;)

«Шукане значення» - аргумент, який містить найменування або адреса комірки стороннього діапазону, в якій воно знаходиться. Саме позицію даного найменування в цільовому діапазоні і слід обчислити. У нашому випадку в ролі першого аргументу виступатимуть посилання на осередки на Листі 1, в яких розташовані імена співробітників.

«Проглядається масив» - аргумент, який представляє собою посилання на масив, в якому виконується пошук вказаного значення для визначення його позиції. У нас цю роль виконуватиме адреса стовпця «Ім'я» на Листі 2.

«Тип зіставлення» - аргумент, який є необов'язковим, але, на відміну від попереднього оператора, цей необов'язковий аргумент нам буде потрібен. Він вказує на те, як буде зіставляти оператор шукане значення з масивом. Цей аргумент може мати одне з трьох значень: -1; 0; 1. Для невпорядкованих масивів слід вибрати варіант «0». Саме цей варіант підійде для нашого випадку.

Отже, приступимо до заповнення полів вікна аргументів. Ставимо курсор в поле «Шукане значення», натискаємо по першій клітинці стовпця «Ім'я» на Листі 1.
Після того, як координати відобразилися, встановлюємо курсор в поле «проглядається масив» і переходимо по ярлику «Лист 2», який розміщений внизу вікна Excel над рядком стану. Затискаємо ліву кнопку миші і виділяємо курсором все комірок стовпчика «Ім'я».
Після того, як їх координати відобразилися в поле «проглядається масив», переходимо до поля «Тип зіставлення» і з клавіатури встановлюємо там число «0». Після цього знову повертаємося до поля «проглядається масив». Справа в тому, що ми будемо виконувати копіювання формули, як ми це робили в попередньому способі. Відбуватиметься зміщення адрес, але ось координати проглядається масиву нам потрібно закріпити. Він не повинен зміщуватися. Виділяємо координати курсором і тиснемо на функціональну клавішу F4. Як бачимо, перед координатами з'явився знак долара, що означає те, що посилання з відносною перетворилася в абсолютну. Потім тиснемо на кнопку «OK».
Результат виведений на екран в перший осередок шпальти «Ставка». Але перед тим, як проводити копіювання, нам потрібно закріпити ще одну область, а саме перший аргумент функції ІНДЕКС. Для цього виділяємо елемент колонки, який містить формулу, і рухаємось в рядок формул. Виділяємо перший аргумент оператора ІНДЕКС (B2: B7) і клацаємо по кнопці F4. Як бачимо, знак долара з'явився близько обраних координат. Клацаємо по клавіші Enter. В цілому формула прийняла такий вигляд:
ІНДЕКС (Аркуш2! $ B $ 2: $ B $ 7; ПОИСКПОЗ (Лист1! A4; Аркуш2! $ A $ 2: $ A $ 7; 0))
Тепер можна зробити копіювання за допомогою маркера заповнення. Викликаємо його тим же способом, про який ми говорили раніше, і простягаємо до кінця табличного діапазону.
Як бачимо, незважаючи на те, що порядок рядків у двох пов'язаних таблиць не збігається, проте, все значення підтягуються відповідно прізвищами працівників. Цього вдалося досягти завдяки застосуванню поєднання операторів ІНДЕКС-ПОИСКПОЗ.

Читайте також:

Функція ІНДЕКС в ЕкселеФункція ПОИСКПОЗ в Ексель

Спосіб 3: виконання математичних операцій з пов'язаними даними

Пряме зв'язування даних добре ще тим, що дозволяє не тільки виводити в одну з таблиць значення, які відображаються в інших табличних діапазонах, але і проводити з ними різні математичні операції (складання, розподіл, віднімання, множення і т.д.).

Подивимося, як це здійснюється на практиці. Зробимо так, що на Листі 3 виводитимуться загальні дані заробітної плати по підприємству без розбивки по співробітниках. Для цього ставки співробітників будуть підтягуватися з Ліста 2, підсумовуватися (за допомогою функції СУММ) і множитися на коефіцієнт за допомогою формули.

Виділяємо осередок, де буде виводитися підсумок розрахунку заробітної плати на Листі 3. Виробляємо клік по кнопці «Вставити функцію».
Слід запуск вікна Майстра функцій. Переходимо до групи «Математичні» і вибираємо там найменування «СУМ». Далі тиснемо на кнопку «OK».
Проводиться переміщення в вікно аргументів функції СУММ, яка призначена для розрахунку суми обраних чисел. Вона має нижче вказаному синтаксис:
СУМ (число1; число2; ...)

Поля в вікні відповідають аргументам зазначеної функції. Хоча їх число може досягати 255 штук, але для нашої мети досить буде всього одного. Ставимо курсор в поле «Число1». Кількома по ярлику «Лист 2» над рядком стану.
Після того, як ми перемістилися в потрібний розділ книги, виділяємо стовпець, який слід підсумувати. Робимо це курсором, затиснувши ліву кнопку миші. Як бачимо, координати виділеної області тут же відображаються в поле вікна аргументів. Потім клацаємо по кнопці «OK».
Після цього ми автоматично переміщаємося на Лист 1. Як бачимо, загальна сума розміру ставок працівників вже відображається у відповідному елементі.
Але це ще не все. Як ми пам'ятаємо, зарплата обчислюється шляхом множення величини ставки на коефіцієнт. Тому знову виділяємо клітинку, в якій знаходиться підсумовувана величина. Після цього переходимо до рядка формул. Дописуємо до наявної в ній формулі знак множення (*), а потім клацаємо по елементу, в якому розташовується показник коефіцієнта. Для виконання обчислення клацаємо по клавіші Enter на клавіатурі. Як бачимо, програма розрахувала загальну заробітну плату по підприємству.
Повертаємося на Лист 2 і змінюємо розмір ставки будь-якого працівника.
Після цього знову переміщаємося на сторінку із загальною сумою. Як бачимо, через зміни в пов'язаної таблиці результат загальної заробітної плати був автоматично перерахований.

Спосіб 4: спеціальна вставка

Зв'язати табличні масиви в Excel можна також за допомогою спеціальної вставки.

Виділяємо значення, які потрібно буде «затягнути» в іншу таблицю. У нашому випадку це діапазон стовпця «Ставка» на Листі 2. Кількома по виділеному фрагменту правою кнопкою миші. У списку вибираємо пункт «Копіювати». Альтернативною комбінацією є поєднання клавіш Ctrl + C. Після цього переміщаємося на Лист 1.
Перемістившись в потрібну нам область книги, виділяємо комірки, в які потрібно буде підтягувати значення. У нашому випадку це стовпець «Ставка». Клацаємо по виділеному фрагменту правою кнопкою миші. У контекстному меню в блоці інструментів «Параметри вставки» клацаємо по піктограмі «Вставити зв'язок».
Існує також альтернативний варіант. Він, до речі, є єдиним для старіших версій Excel. У контекстному меню наводимо курсор на пункт «Спеціальна вставка». У відкритому додатковому меню вибираємо позицію з однойменною назвою.
Після цього відкривається вікно спеціальної вставки. Тиснемо на кнопку «Вставити зв'язок» в нижньому лівому кутку комірки.
Який би варіант ви не вибрали, значення з одного табличного масиву будуть вставлені в інший. При зміні даних в исходнике вони також автоматично будуть змінюватися і під вставленому діапазоні.

Урок: Спеціальна вставка в Ексель

Спосіб 5: зв'язок між таблицями в декількох книгах

Крім того, можна організувати зв'язок між табличними областями в різних книгах. При цьому використовується інструмент спеціальної вставки. Дії будуть абсолютно аналогічними тим, які ми розглядали в попередньому способі, за винятком того, що виробляти навігацію під час внесення формул доведеться не між областями однієї книги, а між файлами. Природно, що всі пов'язані книги при цьому повинні бути відкриті.

Виділяємо діапазон даних, який потрібно перенести в іншу книгу. Клацаємо по ньому правою кнопкою миші і вибираємо в меню позицію «Копіювати».
Потім переміщаємося до тієї книги, в яку ці дані потрібно буде вставити. Виділяємо потрібний діапазон. Натискаємо правою кнопкою миші. У контекстному меню в групі «Параметри вставки» вибираємо пункт «Вставити зв'язок».
Після цього значення будуть вставлені. При зміні даних у вихідній книзі табличний масив з робочою книги буде їх підтягувати автоматично. Причому зовсім не обов'язково, щоб для цього були відкриті обидві книги. Досить відкрити одну тільки робочу книгу, і вона автоматично підтягне дані з закритого пов'язаного документа, якщо в ньому раніше були проведені зміни.

Але потрібно відзначити, що в цьому випадку вставка буде проведена у вигляді незмінного масиву. При спробі змінити будь-який елемент зі вставленими даними буде спливати повідомлення, що інформує про неможливість зробити це.

Зміни в такому масиві, пов'язаному з іншого книгою, можна зробити тільки розірвавши зв'язок.

Розрив зв'язку між таблицями

Іноді потрібно розірвати зв'язок між табличними діапазонами. Причиною цього може бути, як вищеописаний випадок, коли потрібно змінити масив, вставлений з іншої книги, так і просто небажання користувача, щоб дані в одній таблиці автоматично оновлювалися з іншого.

Спосіб 1: розрив зв'язку між книгами

Розірвати зв'язок між книгами в усіх осередках можна, виконавши фактично одну операцію. При цьому дані в осередках залишаться, але вони вже будуть являти собою статичні Неоновлювані значення, які ніяк не залежать від інших документів.

У книзі, в якій підтягуються значення з інших файлів, переходимо у вкладку «Дані». Клацаємо по значку «Змінити зв'язку», який розташований на стрічці в блоці інструментів «Підключення». Потрібно відзначити, що якщо поточна книга не містить зв'язків з іншими файлами, то ця кнопка є неактивною.
Запускається вікно зміни зв'язків. Вибираємо зі списку пов'язаних книг (якщо їх декілька) той файл, з яким хочемо розірвати зв'язок. Клацаємо по кнопці «Розірвати зв'язок».
Відкривається інформаційне віконце, в якому знаходиться попередження про наслідки подальших дій. Якщо ви впевнені в тому, що збираєтеся робити, то тисніть на кнопку «Розірвати зв'язку».
Після цього всі посилання на вказаний файл в поточному документі будуть замінені на статичні значення.

Спосіб 2: вставка значень

Але вищеперелічений спосіб підходить тільки в тому випадку, якщо потрібно повністю розірвати всі зв'язки між двома книгами. Що ж робити, якщо потрібно роз'єднати пов'язані таблиці, що знаходяться в межах одного файлу? Зробити це можна, скопіювавши дані, а потім вставивши на те саме місце, як значення. До речі, цим же способом можна проводити розрив зв'язку між окремими діапазонами даних різних книг без розриву загальної зв'язку між файлами. Подивимося, як цей метод працює на практиці.

Виділяємо діапазон, в якому бажаємо видалити зв'язок з іншою таблицею. Клацаємо по ньому правою кнопкою миші. У меню, що розкрилося вибираємо пункт «Копіювати». Замість зазначених дій можна набрати альтернативну комбінацію гарячих клавіш Ctrl + C.
Далі, не знімаючи виділення з того ж фрагмента, знову натискаємо по ньому правою кнопкою миші. На цей раз в списку дій клацаємо по іконці «Значення», яка розміщена в групі інструментів «Параметри вставки».
Після цього всі посилання у виділеному діапазоні будуть замінені на статичні значення.

Як бачимо, в Excel є способи і інструменти, щоб зв'язати кілька таблиць між собою. При цьому, табличні дані можуть знаходитися на інших аркушах і навіть в різних книгах. При необхідності цей зв'язок можна легко розірвати.

Ми раді, що змогли допомогти Вам у вирішенні проблеми.

Задайте своє питання в коментарях, детально розписавши суть проблеми. Наші фахівці постараються відповісти максимально швидко.

Зв'язки типу "багато-до-багатьох" використовуються між таблицями найчастіше. З їх допомогою можна дізнаватися важливі відомості, наприклад, з якими клієнтами зв'язувалися ваші менеджери з продажу і які продукти входили в замовлення.

Зв'язок "багато-до-багатьох"Передбачає можливість зв'язку одного або декількох елементів з однієї таблиці з одним або декількома елементами з іншої таблиці. Приклади:

У таблиці "Замовлення" вказані замовлення, зроблені різними клієнтами з таблиці "Клієнти". Кожен клієнт міг зробити кілька замовлень.

У таблиці "Продукти" вказані товари, що продаються, кожен з яких може фігурувати в декількох замовленнях з таблиці "Замовлення".

Кожен продукт може входити в одне замовлення як в одному, так і в декількох примірниках.

Наприклад, в замовлення Аріни Іванової № 1012 можуть входити продукти № 12 і 15, а також п'ять продуктів № 30.

Створення зв'язку "багато-до-багатьох"

Зв'язки "багато-до-багатьох" створюються не так, як зв'язку "один-до-одного" і "один-ко-многим", коли потрібно просто зв'язати потрібні поля за допомогою лінії. Щоб створити зв'язок "багато-до-багатьох", необхідно додати нову таблицю, за допомогою якої будуть зв'язуватися дві інші. Нова таблиця буде називатися проміжної (або сполучною).

У розглянутому раніше прикладі створювалася таблиця "Відомості про замовлення" із записами, в яких для кожного товару в потрібному порядку вказувалися номер замовлення з таблиці "Замовлення" і код продукту з таблиці "Продукти". Первинний ключ для цієї таблиці створювався шляхом об'єднання ключів з двох інших таблиць.

Нижче розглянемо приклад, коли в замовлення Аріни Іванової № 1012 входять продукти № 12, 15 і 30. Це означає, що записи в таблиці "Відомості про замовлення" виглядають таким чином:

Створення проміжної таблиці

Створення полів в проміжній таблиці

Стовпець "Код" автоматично додається в Access в якості першого. Змініть ім'я цього поля на ідентифікатор вашої першої таблиці в зв'язку "багато-до-багатьох". Наприклад, якщо перша таблиця називається "Замовлення", поле "Код" в ній перейменовано в "Номер замовлення", і його первинний ключ - число, змініть ім'я поля "Код" в новій таблиці на "Номер замовлення", а в якості типу даних Оберіть числовий.

В режимі таблиці виберіть заголовок стовпця код і введіть нове ім'я поля.

Виберіть перейменоване поле.

на вкладці поля в списку Тип даних виберіть тип, як у відповідному полі вихідної таблиці, наприклад числовий або короткий текст.

клацніть напис Клацніть для додавання і виберіть тип даних, відповідний первинному ключу в другій таблиці. У заголовку стовпця введіть ім'я поля первинного ключа з другої таблиці, наприклад «Код продукту».

Якщо вам потрібно відстежувати іншу інформацію щодо цих записів, наприклад кількість товарів, створіть додаткові поля.

Об'єднання полів для створення первинного ключа

Тепер, коли у вас є поля з ідентифікаторами двох таблиць, між якими ви хочете створити зв'язок, в проміжній таблиці слід створити первинний ключ на основі цих ідентифікаторів.

Ці три таблиць для створення зв'язку "багато-до-багатьох"

Щоб завершити створення зв'язку "багато-до-багатьох", створіть зв'язок "один-ко-многим" між полем первинного ключа в кожній таблиці і відповідним полем у проміжній таблиці. Інструкції див. У статті Початок роботи зі зв'язками між таблицями.

Після цього зв'язку повинні виглядати наступним чином:

Вам потрібні додаткові можливості?

Відносини «багато до багатьох» застосовуються, коли один елемент таблиці А може бути пов'язаний з безліччю елементів таблиці Б і навпаки. Наприклад, товар може входити в безліч замовлень, а замовлення може містити безліч товарів.

У цьому відео ми розглянемо, як зробити з бази даних просту систему відстеження замовлень з окремими таблицями для товарів і замовлень.

Ми дізнаємося, що таке єднальна таблиця і складовою ключ, і використовуємо їх з іншими таблицями, щоб створити відношення «багато до багатьох».

Щоб створити відношення «багато до багатьох», спочатку потрібно створити ще одну таблицю.

Ця таблиця називається проміжною. У ній будуть зберігатися дані з інших таблиць, які нам потрібно зв'язати в відношенні.

Проміжна таблиця також називається сполучною.

Тепер необхідно створити пару відносин «один до багатьох» між сполучною таблицею і таблицями, які вона буде пов'язувати.

Для цього потрібно вказати обидва цих поля як первинний ключ сполучною таблиці. Вони стають складовим ключем.

Так як єднальна таблиця створюється в режимі конструктора, Відкриваємо вкладку створення і натискаємо кнопку конструктор таблиць. Ось наша нова, порожня таблиця.

Тепер відкриємо таблиці «Товари» і «Замовлення».

Погляньмо на первинні ключі обох таблиць. У таблиці «Товари» використовується поле «ВД товару» з типом даних Лічильник.

Первинний ключ для таблиці «Замовлення» називається «ВД замовлення». Він також має тип даних Лічильник.

Тепер повертаємося до сполучною таблиці, вводимо ім'я поля «ВД товару», вибираємо тип даних числовий, Вводимо ім'я «ВД замовлення» і знову вибираємо тип даних числовий.

Тепер виділимо обидва поля. Для цього наведіть покажчик на сірий прямокутник поруч з будь-яким з цих полів і перетягніть його.

Виділивши обидва поля, вибираємо на стрічці пункт ключове поле. Створюється складовою ключ для сполучною таблиці.

Обидва поля повинні бути включені в складовою ключ, щоб у кожного замовлення і всього, що в нього входить, були унікальні ідентифікатори.

Тепер ми можемо створити відносини. Для початку закрийте всі відкриті таблиці. Оберіть Робота з базами даних > схема даних.

на вкладці конструктор виберіть елемент Додавання таблиці. Додайте таблиці «Відомості про замовлення», «Замовлення» і «Товари». Закрийте діалогове вікно.

Додайте таблиці так, щоб єднальна таблиця «Відомості про замовлення» була посередині. Перетягніть поле «ВД замовлення» з таблиці «Замовлення» в сполучну таблицю.

У діалоговому вікні зміна зв'язків встановіть прапорець і натисніть кнопку створити, Щоб створити відношення.

Виконайте ті ж дії з полем «ВД товару». встановіть прапорець Забезпечення цілісності даних, натисніть кнопку створити - і все готово.

Отже, ви створили в базі даних Access зв'язок «багато до багатьох» між існуючими таблицями за допомогою сполучною таблиці і складеного ключа.

Маючи два телефонні апарати, моток двожильного дроти достатньої довжини і невелика кількість радіодеталей можна організувати власну телефонну лінію на дві особи. Наприклад влаштувати зв'язок між двома сусідніми квартирами, перекинувши провід між вікнами, або між сусідніми дачними будиночками, між будинком і альтанкою. Перш за все згадаємо зі шкільного курсу фізики як працює телефон. У найпростішому випадку це два телефонних капсуля, пов'язаних між собою двухпроводной лінією.

Коли говорять перед одним капсулем, його магнітна мембрана рухається від звукових хвиль і наводить в котушці деяке змінне напруга, яке по дротах надходить до котушки другого капсуля і приводить в рух його мембрану. Виходить, що мембрана другого капсуля рухається таким же чином як і першого, створюючи звукові хвилі, які можна почути. Але теоретично такий зв'язок є малоефективною, занадто великі втрати в проводах і низький ККД капсуля.

Тому реальний телефонний апарат має вугільний мікрофон, опір якого змінюється в такт з промовою. Крім того на лінію надходить деяка постійна напруга. В результаті виходить так, що вугільний мікрофон, підключений до лінії змінюючи свій опір в такт з промовою викликає зміна сили струму в лінії.

Таким чином в лінії виходить змінний струм, який змінюється в такт з промовою. Цим струмом харчується капсуль іншого телефонного апарату і випромінює звукові хвилі, досить виразні і гучні.

Принципова схема найпростішої телефонної лінії показана на малюнку. Використовуються два електронних телефонних апарату - телефони-трубки з кнопковими номеронабирачами, але можна взяти і прості
дискові апарати. Вони пов'язані між собою двома проводами. На ці дроти через опір R1 поступає постійна напруга величиною 60-80В. Коли трубки обох ТА повішені між цими проводами є напруга 60-80В, оскільки в такому стані внутрішні опору ТА дуже високі.

Припустимо, підняли трубку ТА2, при цьому його важільний перемикач підключає до лінії його розмовний вузол і опір ТА2 різко зменшується, на стільки, що напруга в лінії падає до 8-10В (напруга падає тому, що з резистора R1 і опору розмовного вузла ТА виходить дільник напруги, а лінія виявляється підключеної якраз через цей дільник).

Якщо почати говорити перед мікрофоном ТА2, опір його розмовного вузла починає змінюватися в такт з промовою, а значить зміниться і загальний опір ТА2. При цьому R1 залишається незмінним і в лінії напруга змінюється в такт з промовою. Телефонний апарат ТА 1 також підключений до цієї лінії і якщо підняти його трубку можна почути те, що кажуть перед мікрофоном ТА2. Якщо абонент ТА 1 відповість, його голос почує абонент ТА2. Таким чином можна розмовляти один з одним.

Для виклику абонента телефонний апарат має дзвінок або зумер, він підключений до лінії завжди, коли трубка ТА повішена і реагує на будь-який значний перепад напруги в лінії. Якщо підняти трубку ТА2 то дзвінок ТА 1 "дзвякне" один раз, тому що в лінії був один перепад напруги, повісите трубку - ТА 1 "дзвякне" ще раз.

У складі ТА є номеронабиратель, кнопковий або дисковий, неважливо, в будь-якому випадку він служить для створення в лінії перепадів напруги, ці перепади приймає телефонна станція, відраховує їх і таким чином визначає номер абонента. У нашому випадку телефонної станції немає, але номеронабиратель можна використовувати для виклику абонента. Якщо ви набираєте цифру "9" ТА створює в лінії дев'ять перепадів напруги, а це значить що другий ТА "дзвякне" вже не один раз а дев'ять, якщо цифру "0" - десять разів.

Таким чином щоб вам з телефону ТА2 зателефонувати своєму другові на ТА 1 вам потрібно підняти трубку ТА2 і кілька разів поспіль набрати "О" (або іншу цифру). В результаті ТА 1 задзвонить, дзвінок почує ваш друг, і піднявши трубку зможе з вами розмовляти. Точно також можна викликати ТА2 з апарату ТА 1.

Постійна напруга для харчування лінії виходить з мережевої напруги 220В за допомогою трансформатора Т1 і випрямляча на діодах VD1-VD4. Пульсації випрямленої напруги згладжуються конденсатором С1. Трансформатор Т1 можна зробити з малогабаритного трансформатора від мережевого джерела для живлення іграшок або переносної апаратури. Потрібно розібрати корпус джерела і підключивши трансформатор за допомогою подовжувача до мережі виміряти напругу на його вторинній обмотці. Припустимо вольтметр показав 6В.

Нам потрібно 50 В. Тепер відключаємо трансформатор від мережі, розбираємо його пластинчастий сердечник і розмотує вторинну обмотку (НЕ мережеву), акуратно, відраховуючи її витки. Припустимо нарахували 120 витків, цей при тому, що вихідна напруга була 6В. Тепер розраховуємо скільки у даного трансформатора на 1В доводиться витків: 120/6 \u003d 20, разом 20 витків. Нам потрібно 50 В, значить вторинна обмотка повинна містити 20x50 \u003d 1000 витків. Беремо тонкий намотувальний провід, наприклад ПЕВ 0,06 або ПЗВ 0,09 і обережно наметовому цим проводом 1000 витків замість старої вторинної намотування.

Потім збираємо сердечник трансформатора. Можна взяти готовий трансформатор, який виробляє як мінімум 36В і як максимум 50В.
Діоди Д226 можна замінити на будь-які випрямні, наприклад на Д7 або на сучасні КД102, КД103, КД105, КД226, КД209. Конденсатор С1 на напругу не менше 100В і ємність не менше 160 мкФ. Резистор R1 опором від 1 кОм до 1,8 кОм на потужність не менше 2 Вт. Тумблер S1 будь-якого типу.

Конструктивно джерело живлення може бути підключений до лінії в будь-якому місці, біля ТА2, біля TA1 або взагалі посередині. Наприклад якщо ТА2 знаходиться в електрифікована приміщенні, а ТА 1 в садовій альтанці, зрозуміло, що джерело підключається біля TA2.

При монтажі не переплутайте полярність підключення конденсатора С1 і діодів. У діодів Д226 на корпусі нанесено графічне зображення діода, уздовж корпусу, так щоб можна було зрозуміти призначення висновків.