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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Робота з базами даних - процес, що потребує певних навичок та розуміння деяких нюансів. Розроблена компанією Microsoft програма Access дає користувачам можливість швидко створювати та редагувати БД. Один із найважливіших моментів, з яким обов'язково потрібно розібратися, при створенні БД – зв'язування її елементів. У цій статті розберемося з тим, як створити або видалити зв'язок між таблицями в Access. Давайте почнемо. Поїхали!

Перш ніж ми перейдемо до зв'язування, необхідно створити звані ключові поля. Що це таке? Ключове поле – це поле, яке визначає кожний запис. Тепер перейдемо безпосередньо до його створення. Відкривши потрібну таблицю, клацніть правою кнопкою миші по вкладці та виберіть пункт «Конструктор». Потім додайте нове поле. Наприклад, якщо у вас є список співробітників, то ключовим варто зробити "код співробітника", вказавши тип даних "лічильник" або "числовий". Щоб зробити його ключовим, натисніть кнопку «Ключове поле» на панелі інструментів Microsoft Access. Далі, необхідно зробити те саме для всіх інших таблиць.

Тепер уже можна пов'язувати наші таблиці. Щоб зробити це, перейдіть на вкладку «Робота з базами даних» і в розділі «Відносини» стрічки інструментів виберіть «Схема даних». Перед вами відкриється вікно, в якому потрібно вибрати всі таблиці. По черзі натисніть на кожну з них. Закінчивши, вийдіть із цього вікна. Тепер слід визначитися з тим, як саме пов'язувати. Наприклад, є дві таблиці: «Бренд» та «Одяг», між якими необхідно зв'язати між собою. Поле "Код бренду" має бути і там, і там. Якщо воно відсутнє – створіть його. При цьому для записів категорії "Бренд" воно буде ключовим (так званий первинний ключ), а для "Одяг" - звичайним (зовнішній ключ). Первинний ключ "Код бренду" перетягніть на зовнішній ключ "Код бренду". Після цього з'явиться діалогове вікно, в якому потрібно відзначити галочками пункти: "Забезпечення цілісності ...", "Каскадне оновлення пов'язаних полів" та "Каскадне видалення пов'язаних записів". Позначивши всі пункти, натисніть кнопку "Створити". З'явиться чорна лінія, що з'єднує ці рядки. Створений зв'язок має назву "один до багатьох". Тобто в наведеному прикладі це означатиме, що один бренд може випускати багато різного одягу.

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

Щоб видалити зв'язок, клацніть по кнопці «Схема даних» на панелі інструментів, потім клацніть по лінії, що її показує, і натисніть на клавіатурі клавішу delete.

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

Microsoft Access 2007

2.4. Microsoft Access 2007

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

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

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

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

Мал. 1.

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

Мал. 2.

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

Мал. 3.

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

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

Мал. 4.

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

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

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

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

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

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

Принципова схема найпростішої телефонної лінії показано малюнку. Використовуються два електронні телефонні апарати - телефони-трубки з кнопковими номеронабирачами, але можна взяти і прості
дискові апарати Вони пов'язані між собою двома проводами. На ці дроти через опір 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 = 20, разом 20 витків. Нам потрібно 50 В, отже, вторинна обмотка повинна містити 20x50 = 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 на корпусі нанесено графічне зображення діода, вздовж корпусу, щоб можна було зрозуміти призначення висновків.

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

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

Примітка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. Всі дані з аналогічного стовпця на аркуші 2 були підтягнуті в таблицю на аркуші 1. При зміні даних на аркуші 2 вони автоматично будуть змінюватися і на першому.

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

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

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

   ІНДЕКС(масив; номер_рядка;)

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

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

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

   Ставимо курсор у полі «Масив». Після цього переходимо на Аркуш 2 і, затиснувши ліву кнопку миші, виділяємо весь вміст стовпця «Ставка».

