Grafikonok összekapcsolása Excelben. Excel munkafüzet

Használta a VLOOKUP függvényt az oszlopadatok egyik táblából a másikba való áthelyezéséhez? Mivel az Excel immár beépített adatmodellel rendelkezik, a VLOOKUP függvény elavult. Kapcsolatot hozhat létre két tábla között a bennük lévő egyező adatok alapján. Ezután Power View-lapokat vagy kimutatásokat és egyéb jelentéseket hozhat létre az egyes táblák mezőivel, még akkor is, ha azok különböző forrásokból származnak. Ha például rendelkezik vevői értékesítési adatokkal, érdemes lehet importálni és linkelni az értékesítési trendek év és hónap szerinti elemzéséhez.

A munkafüzet összes táblája szerepel a kimutatástáblázat és a Power View mezőlistában.

Kapcsolódó táblák importálásakor innen relációs adatbázis excel adatok gyakran létrehozhatja ezeket a kapcsolatokat a háttérben generált adatmodellben. Más esetekben manuálisan kell társításokat létrehoznia.

Ha a csapat Kapcsolat nem érhető el, akkor a munkafüzet csak egy táblázatot tartalmaz.

Az ablakban Kapcsolat-kezelés nyomja meg a gombot Teremt.

Az ablakban Hozzon létre kapcsolatot kattintson a mező melletti nyílra asztalés válasszon ki egy táblázatot a legördülő listából. Egy-a-többhöz kapcsolat esetén ennek a táblázatnak többtagú résznek kell lennie. Az ügyfél- és időintelligencia példában először ki kell választania a vevőértékesítési táblát, mert minden nap valószínűleg sok értékesítés lesz.

Elemhez Oszlop (idegen) válassza ki az elemhez kapcsolódó adatokat tartalmazó oszlopot. Például, ha mindkét táblázatban van egy dátum oszlop, akkor most ki kell választania azt az oszlopot.

A terepen Kapcsolódó táblázat válasszon ki egy táblát, amely legalább egy olyan adatoszlopot tartalmaz, amely a mezőben kiválasztott táblához kapcsolódik asztal.

A terepen Kapcsolódó oszlop ( elsődleges kulcs) válassza ki a tartalmazó oszlopot egyedi értékek, amelyek megfelelnek a mezőben kiválasztott oszlop értékeinek Oszlop.

Kattintson a gombra rendben.

További információ az Excel táblázatai közötti kapcsolatokról

Kapcsolati megjegyzések

Példa. Az időintelligencia adatok társítása a légitársaság repülési adataival

A Microsoft Azure Marketplace ingyenes adatokkal megtudhatja a két tábla közötti kapcsolatokat és az időintelligenciát. Ezen adatkészletek némelyike ​​nagyon nagy, és szükséges gyors kapcsolat az internetre.

Mostantól az érkezési késéseket évre és hónapra, valamint egyéb értékekre oszthatja a naptárban.

Tanács: Alapértelmezés szerint a hónapok ábécé sorrendben vannak felsorolva. A Power Pivot bővítménnyel módosíthatja a rendezési sorrendet, hogy azok időrendi sorrendben jelenjenek meg.

asztal BasicCalendarUS nyitva kell lennie a Power Pivot ablakban.

A fő táblázatban kattintson a gombra Rendezés oszlop szerint.

A "Rendezés" mezőben válassza ki a lehetőséget MonthInCalendar.

A "Címzett" mezőben válassza ki a lehetőséget Év hónapja.

A PivotTable mostantól az egyes "hónap és év" kombinációkat (2011. október, 2011. november) az év hónapjának száma (10, 11) szerint rendezi. A rendezési sorrend megváltoztatása egyszerű, mert a csatorna dátumfolyam biztosítja az összes szükséges oszlopot a szkript működéséhez. Ha más időintelligencia-táblázatot használ, a lépései eltérőek lesznek.

"A táblák közötti kapcsolatok szükségesek lehetnek"

Ahogy a mezők hozzáadódnak Pivot táblaértesítést kap arról, hogy a táblák közötti kapcsolat szükséges a kimutatásban kiválasztott mezők kezeléséhez.

Míg az Excel meg tudja mondani, hogy mikor van szükség hivatkozásra, azt nem tudja megmondani, hogy mely táblázatokat és oszlopokat kell használni, illetve hogy lehetséges-e hivatkozás a táblázatok között. Ha választ szeretne kapni kérdéseire, próbálja ki a következőket.

1. lépés: Döntse el, mely táblákat vegye fel a kapcsolatba

