Как да свържете графики в excel. Работна книга на Excel

Използвали ли сте функцията VLOOKUP за преместване на данни от колони от една таблица в друга? Тъй като Excel вече има вграден модел на данни, функцията VLOOKUP е отхвърлена. Можете да създадете връзка между две таблици въз основа на съвпадащи данни в тях. След това можете да създадете Power View листове или обобщени таблици и други отчети с полета от всяка таблица, дори ако идват от различни източници. Например, ако имате данни за продажбите на клиенти, може да искате да импортирате и свържете, за да анализирате тенденциите в продажбите по година и месец.

Всички таблици в работната книга са изброени в списъците с полета на обобщена таблица и Power View.

При импортиране на свързани таблици от релационна база данни данни в екселчесто може да създаде тези релации в модел на данни, който се генерира във фонов режим. В други случаи трябва да създадете асоциации ръчно.

Ако отборът Връзкане е наличен, тогава работната книга съдържа само една таблица.

В прозореца Управление на взаимоотношениятаНатисни бутона Създавайте.

В прозореца Създайте връзкащракнете върху стрелката до полето Таблицаи изберете таблица от падащия списък. В релация "един към много" тази таблица трябва да бъде многочленна част. В примера за разузнаване на клиента и времето първо трябва да изберете таблицата с продажби на клиенти, тъй като е вероятно да има много продажби всеки ден.

За елемент Колона (чужда)изберете колоната, която съдържа данните, свързани с елемента. Например, ако има колона с дата и в двете таблици, трябва да изберете тази колона сега.

В полето Свързана таблицаизберете таблица, която съдържа поне една колона с данни, която е свързана с таблицата, избрана в полето Таблица.

В полето Свързана колона ( първичен ключ) изберете колоната, съдържаща уникални ценности, които съответстват на стойностите в колоната, избрана в полето Колона.

Щракнете върху бутона Добре.

Научете повече за връзките между таблиците в Excel

Бележки за взаимоотношения

Пример. Свързване на разузнавателни данни за време с данни за полети на авиокомпания

Можете да научите за връзките между двете таблици и времевата интелигентност с безплатни данни на Microsoft Azure Marketplace. Някои от тези набори от данни са много големи и изискват бърза връзкакъм Интернет.

Вече можете да разделяте закъсненията на пристигане по година и месец, както и други стойности в календара.

Съвет:По подразбиране месеците са изброени по азбучен ред. С добавката Power Pivot можете да промените реда на сортиране, така че да се показват в хронологичен ред.

Таблица BasicCalendarUSтрябва да е отворен в прозореца на Power Pivot.

В главната таблица щракнете Сортиране по колона.

В полето "Сортиране" изберете MonthInCalendar.

В полето „До“ изберете MonthOfYear.

Обобщената таблица вече сортира всяка комбинация „месец и година“ (октомври 2011 г., ноември 2011 г.) по номера на месеца в годината (10, 11). Промяната на реда на сортиране е лесна, тъй като каналът поток от датипредоставя всички необходими колони, за да работи този скрипт. Ако използвате различна таблица за времева интелигентност, вашите стъпки ще бъдат различни.

„Може да са необходими връзки между таблици“

Тъй като полетата се добавят към осева таблицаще бъдете уведомени, че е необходима връзка между таблиците, за да се работи с полетата, избрани в обобщената таблица.

Докато Excel може да ви каже кога е необходима връзка, той не може да ви каже кои таблици и колони да използвате или дали е възможна връзка между таблици. За да получите отговори на вашите въпроси, опитайте следното.

Стъпка 1. Решете кои таблици да включите в релацията

Ако вашият модел съдържа само няколко таблици, е ясно кои да използвате. Но за по-големите модели може да се нуждаете от помощ. Един от начините е да използвате изгледа на диаграма в добавката Power Pivot. Chart View предоставя визуализация на всички таблици в модела на данни. С него можете бързо да определите кои маси са отделени от останалата част от модела.

