Lap- és oldalszámképlet Excelben. Képlet lap- és oldalszámokhoz Excelben Hogyan készítsünk tartalmat az Excelben

Elemezzük a képleteket Lap- és oldalszámok Excelben(mind standard, mind egyéni funkciók) tartalomjegyzék létrehozásához és kényelmes navigációhoz a dokumentumban.

Amikor egy jelentéshez, könyvhöz vagy cikkhez tartalomjegyzéket készítünk, fontos, hogy ne csak a fejezetek nevét ismerjük és hiperhivatkozásokat helyezzünk el rájuk, hanem azok helyét is a dokumentumban, hogy olvasáskor kényelmesebb legyen az könnyebben eligazodhatunk a dokumentumban, és könnyebben megtalálhatjuk a szükséges információkat.
A tartalomjegyzékhez kézzel is hozzáadhat lapszámokat, de ebben az esetben a számok hozzáadása meglehetősen sok időt vehet igénybe. Ezenkívül, ha megváltoztatja egy fejezet oldalszámát, az összes következő fejezet száma megváltozik, így ez a módszer nem nevezhető kényelmesnek és hatékonynak.

Tekintsük azokat a lehetőségeket, amelyek lehetővé teszik számunkra, hogy automatikusan megtudjuk a lapok és oldalak számát.
Mindenekelőtt azonban tisztázzuk, mi a különbség a munkalap és az oldal között az Excelben.
Az Excel-lap egy táblázat, amely sorokban és oszlopokban adatként strukturált cellákból áll, az oldal pedig a lap nyomtatható ábrázolása. Más szóval, a nyomtatási beállításoktól függően egy lap több oldalt is tartalmazhat, így külön megoldjuk a lap- és oldalszámok megtalálásának problémáját.

Lapszám képlet Excelben

Az Excel 2013-tól kezdve, egy szabvány LAP funkció, amely a meglévő hivatkozás lapszámát adja vissza.
Többben azonban korábbi verziók Excel szabvány Sajnos nincsenek függvények, ezért erre az esetre a VBA képességeit használjuk, és írunk egy egyedi függvényt, amely a lapnév alapján adja vissza a sorozatszámát.

SHEET függvény (Excel 2013-tól kezdve)

A függvény szintaxisa és leírása:

LAP(érték)
A megadott lap számát adja vissza.

• Jelentése (opcionális érv) — annak a lapnak vagy linknek a neve, amelynek lapszámát meg kell találnia.

Ha az argumentum nincs megadva, a függvény alapértelmezés szerint annak a lapnak a számát adja vissza, ahol a függvény meg van adva.
Nézzük meg a függvény használatának alapelveit példákon keresztül:

Ha függvényekkel dolgozik, vegye figyelembe, hogy a képletek =LAP("2. lap")És =LAP(A1)(ebben az esetben az A1 cella például a „Sheet2” szöveget tartalmazza) különböző eredményeket adhat vissza, mivel az első esetben az argumentum szövegként jelenik meg (lap keresése szövegnév alapján), a másodikban pedig egy hivatkozás (keressen egy lapot a hivatkozó cella alapján).

Lapszám a VBA-ban

Egyéni függvény létrehozásához lépjen a szerkesztőbe (Alt + F11), majd hozzon létre egy új modult, és adja hozzá a következő kódot:

Visual Basic

Függvény SheetNumber(SheetName As String) As Integer "SheetNumber függvény (SheetName argumentum szövegként) egész értékeket ad vissza" SheetNumber = Worksheets(SheetName).Index, amely egyenlővé teszi a lap indexszámát a visszatérési értékkel End Function

Most már használhatjuk ezt a funkciót számunkra kényelmes módon - vagy közvetlenül írja be a képletet bármelyik cellába, vagy használja a függvényvarázslót (a függvényt a kategóriában találja Felhasználó által meghatározott):

Oldalszám a VBA-ban

Beszúrhatunk fejlécek és láblécek használatával, de ha egy lapon egy cellához oldalszámot szeretnénk adni, akkor ez a módszer nem megfelelő. Használjuk a lap paramétert PageSetup.Pages.Count, amely egy adott lap nyomtatandó oldalainak számát határozza meg.
Az oldalszám keresésének algoritmusa a következő: két adott lap között elhelyezkedő összes lapra összesítjük a rajtuk lévő oldalak számát.
Visszatérünk a Visual Basic szerkesztőhöz, és hozzáadjuk a modulhoz új funkció:

Visual Basic

Függvény PageNumber(SheetName1 As String, SheetName2 As String) As Integer "Oldalszám függvény (SheetName1 és SheetName2 argumentumok szövegként) egész értékeket ad vissza. Dim FirstPage As Integer, LastPage As Integer = 0 " visszatérési érték = 0 FirstPage = Worksheets(SheetName1).Index "a kezdőlap számának lekérése LastPage = Worksheets(SheetName2).Index "a záró lapszám lekérése For i = FirstPage to LastPage - 1 "hurok a lapokon OldalNumber = OldalNumber + Sheets(i) .PageSetup.Pages.Count "hozzáadja az aktuális lap oldalainak számát a Next i End Function visszatérési értékéhez