Ha a modell csak néhány táblázatot tartalmaz, egyértelmű, hogy melyiket használja. De nagyobb modellek esetén segítségre lehet szüksége. Az egyik módja a diagram nézet használata a Power Pivot bővítményben. A diagram nézet az adatmodell összes táblájának megjelenítését biztosítja. Segítségével gyorsan meghatározhatja, hogy mely táblázatok legyenek elválasztva a modell többi részétől.

Jegyzet: Létrehozhat kétértelmű kapcsolatokat, amelyek érvénytelenek, ha kimutatásban vagy Power View jelentésben használják. Előfordulhat, hogy az összes táblája valamilyen módon kapcsolódik a modell más tábláihoz, de amikor megpróbálja egyesíteni a különböző táblák mezőit, a "Táblázatok közötti kapcsolatok szükségesek lehetnek" üzenet jelenik meg. A legtöbb lehetséges ok az, hogy találkoztál egy sok-sok kapcsolattal. Ha a szükséges táblákhoz kapcsolódó táblák közötti kapcsolatok láncát követi, akkor valószínűleg azt fogja tapasztalni, hogy a táblák között kettő vagy több egy a többhez kapcsolat van. Nincs egyszerű megoldás, amely minden helyzetben működne, de megpróbálhat kiszámított oszlopokat létrehozni a használni kívánt oszlopok egy táblázatban való összevonásához.

2. lépés: Keressen olyan oszlopokat, amelyek segítségével útvonalat hozhat létre egyik táblából a másikba

Miután megállapította, hogy melyik tábla nem kapcsolódik a modell többi részéhez, tekintse át a táblázat oszlopait annak megállapítására, hogy a modellben más oszlopok tartalmaznak-e egyező értékeket.

Tegyük fel, hogy van egy modellje, amely a termékek értékesítését területek szerint tartalmazza, és ezt követően demográfiai adatokat importál, hogy megnézze, van-e összefüggés az értékesítés és a demográfiai trendek között az egyes területeken. Mivel a demográfiai adatok sokféle forrásból származnak, ezek táblázatai kezdetben elkülönítve vannak a modell többi részétől. Ahhoz, hogy a demográfiai adatokat integrálja a modell többi részével, meg kell találnia egy oszlopot az egyik demográfiai táblázatban, amely megfelel a már használt oszlopnak. Például, ha a demográfiai adatok régiók szerint vannak rendezve, és az értékesítési adatok értékesítési területet határoznak meg, akkor a két adatkészletet összekapcsolhatja azáltal, hogy közös oszlopokat keres, mint például az állam, az irányítószám vagy a régió.

Az értékek egyeztetése mellett néhány további követelmény is van az asszociációk létrehozásához.

Ha kell Adatok automatikus átvitele egyik Excel-táblázatból a másikba, össze kell kapcsolnia ezeket a táblázatokat. Az Excelben többféleképpen kapcsolhatja össze az adatokat két táblázatból. egyirányú - képlet használatával olvassa el a „Táblázatok összekapcsolása Excelben” című cikket.
Tessék, fontoljuk meg hogyan lehet két táblázatot összekapcsolni különböző Excel-munkafüzetekben táblák közötti kapcsolatok létrehozásával. Ha minden be van állítva, elég megnyitni egy táblát, hogy az adatok egy másik táblából átkerüljenek ebbe a táblába. A kapcsolat működéséhez nem kell minden könyvet kinyitnia. Egy nyitott munkafüzettel fog működni.
Tehát két Excel-munkafüzetünk van - Book1 és Book2. Az 1. könyvben van ilyen táblázat, már kitöltve. Asztal 1.Figyelem!
Ha a táblázat egyesített cellákat tartalmaz, akkor a hivatkozás nem fog működni bennük, vagy tévútra megy.
A 2. könyvben elkészítjük ugyanazt a táblázatot, de nem töltjük ki.

Egy másik "hivatkozás beszúrása" funkciót így hívhatunk. Másoljuk is, de a helyi menüben rákattintunk a " funkcióra Speciális betét". A megjelenő párbeszédpanelen kattintson a "Hivatkozás beszúrása" gombra.

Ha az egyik munkalap egy másik munkalapról származó adatokat használ, akkor ezek a lapok összekapcsoltnak minősülnek. A linkeléssel több különböző táblázat cellaértékeit is összehozhatja ugyanazon a munkalapon.

Egy cella tartalmának módosítása ugyanazon a lapon vagy táblázaton ( forrás) módosítja a kapcsolódó cellákat lapokban vagy táblázatokban ( vevőkészülékek). Ez az elv megkülönbözteti a lapok összekapcsolását a cellák tartalmának egyszerű másolásától egyik lapról a másikra.

A végrehajtási technikától függően a kötés az "egyenes"és parancson keresztül SPECIÁLIS BETÉT.

1. módszer – „Közvetlen cellakapcsolás”