Забележка:Можете да създадете двусмислени релации, които са невалидни, когато се използват в отчет с обобщена таблица или Power View. Всичките ви таблици може да са свързани по някакъв начин с други таблици в модела, но когато се опитате да обедините полета от различни таблици, ще получите съобщението „Може да са необходими връзки между таблици“. Повечето вероятна причинае, че сте срещнали връзка много към много. Ако следвате верига от релации между таблици, които се свързват с таблиците, от които се нуждаете, вероятно ще откриете, че има две или повече релации "един към много" между таблиците. Няма лесно решение, което да работи във всяка ситуация, но можете да опитате да създадете изчисляеми колони, за да консолидирате колоните, които искате да използвате в една таблица.

Стъпка 2: Намерете колони, които могат да се използват за създаване на път от една таблица към друга

След като определите коя таблица не е свързана с останалата част от модела, прегледайте колоните в тази таблица, за да определите дали друга колона на друго място в модела съдържа съвпадащи стойности.

Да предположим, че имате модел, който съдържа продажби на продукти по територия, и впоследствие импортирате демографски данни, за да видите дали има връзка между продажбите и демографските тенденции във всяка територия. Тъй като демографските данни идват от различни източници, техните таблици първоначално са изолирани от останалата част от модела. За да интегрирате демографските данни с останалата част от вашия модел, ще трябва да намерите колона в една от демографските таблици, която съответства на тази, която вече използвате. Например, ако демографските данни са организирани по региони и вашите данни за продажбите дефинират зона за продажби, тогава можете да свържете двата набора от данни, като потърсите общи колони като държава, пощенски код или регион, за да предоставите справка.

В допълнение към съвпадащите стойности има няколко допълнителни изисквания за създаване на асоциации.

Ако имаме нужда Автоматично прехвърляне на данни от една електронна таблица на Excel в друга, трябва да свържете тези таблици. Има няколко начина за свързване на данни от две таблици в Excel. еднопосочен - с помощта на формула, вижте статията "Как да свържете таблици в Excel".
Ето, нека помислим как да свържете две таблици в различни работни книги на Excel, чрез установяване на релации между таблици. Когато всичко е настроено, достатъчно е да отворите една таблица, така че данните да се прехвърлят към тази таблица от друга таблица. Не е необходимо да отваряте всички книги, за да работи връзката. Ще работи с една отворена работна книга.
И така, имаме две работни книги на Excel - Book1 и Book2. Такава таблица има в книга 1, вече е попълнена. Маса 1.внимание!
Ако таблицата има обединени клетки, тогава връзката няма да работи в тях или ще се обърка.
В книга 2 правим същата таблица, но не я попълваме.

Друга функция "Вмъкване на връзка" може да се извика така. Ние също копираме, но в контекстното меню кликваме върху функцията " Специална вложка". В диалоговия прозорец, който се показва, щракнете върху бутона „Вмъкване на връзка“.

Ако един работен лист използва данни от друг лист, тогава тези листове се считат за свързани. С свързването можете да съберете стойности на клетки от няколко различни таблици на един и същи работен лист.

Промяна на съдържанието на клетка на същия лист или таблица ( източник) на работна книга променя свързаните с нея клетки в листове или таблици ( приемници). Този принцип разграничава свързването на листове от простото копиране на съдържанието на клетки от един лист в друг.

В зависимост от техниката на изпълнение, обвързването е "направо"и чрез командване СПЕЦИАЛНА ВЛОЖКА.

Метод 1 - "Директно свързване на клетки"

Директно свързване sheets се използва директно при въвеждане на формула в клетка, когато препратка към клетка от друг лист се използва като един от елементите на формулата. Например, ако клетка B4 на таблица на worksheet2 съдържа формула, която използва връзка към клетка A4 на друг работен лист (например Sheet1) и двата листа са заредени с данни, тогава такова свързване на листове се нарича „директно“.