Így az eredményül kapott függvény két, argumentumként megadott lap között megkeresi az oldalak számát.
Térjünk vissza az Excelhez, és írjunk be egy új képletet az oldalszámok számlálására:

Ugyanakkor a „3. rész” és a „4. rész” lapokon (az előző példából) további adatokat is hozzáadtunk, így a lap több oldalt is tartalmazott.

Sajnos ennek az oldalszám-meghatározási lehetőségnek a hátránya a függvény gyorsasága.
A PageSetup.Pages.Count művelet végrehajtásakor az Excel minden oldalhoz hozzáfér a nyomtató nyomtatási beállításaihoz, amelyek a könyveken nagy méretű hosszú számítási időt eredményez a függvény számára.

Számítási funkciók

Mivel a funkciók LapszámÉs Oldalszám csak a szöveges változóktól függenek, akkor ha a könyvben lévő lapok vagy oldalak száma megváltozik, a rendszer nem számítja automatikusan újra.
Az újraszámítási probléma részleges elkerülése érdekében hozzáadtuk a parancsot a függvénykódhoz (3. sor) Alkalmazás.Volatile Igaz, amely a cellák tartalmának megváltozásakor újraszámolja a függvény eredményét (hasonlóan a képletek F9 lenyomásával történő teljes újraszámításához).
Ha a függvény továbbra is jelentősen lelassítja a munkát, akkor ezt a sort kizárhatja a kódból, de ebben az esetben ne felejtse el megbizonyosodni arról, hogy a képletek újraszámításra kerülnek a dokumentum végső verziójában.

Ha a könyvben lévő lapok száma nő, és a navigáció problémássá válik, azt javaslom, hogy készítsen egy tartalomjegyzéket a könyvhöz, a szükséges lapokat.

beilleszteni a könyvbe üres lap

a „Hiperhivatkozás beszúrása” ablakban válassza ki, hogy mihez kívánja csatolni a hiperhivatkozást: „Hivatkozás egy helyre a dokumentumban”. Cella címe – a lap melyik cellájába kerül a kurzor. és válassza ki a helylapot Szingapúr. A „Szöveg” mezőben adja meg a lap nevét. A paraméterek kiválasztása után kattintson az OK gombra.

A cellában lévő szöveg megváltozott kinézet. Ez azt jelenti, hogy egy hiperhivatkozást hoztak létre hozzá. Ugyanígy állítsunk be hiperhivatkozásokat a könyv más lapjaira. Felhívjuk figyelmét, hogy a navigációhoz használt hiperhivatkozás színe megváltozik.

A kényelem kedvéért a könyv minden lapján létrehozhat visszamutató hivatkozásokat is, amelyek visszavezetnek a tartalomjegyzékhez. A tanulás elkerülése érdekében kézi alkotás hiperhivatkozásokat, majd minden lapra másolja őket, jobb egy másik módszert használni - a HYPERLINK funkciót.

A könyvben kijelöljük az összes lapot, amelyhez visszahivatkozást szeretnénk hozzáadni (a lapok tömeges kiválasztásához tartsa lenyomva a Ctrl billentyűt, és az egérrel válassza ki a kívánt lapot), és írja be a következő funkciót bármelyik megfelelő cellába:

Ez a függvény létrejön aktuális cella minden kiválasztott lapon található egy „Vissza a tartalomjegyzékhez” szövegű hiperhivatkozás, amelyre kattintva a felhasználó visszakerül a Tartalomjegyzék lapra.

Hogyan hozzunk létre gombot a menüben:

A menü látványosabbá tétele érdekében adjunk hozzá gombokat

Először hozzuk létre a jövő gomb alakját: Beszúrás → Alakzatok → Bármely alakzat kiválasztása:

Nyomtassuk ki az alakzatba a szöveget. Így rajzoltuk a gombot.

Válassza ki az alakzatot → lépjen a Beszúrás fülre → Hiperhivatkozás. Ezután rendeljen hozzá paramétereket az első bekezdésben leírtak szerint, majd kattintson az OK gombra. Hasonlóképpen létrehozhat más hivatkozásgombokat a könyv különböző lapjain. És adjon hozzá egy kis kreativitást a FORMAT menü segítségével

Helyezzen be egy üres lapot a munkafüzetbe, és a parancs segítségével adjon hozzá hivatkozásokat a szükséges lapokhoz Beszúrás – Hiperhivatkozás. A megnyíló ablakban válassza ki a bal oldali lehetőséget Helyezze el a dokumentumbanés állítsa be a külső szöveges megjelenítést és annak a cellának a címét, ahová a hivatkozás vezet:

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket, amelyek visszavezetnek a tartalomjegyzékhez. Annak érdekében, hogy ne kelljen manuálisan hiperhivatkozásokat létrehozni, majd minden lapra másolni, jobb egy másik módszert használni - a funkciót. HIPERLINK. A könyvben kijelöljük az összes lapot, amelyhez backlinket akarunk hozzáadni (a billentyűk segítségével tömegesen kiválaszthatja a lapokat Váltásés/vagy Ctrl) és bármelyik megfelelő cellába beírunk egy függvényt a következő formában:

Ez a funkció az összes kiválasztott lapon egy hiperhivatkozást hoz létre az aktuális cellában „Vissza a tartalomjegyzékhez” szöveggel, amelyre kattintva a felhasználó visszatér a lapra. Tartalomjegyzék.

2. módszer: Dinamikus tartalomjegyzék képletek használatával

Ez, bár kissé egzotikus, nagyon szép és kényelmes módja annak, hogy automatikus tartalomjegyzéket készítsen a könyvéhez. Egzotikus – mert dokumentálatlan XLM funkciót használ GET.WORKBOOK, amelyet a fejlesztők hagytak meg az Excel régebbi verzióival való kompatibilitás érdekében. Ez a függvény az aktuális munkafüzet összes lapjának listáját kiírja egy adott változóba, amelyből aztán kibonthatjuk és felhasználhatjuk a tartalomjegyzékünkben.

Nyisd ki Névkezelő a lapon Képletek (Képletek – Névkezelő)és hozzon létre egy új nevű tartományt, mondjuk Tartalomjegyzék. A terepen Tartomány (referencia)írja be ezt a képletet:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Most a változóban Tartalomjegyzék keresett neveinket tartalmazza. A funkció segítségével kivonhatja őket a lapra INDEX, amely számuk alapján „kihúzza” az elemeket a tömbből:

Funkció SOR számot ad aktuális vonalés ebben az esetben csak azért van rá szükség, hogy ne hozzunk létre kézzel külön oszlopot a kivont elemek sorszámaival (1,2,3...). Így az A1 cellában az első lap neve lesz, az A2-ben - a másodiké stb.

Nem rossz. Azonban, mint látható, a függvény nem csak a lap nevét adja vissza, hanem a munkafüzet nevét is, amire nincs szükségünk. Az eltávolításhoz a függvényeket használjuk CSERE (SUBST)És MEGTALÁLJA, amely megtalálja a záró szimbólumot szögletes zárójel(]), és cserélje ki az összes szöveget e karakterig és az üres karakterláncra (""). Nyissuk ki újra Névkezelő lapról Képletek (Képletek – Névkezelő), dupla kattintás nyissa meg a létrehozott tartományt Tartalomjegyzékés változtassa meg a képletét:

=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Most a laplistánk sokkal jobban fog kinézni:

Apró mellékes bonyodalom, hogy a képletünk egy elnevezett tartományba esik Tartalomjegyzék csak beíráskor kerül újraszámításra, vagy ha a könyvet egy billentyűkombináció megnyomásával újraszámítani kell Ctrl+Alt+F9. Hogy megkerüljük ezt a kellemetlen pillanatot, adjunk hozzá egy kis „farkot” a képlethez:

REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"") &T(TDATE())=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Funkció TDATE (MOST) megadja az aktuális dátumot (idővel együtt), és a függvényt Türessé változtatja ezt a dátumot szöveges karakterlánc, amelyet azután az összefűzési operátor (&) használatával összefűzünk a lapnevünkhöz. Azok. a lap neve valójában nem változik, hanem a függvény óta TDATEújraszámításra kerül, és új időpontot és dátumot produkál a lap bármilyen változásához, akkor képletünk többi része is újraszámításra kényszerül, és ennek eredményeként a lapok nevei folyamatosan frissülnek.

A hibák elrejtésére #REFERENCE (#REF), ami akkor jelenik meg, ha a képletünket a függvénnyel másoljuk INDEX tovább nagy mennyiség cellákban, mint lapjaink vannak, használhatjuk a függvényt IFERROR amely elkapja a hibákat, és lecseréli azokat az üres karakterláncra (""):

És végül, ha „élő” hivatkozásokat szeretne hozzáadni a lapnevekhez a gyors navigáció érdekében, ugyanezt a funkciót használhatja HIPERLINK, amely a lapnévből való áttérés címét fogja képezni:

3. módszer. Makró

Végül pedig egy egyszerű makróval is létrehozhat tartalomjegyzéket. Igaz, minden alkalommal le kell futtatnia, amikor a könyv szerkezete megváltozik - ellentétben 2. módszer, maga a makró nem követi őket.

Nyissa meg a Visual Basic Editort a gombra kattintva Alt+F11 vagy válassza ki (az Excel régebbi verzióiban) a menüből Eszközök – Makró – Visual Basic Editor(Eszközök – Makró – Visual Basic szerkesztő) . A megnyíló szerkesztő ablakban hozzon létre egy új üres modult (menü Beszúrás - Modul ), és másolja oda ennek a makrónak a szövegét:

Zárja be a Visual Ba szerkesztőt sic, és térjen vissza az Excelhez. Hozzáadás a könyvhöz Üres lapés helyezze az első helyre. Ezután kattintsonAlt+F8vagy nyissa meg a menütEszközök - Makró - Makrók. Keresse meg az ott létrehozott makrótLaplistaés futtasd. A makró létrehozza a hiperhivatkozások listáját lapnevekkel a munkafüzet első lapján. Bármelyikre kattintva a kívánt lapra jut.

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket is, amelyek visszavezetnek a tartalomjegyzékhez, amint azt a 1. módszer.

Utam. Az én verzióm