Közvetlen linkelés A lapok közvetlenül a képlet cellába való beírásakor használatosak, amikor a képlet egyik elemeként egy másik munkalap cellájára való hivatkozást használjuk. Például, ha a 2. munkalap B4-es cellája tartalmaz egy képletet, amely egy másik munkalap (például Munkalap1) A4-es cellájára mutató hivatkozást használ, és mindkét lap meg van töltve adatokkal, akkor a lapok ilyen összekapcsolását „közvetlen”-nek nevezzük.

Term "közvetlen" kötés azt jelenti, hogy maga a felhasználó közvetlenül a képlet beírásakor azt jelzilap neveÉs abszolút cellacím, "!" felkiáltójellel elválasztva.

Képlet példák: = C5*Lap1! A4

Lap3! B2*100%

Lap1! A1- Lap2! A1

Jegyzet.

A betöltetlen (megbontatlan) munkafüzetekben található cellákra és lapokra mutató hivatkozás megadásához a képletben szóközök nélkül kell megadni teljes útvonal fájl helyét. Az elérési út szimpla idézőjelben van megadva, ahol a lemez neve, a könyvtár (mappa), a munkafüzet neve (fájlnév) szögletes zárójelben és a lap neve, amelyre a hivatkozás mutat.

2. módszer – Cellák összekapcsolása a „Speciális beillesztés” paranccsal

Összekapcsolás parancson keresztül SPECIÁLIS BETÉT akkor hajtjuk végre, ha az egyik munkalapon lévő táblázat bármely cellájának tartalmaznia kell egy másik munkalap cellájának értékét.

A lap C4 cellájában tükrözni Ár a H4 cella értéke az eredeti lapon Vásárlás, akkor a kurzort a forráslap H4 cellájára kell helyezni, és végre kell hajtani a parancsot Szerkesztés – Másolás. A lapon Ár vigye a kurzort a C4 cellára, amelyet az eredetihez kell kapcsolni, és hajtsa végre a parancsot Szerkesztés – Speciális beillesztés – Hivatkozás beillesztése(lásd a 8. ábrát). Aztán a lapra Ár megjelenik az eredeti lap cellájának jelzése Vásárlás, Például: = Vásároljon! $4 N$

Ezzel az összerendeléssel az EXCEL automatikusan a cellánkénti abszolút címet használja, mert a relatív hivatkozási cím hibákhoz vezethet, különösen a betöltetlen fájlok (munkafüzetek) elérésekor.

Gyakorlat. Kapcsolja össze a táblázat C4, C5, C6, C7, C8 celláit Beszerzési költségek egy lapon Ár a lap megfelelő celláival Vásárlás, a sejtek összekapcsolásának különféle módszereivel (8. ábra).

Rizs. 8 Különböző munkalapok celláinak összekapcsolása

! A cellák összekapcsolásakor határozza meg, hogy mely cellák a forráscellák.

! Egy csatolt tábla esetén a különböző munkalapokon vagy az aktuális munkalapon lévő különböző táblázatokból származó cellák lehetnek forráscellák.

Önálló munkára vonatkozó feladatok.

Egy önálló feladat elvégzése előtt vegye figyelembe a laptáblázatok példáját Ár sejtek összekapcsolása.

Például:

egy lapon Ár az asztalban Beszerzési költségek Az A4:A8 cellák táblázatcellákhoz vannak kapcsolva A vásárolt termékek mennyisége egy lapon Vásárlás;

A B4:B8 cellák kezdeti, mert. tartalmazzon kezdeti információkat a vásárolt áruk árairól;

A C4:C8 cellák a munkalap H4:H8 celláihoz kapcsolódnak Vásárlás;

A D4:D8 cellák képleteket tartalmaznak a vásárolt árura fordított összeg kiszámítására, és a saját táblázatuk celláira hivatkoznak (például a D4 cellában lévő képlet így néz ki: = B4 * C4, ami azt jelenti, hogy az áru árát meg kell szorozni Mennyiség);

D9 cella a D4:D8 cellák összege;

a második táblázatban Árkalkuláció ugyanazon a lapon az A14:A18 cellák az 1. záradékhoz hasonlóan kapcsolódnak;

A B14:B18 cellák az aktuális B4:B8 lap eredeti celláihoz kapcsolódnak;

A C4:C8 cellák kezdeti, mert tartalmazzon kezdeti információkat a szalon felárairól a vásárolt árukra vonatkozóan;

A D14:D18 cellák képleteket tartalmaznak egy termék eladási árának kiszámításához, és a saját táblázatuk celláira hivatkoznak (például a D14 cellában lévő képlet így néz ki: = B14 * C14 + B14, ami azt jelenti, hogy a vételárat meg kell szorozni a készlettel feláras százalék, amely megadja a vételárhoz hozzáadandó felár összegét);