Срок "директно" обвързванеозначава, че самият потребителдиректно при въвеждане на формулата показваиме на листаИ абсолютен адрес на клетка, разделени с удивителен знак "!".

Примери за формули: = C5*Sheet1! A4

Лист3! B2*100%

Лист1! A1- Лист2! A1

Забележка.

За да посочите връзка към клетки и листове, намиращи се в незаредени (неотворени) работни книги, във формулата трябва да посочите без интервали пълен пътместоположение на файла. Пътят е посочен в единични кавички, където са посочени името на диска, директорията (папката), името на работната книга (името на файла) в квадратни скоби и името на листа, към който отива връзката.

Метод 2 - Свързване на клетки чрез командата "Специално поставяне".

Свързване чрез команда СПЕЦИАЛНА ВЛОЖКАизпълнява се, ако някоя клетка от таблица на един работен лист трябва да съдържа стойността на клетка от друг работен лист.

За отразяване в клетка C4 на листа Ценастойността на клетка H4 на оригиналния лист Покупка, трябва да поставите курсора върху клетка H4 на изходния лист и да изпълните командата Редактиране–Копиране. На листа Ценапоставете курсора върху клетка C4, която трябва да бъде свързана с оригиналната, и изпълнете командата Редактиране - Специално поставяне - Поставяне на връзка(виж фиг. 8). След това върху листа Ценаще се появи индикация за клетката на оригиналния лист Покупка, Например: = Покупка!$N$4

С това обвързване EXCEL автоматично използва абсолютния адрес на клетка, тъй като относителен референтен адрес може да доведе до грешки, особено при достъп до незаредени файлове (работни книги).

Упражнение.Свържете клетки C4, C5, C6, C7, C8 в таблицата Разходи за покупкана лист Ценасъс съответните клетки на листа Покупка, използвайки различни методи за свързване на клетки (фиг. 8).

Ориз. 8 Свързване на клетки от различни работни листове

! Когато свързвате клетки, определете кои клетки са изходните клетки.

! За една свързана таблица клетки от различни таблици на различни работни листове или на текущия лист могат да бъдат изходни клетки.

Задачи за самостоятелна работа.

Преди да изпълните независима задача, помислете за примера на листови таблици Ценасвързващи клетки.

Например:

на лист Ценана масата Разходи за покупкаклетките A4:A8 са свързани с клетките на таблицата Количество закупени продуктина лист Покупка;

клетки B4:B8 са начални, т.к. съдържат първоначална информация за цените на закупените стоки;

клетки C4:C8 са свързани с клетки H4:H8 в работния лист Покупка;

клетки D4:D8 съдържат формули за изчисляване на сумата, изразходвана за закупените стоки и се отнасят до клетките на собствената им таблица (например формулата в клетка D4 изглежда = B4 * C4, което означава умножаване на цената на стоките по нейната количество);

клетка D9 е сумата от клетки D4:D8;

във втората таблица Калкулация на ценатана същия лист клетки A14:A18 са свързани подобно на клауза 1;

клетки B14:B18 са свързани с оригиналните клетки на текущия лист B4:B8;

клетки C4:C8 са начални, т.к съдържа първоначална информация за надценката на салона за закупените стоки;

клетките D14:D18 съдържат формули за изчисляване на продажната цена на продукт и се отнасят до клетките на собствената си таблица (например формулата в клетка D14 изглежда като = B14 * C14 + B14, което означава умножаване на покупната цена по набора процент надценка, който дава сумата на надценката, която трябва да се добави към покупната цена);

След като извършите всички операции с тези таблици, проверете тяхната "работоспособност".

промянаИме на продукта - Диванв клетка A4 на лист Покупкана друг - напр Диван.

промянаколичество закупени стоки Диванпрез юни (в клетка G4 на работния лист Покупкавъведете числото 11).

промянапокупната цена на дивана в клетка B4 на листа Ценаза друг - 2500,00 рубли.