anyagok - =INDIRECT("""&B4&"""&"!E8")
vsp anyagok - =INDIRECT("""&B4&"""&"!E9")
és tovább az oszlopok mentén
=INDIRECT("""&B4&"""&"!E10")
=INDIRECT("""&B4&"""&"!E11")
=INDIRECT("""&B4&"""&"!E12")
=INDIRECT("""&B4&"""&"!E13")
=INDIRECT("""&B4&"""&"!E18")
=INDIRECT("""&B4&"""&"!E19")

Ha az Excel-munkafüzetben lévő lapok száma meghaladta a második tízet, akkor a lapok közötti navigáció problémát jelent. Az egyik gyönyörű módokon Ennek megoldása egy tartalomjegyzék létrehozása a könyv megfelelő lapjaihoz vezető hiperhivatkozásokkal:

Ennek megvalósítására többféle mód van.

Videó

Helyezze el a dokumentumban

HIPERLINK Váltásés/vagy Ctrl

Tartalomjegyzék.

Nyisd ki Névkezelő a lapon Tartalomjegyzék. A terepen Tartomány (referencia)írja be ezt a képletet:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Most a változóban Tartalomjegyzék INDEX

Funkció SOR

CSERE (SUBST)És MEGTALÁLJA Névkezelő lapról Tartalomjegyzékés változtassa meg a képletét:

Tartalomjegyzék Ctrl+Alt+F9

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())

Funkció TDATE (MOST) T TDATE

A hibák elrejtésére #REFERENCE (#REF) INDEXIFERROR

HIPERLINK

3. módszer. Makró

2. módszer

Alt+F11 Beszúrás - Modul

Sub SheetList() Lap halványítása munkalapként A cella tompítása az ActiveWorkbook tartományaként minden laphoz In ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Address:="", SubAddress:=""" & sheet.Név & """ & "!A1" cella.Formula = sheet. Name Következő Befejezés Al végével

Zárja be a Visual Basic szerkesztőt, és térjen vissza az Excelbe. Adjon hozzá egy üres lapot a könyvhöz, és helyezze el először. Ezután kattintson Alt+F8 vagy nyissa meg a menüt Laplista

1. módszer.

Kapcsolódó linkek

• Mi a makró, hogyan kell létrehozni, hova kell másolni a makró szövegét, hogyan kell futtatni a makrót?
• Könyv tartalomjegyzék automatikus létrehozása egyetlen gombbal (PLEX bővítmény)
• E-mail küldése a HYPERLINK funkcióval
• Gyors váltás a lapok között Excel munkafüzetek

1. módszer: Kézzel létrehozott hiperhivatkozások

Helyezzen be egy üres lapot a munkafüzetbe, és a parancs segítségével adjon hozzá hivatkozásokat a szükséges lapokhoz Beszúrás – Hiperhivatkozás. A megnyíló ablakban válassza ki a bal oldali lehetőséget Helyezze el a dokumentumbanés állítsa be a külső szöveges megjelenítést és annak a cellának a címét, ahová a hivatkozás vezet:

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket, amelyek visszavezetnek a tartalomjegyzékhez. Annak érdekében, hogy ne kelljen manuálisan hiperhivatkozásokat létrehozni, majd minden lapra másolni, jobb egy másik módszert használni - a funkciót. HIPERLINK. A könyvben kijelöljük az összes lapot, amelyhez backlinket akarunk hozzáadni (a billentyűk segítségével tömegesen kiválaszthatja a lapokat Váltásés/vagy Ctrl) és bármelyik megfelelő cellába beírunk egy függvényt a következő formában:

Ez a funkció az összes kiválasztott lapon egy hiperhivatkozást hoz létre az aktuális cellában „Vissza a tartalomjegyzékhez” szöveggel, amelyre kattintva a felhasználó visszatér a lapra. Tartalomjegyzék.

2. módszer: Dinamikus tartalomjegyzék képletek használatával

Ez, bár kissé egzotikus, nagyon szép és kényelmes módja annak, hogy automatikus tartalomjegyzéket készítsen a könyvéhez. Egzotikus – mert dokumentálatlan XLM funkciót használ GET.WORKBOOK, amelyet a fejlesztők hagytak meg az Excel régebbi verzióival való kompatibilitás érdekében. Ez a függvény az aktuális munkafüzet összes lapjának listáját kiírja egy adott változóba, amelyből aztán kibonthatjuk és felhasználhatjuk a tartalomjegyzékünkben.

Nyisd ki Névkezelő a lapon Képletek (Képletek – Névkezelő)és hozzon létre egy új nevű tartományt, mondjuk Tartalomjegyzék. A terepen Tartomány (referencia)írja be ezt a képletet:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Most a változóban Tartalomjegyzék keresett neveinket tartalmazza. A funkció segítségével kivonhatja őket a lapra INDEX, amely számuk alapján „kihúzza” az elemeket a tömbből:

Funkció SOR az aktuális sorszámot adja meg, és ebben az esetben csak azért van rá szükség, hogy ne hozzon létre manuálisan külön oszlopot a kivonatolt elemek sorozatszámaival (1,2,3...). Így az A1 cellában az első lap neve lesz, az A2-ben - a másodiké stb.