Miután elvégezte az összes műveletet ezekkel a táblázatokkal, ellenőrizze a "működésüket".

változás A termék neve - Kanapé lap A4-es cellájában Vásárlás másnak – például Kanapé.

változás vásárolt áruk mennyisége Kanapé júniusban (a munkalap G4 cellájában Vásárlásírja be a 11-es számot).

változás a lap B4-es cellájában található Kanapé vételárát Ár másikért - 2500,00 rubel.

változás a kanapé margójának százalékos aránya a lap C14-es cellájában Ár 50%-ról 32%-ra.

Jelölje be , történt-e változás a kapcsolódó táblázatokban vagy nem?

Jegyzet, a különböző táblázatok mely celláit érintették a változások.

Figyelem! Amikor a cellákat PASTE SPECIAL-lel kapcsolja össze... a szomszédos cellákba való másolás problémássá válik a cellák abszolút címzése miatt.

1. Feladat. Különféle módszerekkel kapcsolja össze a munkafüzet többi táblázatának celláit.

Utasítás. Az 1 negyedéves bevételek és bevételek számítási táblázataiban csak 1 negyedév forráscelláinak összegzésének képlete kerül felhasználásra.

2. feladat. Készítsen lapokon BevételÉs Jövedelem 2. negyedévi táblázatok. Kapcsolja össze ezeket a táblázatokat a megfelelő forrásadatokkal.

Utasítás.

3. feladat. Rajzoljunk kördiagramot egy lapra Jövedelemés elemezze a jövedelem terméktípusonkénti megoszlását.

4. feladat. Adjon hozzá egy munkalapot a munkafüzet végéhez Összevont nyilatkozat. Hozzon létre rajta egy pivot táblát, amely tükrözi a vásárlások és eladások számát, árrést, vételi és eladási árakat, árbevételt az 1. negyedévre és a 2. negyedévre terméknevek szerint. Kapcsolja össze ezt a táblázatot a megfelelő forrásadatokkal más munkalapokon.

Utasítás. A 2. negyedévi bevételek és bevételek kiszámításához használt táblázatokban csak 2 negyedévre használja a forráscellákat.

Információk táblázatban Microsoft Excel kombinálható a rajzból kinyert adatokkal.

E-mail kapcsolat létrehozásával táblázatot információkat tartalmazhat a teljes lapról vagy annak csak egy részéről, pl. nevű tartomány. A Hivatkozás külső adatokhoz párbeszédpanelen a következőket teheti:

• Állítsa be az adatkapcsolatot annak az XLS-fájlnak a kiválasztásával, amelyhez a rajzból lehívott adatokat csatolni kívánja.
• A kivont rajzadatokat képezze le a táblázat adataival.
• Válassza ki az XLS-fájlban azokat az oszlopokat, amelyeket egyesíteni szeretne a kivont rajzadatokkal.

Az Adatok szerkesztése oldal hozzáférést biztosít a párbeszédablak Hivatkozás külső adatokhoz, ahol a letöltött adatokat a teljes Excel-táblázathoz vagy sorok és oszlopok tartományához kapcsolhatja.

A táblázatra mutató hivatkozás létrehozásához az adatlekérést végző számítógépnek rendelkeznie kell Microsoft alkalmazás Excel. Ha nincs telepítve Microsoft program Excel ill XLS fájl nincs helyben tárolva azon a gépen, ahol az adatokat lekérik, az adatkapcsolat nem hozható létre.

A csatolt táblázat módosítása, például egy sor vagy oszlop hozzáadása után a REFRESH paranccsal frissítheti a rajzban szereplő táblázatot, hogy tükrözze a változást. Hasonlóképpen, ha módosít egy táblázatot a rajzban, a kapcsolódó táblázatot is frissítheti ugyanazzal a paranccsal.

Megjegyzés: Az adatkapcsolat eltávolításához használja a REFER parancsot. Paletta adatkapcsolatok Külső linkek" nem törölhető.

Az adatösszekapcsolásról és -leképezésről

Az adatösszekapcsolás abból áll, hogy kiválasztunk egy Microsoft Excel-táblázatot, amely a táblázat egészét vagy egy részét egyesíti a kivont rajzadatokkal. Az adat-összerendeléssel kapcsolatos további információkért lásd: „Táblázattársítás létrehozása külső adatokkal”.

Az adatleképezési folyamat két adatkészlet között hoz létre kapcsolatot: a kivont rajzadattábla oszlopában lévő adatok és az Excel táblázat oszlopában lévő adatok között.