промянапроцент от маржа на Sofa в клетка C14 на листа Ценаот 50% до 32%.

Проверете , има ли промени в свързани таблици или не?

Забележка, кои клетки от различни таблици са засегнати от промените.

внимание! При свързване на клетки чрез PASTE SPECIAL... копирането в съседни клетки става проблематично поради абсолютното адресиране на клетките.

Упражнение 1.Свържете клетките на останалите таблици на работната книга, като използвате различни методи.

Инструкция.В таблиците за изчисляване на приходите и приходите за 1 тримесечие се използва формулата за сумиране на изходните клетки само за 1 тримесечие.

Задача 2.Създавайте върху листове приходиИ доходитаблици за 2-ро тримесечие. Свържете тези таблици със съответните изходни данни.

Инструкция.

Задача 3.Начертайте кръгова диаграма върху лист доходии анализира разпределението на доходите по видове продукти.

Задача 4.Добавете работен лист в края на работната книга Консолидиран отчет.Създайте върху него обобщена таблица, която отразява броя на покупките и продажбите, маржа, покупните и продажните цени, приходите от продажби за 1-во тримесечие и за 2-ро тримесечие по имена на продукти. Свържете тази таблица със съответните изходни данни в други работни листове.

Инструкция.В таблиците за изчисляване на приходите и приходите за второто тримесечие използвайте изходните клетки само за 2 тримесечия.

Информация в електронна таблица Microsoft Excelможе да се комбинира с данните, извлечени от чертежа.

Чрез създаване на имейл връзка електронна таблицаможете да включите информация от целия лист или само част от него, т.е. именуван диапазон. В диалоговия прозорец Връзка към външни данни можете да направите следното:

• Задайте връзката към данни, като изберете XLS файла, към който искате да свържете данните, извлечени от чертежа.
• Картирайте извлечените данни за чертеж към данните от електронната таблица.
• Изберете колоните в XLS файла, които искате да обедините с извлечените данни за чертеж.

Страницата за редактиране на данни предоставя достъп до диалогов прозорецВръзка към външни данни, където можете да свържете извлечените данни към цялата електронна таблица на Excel или към набор от редове и колони.

За да създадете връзка към електронна таблица, компютърът, извършващ извличането на данни, трябва да има Microsoft приложение Excel. Ако не е инсталиран Програма на Microsoft Excel или XLS файлне се съхранява локално на машината, където се извличат данните, връзката за данни не може да бъде установена.

След промяна на свързаната електронна таблица, като например добавяне на ред или колона, можете да актуализирате таблицата в чертежа, за да отразите промяната, като използвате командата REFRESH. По същия начин, ако промените таблица в чертеж, свързаната електронна таблица може да се актуализира с помощта на същата команда.

Забележка: Използвайте командата REFER, за да премахнете връзка с данни. Връзки с данни на палитрата външни връзки“ не може да бъде изтрит.

Относно свързването на данни и картографирането

Свързването на данни се състои в избиране на електронна таблица на Microsoft Excel за комбиниране на цялата или част от електронната таблица с извлечените данни за чертеж. За повече информация относно обвързването на данни вижте "Създаване на асоциация на таблица с външни данни".

Процесът на картографиране на данни създава връзка между два набора от данни: данните в колоната на извлечената таблица с данни за чертеж и данните в колоната на електронната таблица на Excel.

Следващият пример разглежда проста спецификация на материалите. От чертежа се извличат три колони с данни. Тези колони съдържат следните данни: Част №, РедакцияИ Количество. Две допълнителни колони с данни от електронната таблица могат да бъдат включени в спецификацията. Тези колони съдържат следните данни ЦенаИ Доставчик.

IN този примерелектронната таблица съдържа колона с номер на част, която има заглавка Каталожен номери съответства на колоната Номер на детайлав таблицата с извлечени данни. В диалоговия прозорец Връзка към външни данни изберете "подробен номер"като колона за изчертаване на данни и "Каталожен номер"като колона за външни данни. Тъй като има поне една съответстваща клетка с данни както в колоната с данни за чертежа, така и в колоната с външни данни, функцията за извличане на данни "знае" как са свързани двата източника.