Nem rossz. Azonban, mint látható, a függvény nem csak a lap nevét adja vissza, hanem a munkafüzet nevét is, amire nincs szükségünk. Az eltávolításhoz a függvényeket használjuk CSERE (SUBST)És MEGTALÁLJA, amely megkeresi a záró szögletes zárójelet (]), és lecseréli az összes szöveget az adott karakterig bezárólag az üres karakterláncra (""). Nyissuk ki újra Névkezelő lapról Képletek (Képletek – Névkezelő), kattintson duplán a létrehozott tartomány megnyitásához Tartalomjegyzékés változtassa meg a képletét:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Most a laplistánk sokkal jobban fog kinézni:

Apró mellékes bonyodalom, hogy a képletünk egy elnevezett tartományba esik Tartalomjegyzék csak beíráskor kerül újraszámításra, vagy ha a könyvet egy billentyűkombináció megnyomásával újraszámítani kell Ctrl+Alt+F9. Hogy megkerüljük ezt a kellemetlen pillanatot, adjunk hozzá egy kis „farkot” a képlethez:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Funkció TDATE (MOST) megadja az aktuális dátumot (idővel együtt), és a függvényt T ezt a dátumot üres szöveggé alakítja, amelyet az összefűzési operátor (&) segítségével a munkalap nevéhez fűz. Azok. a lap neve valójában nem változik, hanem a függvény óta TDATEújraszámításra kerül, és új időpontot és dátumot produkál a lap bármilyen változásához, akkor képletünk többi része is újraszámításra kényszerül, és ennek eredményeként a lapok nevei folyamatosan frissülnek.

A hibák elrejtésére #REFERENCE (#REF), ami akkor jelenik meg, ha a képletünket a függvénnyel másoljuk INDEX több cellához, mint amennyi lapunk van, használhatjuk a függvényt IFERROR, amely elkapja a hibákat, és lecseréli azokat az üres karakterláncra (""):

És végül, ha „élő” hivatkozásokat szeretne hozzáadni a lapnevekhez a gyors navigáció érdekében, ugyanezt a funkciót használhatja HIPERLINK, amely a lapnévből való áttérés címét fogja képezni:

3. módszer. Makró

Végül pedig egy egyszerű makróval is létrehozhat tartalomjegyzéket. Igaz, minden alkalommal le kell futtatnia, amikor a könyv szerkezete megváltozik - ellentétben 2. módszer, maga a makró nem követi őket.

Nyissa meg a Visual Basic Editort a gombra kattintva Alt+F11 vagy válassza ki (az Excel régebbi verzióiban) a menüből Eszközök – Makró – Visual Basic Editor(Eszközök – Makró – Visual Basic szerkesztő). A megnyíló szerkesztő ablakban hozzon létre egy új üres modult (menü Beszúrás - Modul ), és másolja oda ennek a makrónak a szövegét:

Allaplista()
Dim lap Munkalapként
Dim cell As Range
ActiveWorkbook segítségével
Minden laphoz Az ActiveWorkbook.Worksheetsben
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
cella.Formula = lap.Név
Következő
Vége ezzel
End Sub Zárja be a Visual Basic szerkesztőt, és térjen vissza az Excelbe. Adjon hozzá egy üres lapot a könyvhöz, és helyezze el először. Ezután kattintson Alt+F8 vagy nyissa meg a menüt Eszközök - Makró - Makrók. Keresse meg az ott létrehozott makrót Laplistaés futtasd. A makró létrehozza a hiperhivatkozások listáját lapnevekkel a munkafüzet első lapján. Bármelyikre kattintva a kívánt lapra jut.

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket is, amelyek visszavezetnek a tartalomjegyzékhez, amint azt a 1. módszer.

Utam. Az én verzióm

T
Lap neve - =IFERROR(REPLACE(INDEX(Tartalomjegyzék,ROW()-3),1,KERESÉS("]",INDEX(Tartalomjegyzék,ROW()-3));"");"")

Dátum - =IFERROR(IF(INDIRECT("'"&B4&"'"&"!A1″)=0;"";INDIRECT("'"&B4&"'"&"!A1″));"")

Név - =INDIRECT(“’”&B4&”’”&”!A3″)

PO - =KÖZVETETT("'"&B4&"'"&"!E5")

fizetési adó - =KÖZVETETT(“‘”&B4&”‘”&”!E6″)

értékcsökkenés - =KÖZVETETT(“’”&B4&”’”&”!E7″)

anyagok - =KÖZVETLEN ("'"&B4&"'"&"!E8")
vsp anyagok - =KÖZVETLEN (“‘”&B4&”’”&”!E9″)

INDIRECT("'"&B4&"'"&"!E10")

INDIRECT("'"&B4&"'"&"!E11″)=INDIRECT("'"&B4&"'"&"!E12″)=INDIRECT("'"&B4&"'"&"!E13″)=INDIRECT( "'"&B4&"'"&"!E18")=INDIRECT("'"&B4&"'"&"!E19")

Ha az Excel-munkafüzetben lévő lapok száma meghaladta a második tízet, akkor a lapok közötti navigáció problémát jelent. Ennek egyik gyönyörű módja a tartalomjegyzék létrehozása a könyv megfelelő lapjaihoz vezető hiperhivatkozásokkal:

Ennek megvalósítására többféle mód van.

Videó

Helyezze el a dokumentumban

HIPERLINK Váltásés/vagy Ctrl

Tartalomjegyzék.

Nyisd ki Névkezelő a lapon Tartalomjegyzék. A terepen Tartomány (referencia)írja be ezt a képletet:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Most a változóban Tartalomjegyzék INDEX

Funkció SOR

CSERE (SUBST)És MEGTALÁLJA Névkezelő lapról Tartalomjegyzékés változtassa meg a képletét:

Tartalomjegyzék Ctrl+Alt+F9

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())