A következő példa egy egyszerű anyagjegyzéket mutat be. A rajzból három adatoszlop kerül kinyerésre. Ezek az oszlopok a következő adatokat tartalmazzák: rész sz., SzerkesztőségiÉs Mennyiség. A specifikációban két további adatoszlop is szerepelhet a táblázatból. Ezek az oszlopok a következő adatokat tartalmazzák ÁrÉs Szolgáltató.

BAN BEN ezt a példát a táblázat tartalmaz egy cikkszám oszlopot, amelynek fejléce van Katalógus számés megfelel az oszlopnak Részletszám a kivont adattáblázatban. A Hivatkozás külső adatokhoz párbeszédpanelen válassza ki a lehetőséget "Részletszám" rajzadatok oszlopaként, és "Katalógus szám" a külső adatok oszlopaként. Mivel mind a rajzi adatoszlopban, mind a külső adatoszlopban van legalább egy megfelelő adatcella, az adatkinyerési funkció "tudja", hogy a két forrás hogyan kapcsolódik egymáshoz.

Az adatok konzisztenciája a Hivatkozás külső adatokhoz párbeszédpanelen a Megfelelőség ellenőrzése gombra kattintva ellenőrizhető. Ha eltérés van, figyelmeztető üzenet jelenik meg, amely jelzi az okot. Egyes esetekben megfelelő alkotás nincs kapcsolat a rajzadatok és a táblázat adatai között.

Feltérképezett adatok ellenőrzése

Az adatkonzisztencia-ellenőrzési hiba azt jelenti, hogy nem található érvényes egyezés a rajzi adatok és a külső adatok között (a táblázatban). A következő hibaüzenetek jelennek meg.

Nem találtunk megfeleltetést a rajzi adatok és a külső adatok értékei között.

• Ha a probléma továbbra is fennáll, a külső adatok nem egyesülnek. Hasonlítsa össze a táblázat oszlopadatait a rajzi adatokkal, és próbáljon meg egyező értékeket találni. Például rajzadatok és külső adatok állíthatók be különböző jelentések pontosság.

A külső adatok adatoszlopa nem tartalmaz egyedi értékeket.

• Egy külső adatoszlop egy vagy több cellát tartalmaz ugyanazokkal az adatokkal. Ebben a helyzetben nem lehet a rajzadatokat egyedileg leképezni a külső adatokra.

Mindenekelőtt a táblázatok közös jellemzők szerinti összekapcsolása nem jellemző az MS Excel funkciójára. Az ilyen feladatokra ott speciális programokúgynevezett adatbázisok. Bár helyesebb ezeket "adatbázis-kezelő rendszereknek" vagy DBMS-nek nevezni. Ilyen például az MS Access, az MS SQL szerver, Oracle és még sokan mások. Ezekben a rendszerekben a táblák közös kulcsmezők szerinti összekapcsolása a leggyakoribb művelet. Megoldásához pedig erős és praktikus eszközök. Lehetővé teszik a táblák közötti kapcsolatok sokféle típusának megszervezését, az adatok integritásának ellenőrzését, a rekordok lépcsőzetes törlését stb. A DBMS teljes körű használatához azonban szüksége van némi képzésre, a lekérdezési nyelv ismeretére stb. Ezért a könyvelői gyakorlatban ritkán használnak DBMS-t tiszta formájában, és minden adatfeldolgozás általában Excel program. Ami az MS Excelt illeti, itt korlátozottak a táblák összekapcsolásának lehetőségei. Ez a kapcsolat alapvetően különböző lapokra vagy munkafüzetekre utaló képletek írására szolgál. Létrehozhat olyan nyomtatott jelentést vagy kimutatási táblát, amely több adatbázison működik. De nincsenek speciális eszközök az adatkészletek közötti kapcsolatok teljes körű megszervezéséhez az Excelben, és nem is kellene.

Azonban nem minden olyan rossz... A tény az, hogy a valódi munkában Minden A táblázatok könyvelőhöz való kapcsolásának lehetőségeire általában nincs szükség. Igen, és a rekordok lépcsőzetes törlése nélkül is sikerül valahogy. De egy nagy táblázat összekapcsolása egy referenciakönyvvel meglehetősen releváns és gyakorlatilag alkalmazható feladat. És ami a legfontosabb (!), egy ilyen funkció teljesen megvalósíthatóMS Excel. Ehhez pedig csak néhány egyszerű trükkre és hiperhivatkozási rendszerre van szükség.

Példaként két táblázatot fogok használni, amelyek formáját a ábra mutatja. 1. és 2. Az első táblázat (a lapon található Megrendelések ”) négy mezőből áll: „ Megrendelés”, „Dátum”, „Ügyfél”, „Összeg "és míg az üres mező" Megjegyzés. ". Ez a rendelési adatbázis egyszerűsített változata. mezőben " Megjegyzés. » végül elhelyezünk egy hivatkozást a könyvtárelemre. Nyilvánvaló, hogy az adatbázisban Megrendelések » Egy partner többször is megjelenhet – ez az ügyfél által adott megbízások számától függ.