Съвместимостта на данните може да се провери чрез щракване върху бутона Проверка на съответствието в диалоговия прозорец Връзка към външни данни. Ако има несъответствие, се показва предупредително съобщение, посочващо причината. В някои случаи правилното създаваненяма връзка между данните от чертежа и данните от електронните таблици.

Проверка на картографирани данни

Неуспешна проверка на съгласуваността на данните означава, че не може да бъде намерено валидно съвпадение между данните за чертежа и външните данни (в електронната таблица). Показват се следните съобщения за грешка.

Не е намерено съответствие между стойностите в данните на чертежа и външните данни.

• Ако проблемът продължава, външните данни не се обединяват. Сравнете данните от колоната на електронната таблица с данните от чертежа и се опитайте да намерите съвпадащи стойности. Например данните за чертежи и външните данни могат да бъдат зададени на различни значенияточност.

Колоната с данни във външни данни не съдържа уникални стойности.

• Колона с външни данни съдържа една или повече клетки с едни и същи данни. В тази ситуация не е възможно еднозначно картографиране на данните за чертежа към външните данни.

Първо и най-важно, свързването на таблици по общи характеристики не е типична функция за MS Excel. За такива задачи има специални програмитака наречените бази данни. Въпреки че е по-правилно да ги наричаме "системи за управление на бази данни" или СУБД. Те включват например MS Access, MS SQL сървър, Oracle и много други. В тези системи свързването на таблици чрез общи ключови полета е може би най-честата операция. И за неговото решение има мощни и подръчни инструменти. Те ви позволяват да организирате голямо разнообразие от видове връзки между таблици, да контролирате целостта на данните, да извършвате каскадно изтриване на записи и т.н. Въпреки това, за пълноценното използване на СУБД е необходимо известно обучение, познаване на езика на заявките и т.н. Ето защо в практиката на счетоводителя рядко се използва СУБД в чист вид и цялата обработка на данни обикновено се извършва в програма Excel. Що се отнася до MS Excel, тук възможностите за свързване на таблици са ограничени. По принцип тази връзка е за писане на формули, които се отнасят до различни листове или работни книги. Можете да създадете отпечатан отчет или обобщена таблица, която работи с множество бази данни. Но няма специални инструменти за пълноценна организация на връзките между набори от данни в Excel и не трябва да има.

Въпреки това, не всичко е толкова лошо ... Факт е, че в реалната работа всичкообикновено не са необходими опции за свързване на таблици към счетоводител. Да, и без каскадно изтриване на записи, той също ще се справи по някакъв начин. Но свързването на една голяма таблица със справочник е доста подходяща и практически приложима задача. И най-важното (!), Такава функция е напълно осъществима вMS Excel. И единственото нещо, което е необходимо за това, е няколко прости трика и система от хипервръзки.