Funkció TDATE (MOST) T TDATE

A hibák elrejtésére #REFERENCE (#REF) INDEXIFERROR

HIPERLINK

3. módszer. Makró

2. módszer

Alt+F11 Beszúrás - Modul

Sub SheetList() Lap halványítása munkalapként A cella tompítása az ActiveWorkbook tartományaként minden laphoz In ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Address:="", SubAddress:=""" & sheet.Név & """ & "!A1" cella.Formula = sheet. Name Következő Befejezés Al végével

Zárja be a Visual Basic szerkesztőt, és térjen vissza az Excelbe. Adjon hozzá egy üres lapot a könyvhöz, és helyezze el először. Ezután kattintson Alt+F8 vagy nyissa meg a menüt Laplista

1. módszer.

Kapcsolódó linkek

• Mi a makró, hogyan kell létrehozni, hova kell másolni a makró szövegét, hogyan kell futtatni a makrót?
• Könyv tartalomjegyzék automatikus létrehozása egyetlen gombbal (PLEX bővítmény)
• E-mail küldése a HYPERLINK funkcióval
• Gyorsan válthat a munkalapok között egy Excel-munkafüzetben

1. módszer: Kézzel létrehozott hiperhivatkozások

Helyezzen be egy üres lapot a munkafüzetbe, és a parancs segítségével adjon hozzá hivatkozásokat a szükséges lapokhoz Beszúrás – Hiperhivatkozás. A megnyíló ablakban válassza ki a bal oldali lehetőséget Helyezze el a dokumentumbanés állítsa be a külső szöveges megjelenítést és annak a cellának a címét, ahová a hivatkozás vezet:

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket, amelyek visszavezetnek a tartalomjegyzékhez. Annak érdekében, hogy ne kelljen manuálisan hiperhivatkozásokat létrehozni, majd minden lapra másolni, jobb egy másik módszert használni - a funkciót. HIPERLINK. A könyvben kijelöljük az összes lapot, amelyhez backlinket akarunk hozzáadni (a billentyűk segítségével tömegesen kiválaszthatja a lapokat Váltásés/vagy Ctrl) és bármelyik megfelelő cellába beírunk egy függvényt a következő formában:

Ez a funkció az összes kiválasztott lapon egy hiperhivatkozást hoz létre az aktuális cellában „Vissza a tartalomjegyzékhez” szöveggel, amelyre kattintva a felhasználó visszatér a lapra. Tartalomjegyzék.

2. módszer: Dinamikus tartalomjegyzék képletek használatával

Ez, bár kissé egzotikus, nagyon szép és kényelmes módja annak, hogy automatikus tartalomjegyzéket készítsen a könyvéhez. Egzotikus – mert dokumentálatlan XLM funkciót használ GET.WORKBOOK, amelyet a fejlesztők hagytak meg az Excel régebbi verzióival való kompatibilitás érdekében. Ez a függvény az aktuális munkafüzet összes lapjának listáját kiírja egy adott változóba, amelyből aztán kibonthatjuk és felhasználhatjuk a tartalomjegyzékünkben.

Nyisd ki Névkezelő a lapon Képletek (Képletek – Névkezelő)és hozzon létre egy új nevű tartományt, mondjuk Tartalomjegyzék. A terepen Tartomány (referencia)írja be ezt a képletet:

GET.WORK.BOOK(1)
=GET.WORKBOOK(1)

Most a változóban Tartalomjegyzék keresett neveinket tartalmazza. A funkció segítségével kivonhatja őket a lapra INDEX, amely számuk alapján „kihúzza” az elemeket a tömbből:

Funkció SOR az aktuális sorszámot adja meg, és ebben az esetben csak azért van rá szükség, hogy ne hozzon létre manuálisan külön oszlopot a kivonatolt elemek sorozatszámaival (1,2,3...). Így az A1 cellában az első lap neve lesz, az A2-ben - a másodiké stb.

Nem rossz. Azonban, mint látható, a függvény nem csak a lap nevét adja vissza, hanem a munkafüzet nevét is, amire nincs szükségünk. Az eltávolításhoz a függvényeket használjuk CSERE (SUBST)És MEGTALÁLJA, amely megkeresi a záró szögletes zárójelet (]), és lecseréli az összes szöveget az adott karakterig bezárólag az üres karakterláncra (""). Nyissuk ki újra Névkezelő lapról Képletek (Képletek – Névkezelő), kattintson duplán a létrehozott tartomány megnyitásához Tartalomjegyzékés változtassa meg a képletét:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Most a laplistánk sokkal jobban fog kinézni:

Apró mellékes bonyodalom, hogy a képletünk egy elnevezett tartományba esik Tartalomjegyzék csak beíráskor kerül újraszámításra, vagy ha a könyvet egy billentyűkombináció megnyomásával újraszámítani kell Ctrl+Alt+F9. Hogy megkerüljük ezt a kellemetlen pillanatot, adjunk hozzá egy kis „farkot” a képlethez:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Funkció TDATE (MOST) megadja az aktuális dátumot (idővel együtt), és a függvényt T ezt a dátumot üres szöveggé alakítja, amelyet az összefűzési operátor (&) segítségével a munkalap nevéhez fűz. Azok. a lap neve valójában nem változik, hanem a függvény óta TDATEújraszámításra kerül, és új időpontot és dátumot produkál a lap bármilyen változásához, akkor képletünk többi része is újraszámításra kényszerül, és ennek eredményeként a lapok nevei folyamatosan frissülnek.

A hibák elrejtésére #REFERENCE (#REF), ami akkor jelenik meg, ha a képletünket a függvénnyel másoljuk INDEX több cellához, mint amennyi lapunk van, használhatjuk a függvényt IFERROR, amely elkapja a hibákat, és lecseréli azokat az üres karakterláncra (""):

És végül, ha „élő” hivatkozásokat szeretne hozzáadni a lapnevekhez a gyors navigáció érdekében, ugyanezt a funkciót használhatja HIPERLINK, amely a lapnévből való áttérés címét fogja képezni:

3. módszer. Makró

Végül pedig egy egyszerű makróval is létrehozhat tartalomjegyzéket. Igaz, minden alkalommal le kell futtatnia, amikor a könyv szerkezete megváltozik - ellentétben 2. módszer, maga a makró nem követi őket.

Nyissa meg a Visual Basic Editort a gombra kattintva Alt+F11 vagy válassza ki (az Excel régebbi verzióiban) a menüből Eszközök – Makró – Visual Basic Editor(Eszközök – Makró – Visual Basic szerkesztő). A megnyíló szerkesztő ablakban hozzon létre egy új üres modult (menü Beszúrás - Modul ), és másolja oda ennek a makrónak a szövegét:

Allaplista()
Dim lap Munkalapként
Dim cell As Range
ActiveWorkbook segítségével
Minden laphoz Az ActiveWorkbook.Worksheetsben
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
cella.Formula = lap.Név
Következő
Vége ezzel
End Sub Zárja be a Visual Basic szerkesztőt, és térjen vissza az Excelbe. Adjon hozzá egy üres lapot a könyvhöz, és helyezze el először. Ezután kattintson Alt+F8 vagy nyissa meg a menüt Eszközök - Makró - Makrók. Keresse meg az ott létrehozott makrót Laplistaés futtasd. A makró létrehozza a hiperhivatkozások listáját lapnevekkel a munkafüzet első lapján. Bármelyikre kattintva a kívánt lapra jut.

A kényelem kedvéért a könyv összes lapján létrehozhat linkeket is, amelyek visszavezetnek a tartalomjegyzékhez, amint azt a 1. módszer.

Utam. Az én verzióm

T
Lap neve - =IFERROR(REPLACE(INDEX(Tartalomjegyzék,ROW()-3),1,KERESÉS("]",INDEX(Tartalomjegyzék,ROW()-3));"");"")

Dátum - =IFERROR(IF(INDIRECT("'"&B4&"'"&"!A1″)=0;"";INDIRECT("'"&B4&"'"&"!A1″));"")

Név - =INDIRECT(“’”&B4&”’”&”!A3″)

PO - =KÖZVETETT("'"&B4&"'"&"!E5")

fizetési adó - =KÖZVETETT(“‘”&B4&”‘”&”!E6″)

értékcsökkenés - =KÖZVETETT(“’”&B4&”’”&”!E7″)

anyagok - =KÖZVETLEN ("'"&B4&"'"&"!E8")
vsp anyagok - =KÖZVETLEN (“‘”&B4&”’”&”!E9″)

INDIRECT("'"&B4&"'"&"!E10")