5. Після того, як координати відобразились у віконці оператора, ставимо курсор у полі «Номер рядка». Даний аргумент ми виводитимемо за допомогою оператора ПОШУКПОЗ. Тому клацаємо по трикутнику, який розташований ліворуч від рядка функцій. Відкривається перелік нещодавно використаних операторів. Якщо ви серед них знайдете найменування "ПОШУКПОЗ", то можете клікати по ньому. У протилежному випадку клацніть по останньому пункту переліку – «Інші функції…».
6. Запускається стандартне вікно Майстра функцій. Переходимо в ньому в ту саму групу «Посилання та масиви». На цей раз у переліку вибираємо пункт «ПОШУКПОЗ». Клацніть по кнопці «OK».
7. Проводиться активація віконця аргументів оператора ПОШУКПОЗ. Ця функція призначена для того, щоб виводити номер значення в певному масиві за його найменуванням. Саме завдяки даній можливості ми обчислимо номер рядка певного значення функції ІНДЕКС. Синтаксис ПОШУКПОЗ представлений так:

   ПОШУКПОЗ(шукане_значення;проглядається_масив;)

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

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

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

   Отже, приступимо до заповнення полів вікна аргументів. Ставимо курсор у полі «Шукане значення», клацаємо по першому осередку стовпця «Ім'я» на Листі 1.

8. Після того, як координати відобразилися, встановлюємо курсор у полі «Масив, що переглядається» і переходимо по ярлику «Лист 2», який розміщений внизу вікна Excel над рядком стану. Затискаємо ліву кнопку миші та виділяємо курсором усі осередки стовпця «Ім'я».
9. Після того, як їх координати відобразилися в полі «Масив, що переглядається», переходимо до поля «Тип зіставлення» і з клавіатури встановлюємо там число «0». Після цього знову повертаємося до поля «Проглядається масив». Справа в тому, що ми будемо виконувати копіювання формули, як ми це робили у попередньому способі. Відбуватиметься зміщення адрес, але ось координати масиву нам потрібно закріпити. Він не повинен зміщуватися. Виділяємо координати курсором і натискаємо на функціональну клавішу F4. Як бачимо, перед координатами з'явився знак долара, що означає те, що посилання із відносної перетворилося на абсолютну. Потім натискаємо на кнопку «OK».
10. Результат виведений на екран у першу комірку стовпця «Ставка». Але перед тим, як робити копіювання, нам потрібно закріпити ще одну область, а саме перший аргумент функції ІНДЕКС. Для цього виділяємо елемент колонки, який містить формулу, та переміщуємось у рядок формул. Виділяємо перший аргумент оператора ІНДЕКС (B2: B7) та клацаємо по кнопці F4. Як бачимо, знак долара з'явився біля вибраних координат. Клацаємо по клавіші Enter. Загалом формула набула наступного вигляду:

    ІНДЕКС(Лист2!$B$2:$B$7;ПОШУКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

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

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

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

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

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

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

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

   СУМ(число1; число2; ...)

   Поля у вікні відповідають аргументам цієї функції. Хоча їх кількість може досягати 255 штук, але для нашої мети достатньо буде лише одного. Ставимо курсор у полі «Число1». Клацаємо по ярлику «Лист 2» над рядком стану.

4. Після того, як ми перемістилися в потрібний розділ книги, виділяємо стовпець, який слід підсумувати. Робимо це курсором, затиснувши ліву кнопку миші. Як бачимо, координати виділеної області відразу відображаються у полі вікна аргументів. Потім клацаємо по кнопці "OK".
5. Після цього ми автоматично переміщуємося на Аркуш 1. Як бачимо, загальна сума розміру ставок працівників вже відображається у відповідному елементі.
6. Але це ще не все. Як ми пам'ятаємо, зарплата обчислюється шляхом збільшення величини ставки на коефіцієнт. Тому знову виділяємо комірку, в якій знаходиться сумована величина. Після цього переходимо до рядка формул. Дописуємо до наявної формулі знак множення (*), а потім клацаємо по елементу, в якому розташовується показник коефіцієнта. Для виконання обчислення клацаємо по Enter на клавіатурі. Як бачимо, програма розрахувала загальну заробітну плату на підприємстві.
7. Повертаємося на Лист 2 та змінюємо розмір ставки будь-якого працівника.
8. Після цього знову переміщуємося на сторінку із загальною сумою. Як бачимо, через зміни у пов'язаній таблиці результат загальної заробітної плати був автоматично перерахований.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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