Като пример ще използвам две таблици, чиято форма е показана на фиг. 1 и 2. Първата таблица (намира се на листа "Поръчки ”) се състои от четири полета: „Поръчка”, „Дата”, „Клиент”, „Сума " и докато празното поле "Коментирайте. ". Това е опростена версия на базата данни с поръчки. В полето "Коментирайте. » в крайна сметка ще поставим връзка към елемента директория. Ясно е, че в базата данниПоръчки » Един контрагент може да се появи многократно - зависи от броя на поръчките, направени от този клиент.

Втората таблица се нарича "клиенти ". Оставих само пет колони в него: „Име ” (наименование на предприятието), следвано от името на директора, юридически адрес, телефон и e-mail.

важно!В директория „Клиенти » един ред съдържа данни за едно предприятие. Тук не се допускат повторения, всеки клиент присъства в базата данни само веднъж.

И така, ще организираме връзка между масата "Поръчки“ и „Клиенти ". За да направите това, на полетоКоментирайте. » таблици « Поръчки » ще поставим хипервръзка, която ще сочи към определен ред от таблица «клиенти ". Алгоритъмът на хипервръзката ще бъде както следва. Първо трябва да анализира съдържанието на полето "НаимПредпр " В текуща линия маси"Поръчки ". С други думи, запомнете Име на фирматакойто е направил определена поръчка. След това връзката трябва да премине към листа "клиенти ". На този лист намерете ред, който описва параметрите на фирмата, направила поръчката. И накрая - задайте показалеца текуща клеткакъм съответния ред в директорията "клиенти ". Ето как изглежда най-общо.

Между другото, в терминологията на базата данни такава връзка се нарича "един към много " (гледано отстрани на масата "клиенти "). Тоест, един елемент от клиентския указател се отнася до няколко записа в базата данни за поръчки. В MS Excel няма специални инструменти за създаване на такава връзка. Трябва сами да ги изградим. За да направим това, въз основа на алгоритъма, имаме нужда от: вградена функция за създаване на хипервръзка (такава функция се нарича "ХИПЕРВРЪЗКА() ”) и функция за търсене на данни в таблица. Предлагам да използвате функцията "СЪВПАДА() ". Но това не е всичко. Хипервръзката трябва да бъде точния адресза прехода: име на файл, име на работен лист и клетката, която искате да направите активна. За да получим името на файла, листа и клетката, ще използваме функцията " CELL() ". Освен това, за да образуваме ред с адреса за прескачане, ще трябва да се обърнем към вградените функции за работа с текст: “ MID() "и" SEARCH() ". Това всъщност са всички основни инструменти. Можете да преминете към практическо изпълнение.

Нека първо направим няколко предварителни действия. Да започнем с функцията "ХИПЕРВРЪЗКА() “, което ще бъде ключпри организиране на връзки между таблици. Тя има два варианта. Първият е адресът, т.е. името на файла и работния лист, към които сочи връзката. Вторият параметър е адресът на конкретна клетка на този лист, където ще сочи хипервръзката. Ще имаме много хипервръзки. И всеки път, за да ги създадете, ще трябва да посочите името на файла и името на листа в параметрите на функцията. Това е дълго и неудобно. Следователно е по-добре да създадете променлива веднъж с името на листа и файла и след това да посочите нейната променлива във функцията "ХИПЕРВРЪЗКА() " колкото е необходимо. По отношение на MS Excel е удобно да се използва като такава променлива именуван диапазон от клетки. Ето откъде ще започнем.

1. Извикваме Excel, зареждаме документа и отиваме в менюто "Формули ". Лентата ще изглежда като на фиг. 3.

2. В групата " Определени имена» щракнете върху иконата «Мениджър на имена ". Прозорецът, показан на фиг. 4.

3. В този прозорец натиснете бутона "Създавайте ". Прозореца "Създаване на име “, показано на фиг. 5.

4. В този прозорец, в полето "Име:" въведете текста "Моят_списък". В полето Диапазон: » отпечатайте формулата «=MID(CELL("име на файл" ;Клиенти!$A$1);ТЪРСЕНЕ(" [ " ;CELL(" filename " ;Customers!$A$1));256)& " ! " » (фиг. 3).

5. В прозореца "Създаване на име" щракнете върху "OK".

6. В прозореца Name Manager щракнете върху OK.

Сега да видим какво всъщност направихме? Работната книга има нов именуван диапазон "Моят_списък".

важно! В нашия случай името на именувания диапазон трябва да бъде БЕЗ интервали. В противен случай формулите за адресиране няма да работят правилно.

Като всеки наименуван диапазон, той сочи към клетка или група от клетки в работна книга на Excel. Но в нашия случай адресът на тази група не е постоянен, а динамичен. Формира се от формулата, която въведохме в полето "Обхват: ". Накратко за работата на тази формула. Да започнем отвътре - с функцията " CELL()".

Изразът "CELL(" filename " ;Customers! $A$1) " се отнася за клетката "А1" на лист "Клиенти ” и връща за тази клетка своя пълен адрес , т.е. пътя до файла, името на файла и името на листа, където се намира тази клетка. Например моят документ с таблици се нарича " DeanReferencesExcel_.xls ". Съхранява се на диск D:" в папката "!Factor ". Тогава резултатът от формулата ще бъде:D:!Фактор[DeanReferencesExcel_.xls]Клиенти».

От тази линия трябва само да вземем имена на файловеИ лист А работна книга - буквата на устройството и името на папката няма да са необходими при създаване на хипервръзка. За да разрешим този проблем, ние използваме стандартните функцииExcel за работа с текст.

Можете да изрежете част от низ с функцията " MID() ". Но тя трябва да уточни: оригинален текст(имаме пълния път до клетката), начална позицияИ брой знациТрябва да бъде премахнат от оригиналния низ.

Началната позиция е лесна за определяне. За да направите това, като използвате функциятаТЪРСЕНЕ() ” намираме първото появяване на квадратната отваряща скоба (“[ “) в текста, където се съхранява пътят до клетката. Фрагментът на формулата, който изпълнява тази операция, изглежда така:ТЪРСЕНЕ(" [ " ;CELL(" име на файл " ; Клиенти!$A$1)) ”. За линията " D:!Фактор [DeanReferencesExcel_.xls]клиенти» тази формула ще върне « 12 ". Броят знаци, които трябва да бъдат изрязани от изходния текст, избрах максималния - " 256".

Остава да замените получените стойности във функцията " MID() ". Първият му параметър е текстът с адреса на клетката. Вторият параметър е номерът на началната позиция за отрязване на част от струната. Последният параметър е максимално допустимият брой знаци на ред. Формулата за тази операция ще бъде:=MID(CELL("име на файл" ;Клиенти!$A$1);ТЪРСЕНЕ

(" [ " ;CELL(" име на файл " ;Клиенти!$A$1));256) ". В нашия пример такава формула ще върне резултата във формата "[DeanReferenceExcel_.xls]Клиенти". Всъщност това е връзка към листа "клиенти » работна тетрадка « DeanReferencesExcel_.xls ". След това може да бъде адресът на клетката. Но засега един елемент липсва - между името на листа и адреса на клетката трябва да има Удивителен знак (« ! "). Можем да добавим този символ към формулата с помощта на операцията "& ", а в крайната версия изразът ще изглежда така:"=MID(CELL("име на файл" ;Клиенти!$A$1);ТЪРСЕНЕ(" [ " ;CELL(" име на файл " ; Клиенти!$A$1)); 256)&" ! " ". И резултатът от работата й ще бъде:».

съвет Внимателно копирайте фрагментите на формулата в отделни клетки на работния лист и тогава работата на всяка част от него ще бъде на една ръка разстояние.

Между другото, работата на формула в наименуван диапазон е лесна за проверка. Въведете израза "=Моят_лист ” и натиснете „Въведете ". Резултатът трябва да се появи в клетката:[DeanLinkExcel_.xls]Клиенти!».

важно! Моля, имайте предвид, че за правилна работаформулите за име на лист не трябва да съдържат интервали.

1. Отидете на листа "Поръчки ", качи се на клетката" E2".

2. Въведете формулата "=HYPERLINK(My_Sheet&ADDRESS(SEARCH(C2;Клиенти!$A:$A;0); 1);" > " ) ».

3. Копирайте тази формула на цялата височина на таблицата. Резултатът от нашата работа е показан на фиг. 6.

Да проверим какво имаме. На масата "Поръчки »щракнете с левия бутон, например върху клетката« E6 ". Този ред съдържа информация за поръчката с номер " 5 „от фирма „ПП „Коло““. След щракване Excel ще превключи на "клиенти ", а указателят на активната клетка ще бъде на адреса " A6 ". Именно в тази позиция на директорията е информацията за "ЧП „Коло”” (фиг. 7).

Нека разгледаме накратко алгоритъма на формулата. израз "=MATCH(C2;Клиенти! $A:$A;0)» намира клетка в колоната «А" на лист "Клиенти “, който съдържа името на контрагента от клетката „С2" лист "Заповеди ". Последният параметър на функцията " MATCH() "е равно на" 0 ". Това означава, че ще търси стойност според принципа прецизенмачове. Да се ​​върнем към нашия пример. Да предположим, че работим с низа " 6 » таблици « Поръчки » (фиг. 6). Този ред съдържа номера на поръчката " 5 "от фирма" ЧП "Коло" ". След копиране на хипервръзката от клетката " E2 » надолу по колоната « E" в клетка "E6 „Формулата с функцията за търсене ще бъде така:“MATCH(C6;Клиенти!$A:$A;0)". И резултатът от този израз ще бъде равен на " 6 ". Това означава, че в таблклиенти " Описание на компанията "ЧП "Коло" " намиращ се в шестолиния на работен лист.

Продължавай. Ще заместим резултата от този израз като първия параметър на адреса на функцията. Вторият параметър е " 1 ". Тогава изразът "АДРЕС(ТЪРСЕНЕ (C2;Клиенти!$A:$A;0);1)" (вторият ред на таблицата "Поръчки "") по същество означава "АДРЕС(2;1) ". Тази функция ще върне като текст адреса на клетката, която е във втория ред и първата колона на работния лист. За поръчка" 1 ' (втори ред) това ще бъде адресът на '$2 $ ". За поръчка в шести ред на таблицата (фирма "ЧП "Коло" ") изразът ще върне низа "$A$6" и т.н.

С адресирането на клетка е ясно. Сега трябва да използвате резултата във функцията " HYPERLINK() ".

Ето каква е ситуацията. Нека ви напомня, че функцията "ХИПЕРВРЪЗКА() » два параметъра. Първият е адресът, към който сочи хипервръзката. Той включва името на файла, листа и адреса на клетката, към която да преминете. Вече имаме имената на файловете и листовете, тази информация се съхранява в променливата " My_List ". Получихме адреса на клетката за връзка с контрагента за конкретна поръчка. Остава да комбинирате тези две части с операцията "& ". И тогава крайният израз за адреса за прескачане ще бъде:My_List&ADDRESS(SEARCH(C2; Клиенти!$A:$A;0);1)».

Да видим какво имаме. За клетка " C2 » тази формула ще върне резултата «[DeanReferenceExcel_.xls]Клиенти!$A$2" - т.е. препратка към клетката " A2 » в указателя «клиенти ". Точно така: за номер на поръчка " 1 » връзката сочи към контрагента «ТОВ "Смит" ". Ако се позовавате на клетката C6 " (Номер на поръчка " 5” от „ЧП „Коло” “), след това в този ред на таблицата „Поръчки „Изразът за адреса за преход ще изглежда така:“My_Sheet&ADDRESS(SEARCH(C6;Customers!$A:$A;0);1)". И резултатът от формулата ще бъде:[DeanReferenceExcel_.xls]Клиенти!$6 A$».

Преминаваме към втория параметър на функцията "ХИПЕРВРЪЗКА() ". Това трябва да е текстът, койтоExcel ще покаже хипервръзката на място. С други думи, това е името на самата хипервръзка. Избрах символа ">“ (знак „още "). И тогава крайната формула за създаване на хипервръзка ще бъде:=HYPERLINK(My_List&ADDRESS(SEARCH(C2; Клиенти!$A:$A; 0);1);" > " ) ».

Това е всичко за създаването на релации между таблицитеExcel. Справихме се със задачата, всички връзки работят правилно. Единственото нещо, което бих искал да добавя няколко думи за дизайна на масата. Сега в колоната