INDIRECT("'"&B4&"'"&"!E11″)=INDIRECT("'"&B4&"'"&"!E12″)=INDIRECT("'"&B4&"'"&"!E13″)=INDIRECT( "'"&B4&"'"&"!E18")=INDIRECT("'"&B4&"'"&"!E19")

Alapértelmezett Microsoft Excel nem eredményez látható lapszámozást. Ugyanakkor sok esetben, különösen, ha a dokumentumot nyomtatásra küldik, számozni kell. Az Excel ezt fejlécek és láblécek segítségével teszi lehetővé. Nézzük meg a különböző lehetőségeket a lapok számozására ebben az alkalmazásban.

Számozás Excelben

Az Excelben fejlécek és láblécek segítségével számozhatja az oldalakat. Alapértelmezés szerint el vannak rejtve, a lap alsó és felső részén találhatók. Sajátosságuk az, hogy benne van ez a terület A rekordok végpontok, azaz a dokumentum minden oldalán megjelennek.

1. módszer: szabályos számozás

A szokásos számozás magában foglalja a dokumentum összes lapjának számozását.

1. Először is engedélyeznie kell a fejlécek és láblécek megjelenítését. Lépjen a „Beszúrás” fülre.
2. A szalagon, a „Szöveg” eszközblokkban kattintson a „Fejléc és lábléc” gombra.
3. Ezt követően az Excel jelölő módba lép, és a fejlécek és láblécek megjelennek a lapokon. A felső és az alsó területen helyezkednek el. Ezenkívül mindegyik három részre oszlik. Kiválasztjuk, hogy melyik láblécben, illetve annak melyik részén történjen a számozás. A legtöbb esetben válassza ki a fejléc bal oldalát. Kattintson arra a részre, ahová a számot el szeretné helyezni.
4. A „Fejléc és lábléc kezelése” további lapok blokkjának „Tervező” lapján kattintson az „Oldalszám” gombra, amely a „Fejléc és lábléc elemek” eszközcsoportban található szalagon található.
5. Amint látja, megjelenik egy speciális „&” címke. Ha egy adott sorozatszámra szeretné konvertálni, kattintson a dokumentum bármely területére.
6. Most az Excel dokumentum minden oldalán megjelent egy sorszám. Formázható, hogy látványosabbnak tűnjön és kiemelkedjen az általános háttérből. Ehhez válassza ki a bejegyzést a láblécben, és vigye a kurzort fölé. Megjelenik egy formázási menü, amelyben a következő műveleteket hajthatja végre:
   • betűtípus módosítása;
   • dőlt vagy félkövér;
   • átméretezés;
   • színét változtatni.

   Válassza ki a végrehajtani kívánt műveleteket a szám vizuális megjelenítésének megváltoztatásához, amíg el nem éri az Önt kielégítő eredményt.

2. módszer: számozás, amely jelzi az összes lap számát

Az oldalakat Excelben is számozhatja, jelezve az egyes lapokon található oldalak számát.

1. Aktiváljuk a számozás megjelenítését, az előző módszer szerint.
2. A címke elé írjuk az „Oldal” szót, utána pedig a „from” szót.
3. Helyezze a kurzort a lábléc mezőbe a „tól” szó után. Kattintson az „Oldalok száma” gombra, amely a „Kezdőlap” fülön található szalagon található.
4. Kattintson bárhová a dokumentumban, hogy a címkék helyett értékek jelenjenek meg.

Most már nem csak az aktuális lapszámról jelenítünk meg információkat, hanem a teljes számról is.

3. módszer: számozás a második oldaltól

Vannak esetek, amikor nem szükséges a teljes dokumentumot számozni, hanem csak egy bizonyos helytől kezdve. Találjuk ki, hogyan kell ezt megtenni.

Annak érdekében, hogy a számozást a második oldalról állítsuk be, és ez megfelelő például absztraktok, szakdolgozatok, ill. tudományos munkák amikor bekapcsolva Címlap A számok jelenléte nem megengedett, az alábbiakban jelzett műveleteket kell végrehajtania.

1. Váltsunk lábléc módra. Ezután lépjen a „Fejléc- és lábléctervező” fülre, amely a „Fejléc és lábléc használata” lapblokkban található.
2. A szalag „Opciók” eszközblokkjában jelölje be a „Speciális fejléc és lábléc az első oldalhoz” beállítási lehetőséget.
3. A számozást az „Oldalszám” gombbal állítjuk be, ahogy fent már látható, de ezt minden oldalon megtesszük, kivéve az elsőt.

Amint látja, ezután minden lap számozott, kivéve az elsőt. Ezenkívül az első oldalt figyelembe veszik a többi lap számozása során, de maga a szám nem jelenik meg rajta.

4. módszer: számozás a megadott oldaltól

Ugyanakkor vannak olyan helyzetek, amikor a dokumentumnak nem az első oldalról kell kezdődnie, hanem például a harmadik vagy a hetedik oldalról. Ilyen igény ritkán fordul elő, de ennek ellenére néha a feltett kérdés is megoldást kíván.

1. A számozást a szokásos módon, a szalag megfelelő gombjával végezzük, Részletes leírás amit fent adtak.
2. Lépjen az „Oldalelrendezés” fülre.
3. Az Oldalbeállítás eszköztár bal alsó sarkában található szalagon egy ferde nyíl alakú ikon található. rákattintunk.
4. Megnyílik a paraméterablak, lépjen az „Oldal” fülre, ha másik lapon volt megnyitva. Az „Első oldal száma” paramétermezőbe írja be azt a számot, amelyből számozni szeretne. Kattintson az „OK” gombra.

Mint látható, ezt követően a dokumentum tényleges első oldalának száma a paraméterekben megadottra változott. Ennek megfelelően a következő lapok számozása is eltolódott.

Lecke: Fejlécek és láblécek eltávolítása az Excelben

Számozd meg a táblázat oldalait! Excel processzor nagyon egyszerű. Ezt az eljárást a fejléc és lábléc mód bekapcsolásával hajtják végre. Ezenkívül a felhasználó személyre szabhatja a számozást: formázhatja a szám megjelenítését, hozzáadhatja a dokumentum teljes lapszámának jelzését, számozást egy adott helyről stb.

Örülünk, hogy tudtunk segíteni a probléma megoldásában.

Tegye fel kérdését a megjegyzésekben, részletesen leírva a probléma lényegét. Szakértőink megpróbálnak a lehető leggyorsabban válaszolni.