A második táblázat az úgynevezett "Ügyfelek ". Csak öt oszlopot hagytam benne: " Név ” (a vállalkozás neve), majd az igazgató neve, jogi címe, telefonszáma és e-mail címe.

Fontos! Az "Ügyfelek » egy sor egy vállalkozás adatait tartalmazza. Itt nem megengedett az ismétlés, minden kliens csak egyszer van jelen az adatbázisban.

Tehát kapcsolatot fogunk szervezni az asztal között" Megrendelések” és „Vásárlók ". Ehhez a terepen Megjegyzés. » asztalok « Megrendelések » helyezünk el egy hiperhivatkozást, amely egy adott táblázatsorra mutat «Ügyfelek ". A hiperhivatkozás algoritmusa a következő lesz. Először is elemeznie kell a mező tartalmát NaimPredpr "V aktuális vonal asztalok" Megrendelések ". Más szóval, emlékezzen Cégnév aki adott rendelést. A linknek ezután a lapra kell ugrania"Ügyfelek ". Ezen a lapon keressen egy sort, amely leírja a megrendelő cég paramétereit. És végül - állítsa be a mutatót aktuális cella a megfelelő sorba a könyvtárban "Ügyfelek ". Általánosságban így néz ki.

Egyébként az adatbázis terminológiájában egy ilyen kapcsolatot " egy a sokhoz " (az asztal oldaláról nézve"Ügyfelek "). Vagyis a vevőkönyvtár egyik eleme a rendelési adatbázis több rekordjára hivatkozik. Az MS Excelben nincsenek speciális eszközök egy ilyen hivatkozás létrehozására. Ezeket magunknak kell megépíteni. Ehhez az algoritmus alapján szükségünk van: egy beépített függvényre hiperhivatkozás létrehozásához (az ilyen függvényt " HIPERLINK() ”), valamint egy táblázatban lévő adatok keresésére szolgáló függvény. Javaslom a " funkció használatát MÉRKŐZÉS() ". De ez még nem minden. A hiperhivatkozásnak lennie kell a pontos címet az átmenethez: fájlnév, munkalapnév és az aktívvá tenni kívánt cella. A fájl, lap és cella nevének lekéréséhez a " függvényt használjuk SEJT() ". Ezenkívül az ugráscímmel egy sor létrehozásához a szöveggel való munkavégzés beépített függvényeihez kell fordulnunk: " MID() "és" SEARCH() ". Valójában ez az összes fő eszköz. Továbbléphet a gyakorlati megvalósításra.

Először csináljunk néhányat előzetes intézkedés. Kezdjük a "függvénnyel" HIPERLINK() ", melyik lesz kulcs a táblák közötti kapcsolatok szervezésekor. Két lehetősége van. Az első a cím, vagyis annak a fájlnak és munkalapnak a neve, amelyre a hivatkozás mutat. A második paraméter egy adott cella címe ezen a lapon, ahová a hiperhivatkozás mutat. Sok hiperhivatkozásunk lesz. És minden alkalommal, amikor létrehozza őket, meg kell adnia a fájl nevét és a lap nevét a függvény paramétereiben. Hosszú és kényelmetlen. Ezért jobb, ha egyszer létrehoz egy változót a lap és a fájl nevével, majd adja meg a változóját a " függvényben HIPERLINK() " szükség szerint. Az MS Excel tekintetében kényelmes ilyen változóként használni nevű sejttartomány. Itt kezdjük.

1. Meghívjuk az Excelt, betöltjük a dokumentumot, és belépünk a menübe " Képletek ". A szalag úgy fog kinézni, mint az ábrán. 3.

2. A csoportban " Bizonyos nevek» kattintson az ikonra « Névkezelő ". ábrán látható ablak. 4.

3. Ebben az ablakban nyomja meg a " gombot Teremt ". Az ablak " A névadás ”, ábrán látható. 5.

4. Ebben az ablakban a " Név:" írja be a "My_List" szöveget. A Tartomány mezőben: » nyomtatja ki a képletet «=MID(CELL("fájlnév" ;Ügyfelek!$A$1);SEARCH(" [ " ;CELL(" filename " ;Customers!$A$1));256)& " ! " » (3. ábra).

5. A "Név létrehozása" ablakban kattintson az "OK" gombra.

6. A Névkezelő ablakban kattintson az OK gombra.

Most pedig lássuk, mit is csináltunk valójában? A munkafüzetnek van egy új nevű tartománya " A listám".

Fontos! Esetünkben a megnevezett tartomány nevének szóközök NÉLKÜL kell lennie. Ellenkező esetben a képletek címzése nem fog megfelelően működni.

Mint minden elnevezett tartomány, ez is egy cellára vagy cellacsoportra mutat egy Excel-munkafüzetben. De a mi esetünkben ennek a csoportnak a címe nem állandó, hanem dinamikus. Az a képlet képezi, amelyet beírtunk a mezőbe " Hatótávolság: ". Röviden ennek a képletnek a működéséről. Kezdjük belülről – a funkcióval SEJT()".

A "CELL(" fájlnév " ;Ügyfelek! $A$1) kifejezés "a cellára utal" A1" az „Ügyfelek ” és ehhez a cellához visszaadja azt teljes cím , azaz a fájl elérési útja, a fájl neve és annak a lapnak a neve, ahol ez a cella található. Például a táblákat tartalmazó dokumentumom neve " DeanReferencesExcel_.xls ". Lemezen van tárolva D:" a "!Factor" mappában ". Ekkor a képlet eredménye a következő lesz:D:!Factor[DeanReferencesExcel_.xls]kliensek».

Ebből a sorból csak ki kell vennünk fájlnevekÉs lap A munkafüzet - a meghajtó betűjelére és a mappa nevére nem lesz szükség hiperhivatkozás létrehozásakor. A probléma megoldására a szabványos függvényeket használjukExcel szöveggel való munkához.

A karakterlánc egy részét a " függvénnyel vághatja ki KÖZÉPSŐ() ". De meg kell határoznia: eredeti szöveg(megvan a cellához vezető teljes elérési út), kezdő pozícióÉs jelek száma Az eredeti karakterláncról leválasztandó.

A kiindulási helyzet könnyen meghatározható. Ehhez használja a funkciót KERESÉS() " megtaláljuk a szögletes nyitó zárójel első előfordulását ("[ ”) a szövegbe, ahol a cella elérési útja van tárolva. A műveletet végrehajtó képletrészlet így néz ki: SEARCH(" [ " ;CELL(" fájlnév " ; Ügyfelek!$A$1)) . a vonalhoz" D:!Factor [DeanReferencesExcel_.xls]kliensek» ez a képlet vissza fog térni « 12 ". A forrásszövegből kivágandó karakterek száma a maximumot választottam - " 256".

Marad a kapott értékek behelyettesítése a függvénybe " KÖZÉPSŐ() ". Első paramétere a cella címét tartalmazó szöveg. A második paraméter a húr egy részének vágásához szükséges kezdőpozíció száma. Az utolsó paraméter a karakterek soronként megengedett maximális száma. Ennek a műveletnek a képlete a következő lenne:=MID(CELL("fájlnév" ;Ügyfelek!$A$1);KERESÉS

(" [ " ;CELL(" fájlnév " ;Ügyfelek!$A$1));256) ". Példánkban egy ilyen képlet az eredményt a következő formában adja vissza[DeanReferenceExcel_.xls]Ügyfelek". Valójában ez egy link a lapra "Ügyfelek » munkafüzet « DeanReferencesExcel_.xls ". A következő lehet a cella címe. De eddig egy elem hiányzik - a lap neve és a cella címe között lennie kell Felkiáltójel (« ! "). Ezt a szimbólumot hozzáadhatjuk a képlethez a " művelettel& ", és a végső verzióban a kifejezés így fog kinézni:"=MID(CELL("fájlnév" ;Ügyfelek!$A$1);SEARCH(" [ " ;CELL(" fájlnév " ; Ügyfelek!$A$1)); 256)&" ! " ". És munkájának eredménye a következő lesz:».

Tanács Óvatosan másolja át a képletrészleteket a munkalap külön celláiba, és akkor az egyes részek munkája kéznél lesz.

Egyébként a képlet működése egy elnevezett tartományon belül könnyen ellenőrizhető. Írja be a "" kifejezést=My_Sheet ” és nyomja meg a „ Belép ". Az eredménynek meg kell jelennie a cellában:[DeanLinkExcel_.xls]Ügyfelek!».

Fontos! Kérjük, vegye figyelembe, hogy a helyes működés a lapnév képletei nem tartalmazhatnak szóközt.

1. Ugrás a lapra Megrendelések ", menj be a cellába" E2".

2. Írja be a következő képletet: "=HYPERLINK(Saját_lap&CÍM(SEARCH(C2;Ügyfelek!$A:$A;0); 1);" > " ) ».

3. Másolja ezt a képletet a táblázat teljes magasságába. Munkánk eredményét az ábra mutatja. 6.

Nézzük meg, mi van. Az asztalban " Megrendelések »bal gombbal kattintson például a cellára « E6 ". Ez a sor a következő számú megrendelésről tartalmaz információkat 5 "a cégtől" PE "Kolo"". Kattintás után Az Excel átvált a "Ügyfelek ", és az aktív cellamutató a következő címen lesz A6 ". A címtár ezen pozíciójában találhatók információk a " PE "Kolo"" (7. ábra).

Tekintsük röviden a képlet algoritmusát. Kifejezés "=MATCH(C2;Ügyfelek! $A:$A;0)» talál egy cellát az oszlopban « A" az „Ügyfelek ", amely tartalmazza a partner nevét a " cellából C2" lap „Rendelések ". A " függvény utolsó paramétere MATCH() "egyenlő" 0-val ". Ez azt jelenti, hogy az elv szerint fog keresni egy értéket pontos mérkőzések. Térjünk vissza példánkhoz. Tegyük fel, hogy a karakterlánccal dolgozunk 6 » asztalok « Megrendelések » (6. ábra). Ez a sor tartalmazza a "rendelési számot" 5 "a cégtől" PE "Kolo" ". Miután kimásolta a hiperhivatkozást a cellából " E2 » lefelé az oszlopban « E" az „E6 "A képlet a kereső funkcióval a következő lesz:"MATCH(C6;Ügyfelek!$A:$A;0)". És ennek a kifejezésnek az eredménye egyenlő lesz: " 6 ". Ez azt jelenti, hogy a táblázatbanÜgyfelek " cégleírás " PE "Kolo" " található hatodik munkalap sor.

Menj tovább. Ennek a kifejezésnek az eredményét behelyettesítjük a függvénycím első paramétereként. A második paraméter a " 1 ". Aztán a kifejezés: " CÍM(KERES (C2;Ügyfelek!$A:$A;0);1)"(a táblázat második sora" Megrendelések "") lényegében azt jelenti, hogy " CÍM(2;1) ". Ez a függvény szövegként adja vissza annak a cellának a címét, amely a munkalap második sorában és első oszlopában található. Rendelésre " 1 ' (második sor) ez lesz a címe$2 A$ ". A táblázat hatodik sorában található megrendeléshez (cég " PE "Kolo" ") kifejezés a " karakterláncot adja vissza$6" stb.

Egy cella címzésével egyértelmű. Most az eredményt kell használnia a " függvényben HIPERLINK()".

Íme a helyzet. Hadd emlékeztesselek arra, hogy a " HIPERLINK() » két paraméter. Az első az a cím, amelyre a hiperhivatkozás mutat. Tartalmazza a fájl, a munkalap nevét és a cella címét, amelyre ugrik. A fájl- és lapnevek már megvannak, ezek az információk a " változóban vannak tárolva. A listám ". Megkaptuk annak a cellának a címét, amelyet egy adott megbízás partneréhez kell kapcsolni. Marad hátra, hogy ezt a két részt összekapcsoljuk a művelettel "& ". És akkor az ugráscím végső kifejezése a következő lesz:Saját_Lista&CÍM(KERESÉS(C2; Ügyfelek!$A:$A;0);1)».

Lássuk, mit kaptunk. cellához " C2 » ez a képlet visszaadja az eredményt «[DeanReferenceExcel_.xls]Ügyfelek!$2 A$"- azaz hivatkozás a cellára" A2 » a könyvtárban «Ügyfelek ". Így van: a rendelési számhoz " 1 » a link a partnerre mutat « TOV "Smit" ". Ha a cellára hivatkozik C6 " (Rendelésszám " 5" from "PE "Kolo" ), akkor a táblázat ezen sorában a „ Megrendelések "Az ugráscím kifejezése így fog kinézni:"Saját_lap&CÍM(SEARCH(C6;Ügyfelek!$A:$A;0);1)". És a képlet eredménye a következő lesz:[DeanReferenceExcel_.xls]Ügyfelek! 6 USD».

Áttérünk a függvény második paraméterére HIPERLINK() ". Ennek a szövegnek kell lennieAz Excel a helyén jeleníti meg a hiperhivatkozást. Más szóval, ez magának a hiperhivatkozásnak a neve. a szimbólumot választottam">" (jel "tovább "). És akkor a hiperhivatkozás létrehozásának végső képlete a következő lesz:=HYPERLINK(Saját_Lista&CÍM(SEARCH(C2; Ügyfelek!$A:$A; 0);1);" > " ) ».

Ez minden a táblák közötti kapcsolatok létrehozásáról szólExcel. Megbirkóztunk a feladattal, minden link megfelelően működik. Az egyetlen dolog, amit szeretnék néhány szót hozzáfűzni az asztal kialakításához. Most az oszlopban