Formula pentru numărul de foi și pagină în Excel. Formula pentru numerele foilor și paginilor în Excel Cum se creează conținut în Excel

Să analizăm formulele Numerele foilor și paginilor în Excel(atât funcții standard, cât și personalizate) pentru a crea un cuprins și o navigare convenabilă prin document.

Atunci când creăm un cuprins pentru un raport, carte sau articol, este important pentru noi să cunoaștem nu numai numele capitolelor și să punem hyperlinkuri către acestea, ci și locația lor în document, astfel încât atunci când citim să fie mai convenabil pentru pentru a naviga prin conținutul documentului și pentru a găsi mai ușor informațiile de care avem nevoie.
Puteți adăuga manual numere de foi la cuprins, dar în acest caz procesul de adăugare a numerelor poate dura destul de mult. În plus, dacă modificați numărul paginii unui capitol, numerele pentru toate capitolele următoare se vor schimba, astfel încât această metodă nu poate fi numită convenabilă și eficientă.

Să luăm în considerare opțiunile care ne vor permite să aflăm automat numărul de foi și pagini.
Cu toate acestea, în primul rând, să înțelegem care este exact diferența dintre o foaie și o pagină în Excel.
O foaie Excel este un tabel format din celule structurate ca date în rânduri și coloane, iar o pagină este o reprezentare imprimabilă a foii. Cu alte cuvinte, în funcție de setările dvs. de imprimare, o coală poate conține mai multe pagini, așa că vom rezolva separat problema găsirii numerelor foilor și paginilor.

Formula numărului foii în Excel

Începând cu Excel 2013, un standard Funcția SHEET, care returnează numărul foii de la linkul existent.
Cu toate acestea, în mai mult versiuni anterioare Standard Excel Din păcate, nu există funcții, așa că în acest caz vom folosi capabilitățile VBA și vom scrie o funcție personalizată care va returna numărul de serie pe baza numelui foii.

Funcția SHEET (începând cu Excel 2013)

Sintaxa și descrierea funcției:

SHEET(valoare)
Returnează numărul foii specificate.

• Sens (argument opțional) — numele foii sau link-ul pentru care trebuie să aflați numărul foii.

Dacă argumentul nu este specificat, funcția returnează în mod implicit numărul foii în care este specificată funcția.
Să ne uităm la principiile de utilizare a funcției folosind exemple:

Când lucrați cu o funcție, rețineți că formulele =Foaie(„Foaie2”)Și =FOAIA(A1)(în acest caz, celula A1, de exemplu, conține textul „Sheet2”) poate returna rezultate diferite, deoarece în primul caz argumentul este prezentat ca text (căutați o foaie după numele textului), iar în al doilea - ca text un link (căutați o foaie după celula de referință).

Numărul foii în VBA

Pentru a crea o funcție personalizată, mergeți la editor (Alt + F11), apoi creați un nou modul și adăugați următorul cod la acesta:

Visual Basic

Funcția SheetNumber(SheetName As String) As Integer „Funcția SheetNumber (argument SheetName ca text) care returnează valori întregi SheetNumber = Foi de lucru (SheetName).

Acum putem folosi această funcțieîntr-un mod convenabil pentru noi - fie introduceți direct formula în orice celulă, fie utilizați expertul pentru funcții (puteți găsi funcția în categoria Definit de utilizator):

Numărul paginii în VBA

Puteți insera folosind anteturi și subsoluri, dar dacă dorim să adăugăm un număr de pagină la o celulă de pe o foaie, atunci această metodă nu este potrivită. Să folosim parametrul foaie PageSetup.Pages.Count, care determină numărul de pagini dintr-o anumită coală de imprimat.
Algoritmul pentru căutarea unui număr de pagină este următorul: pentru toate foile situate între două foi date, însumăm numărul de pagini de pe ele.
Ne întoarcem la editorul Visual Basic și îl adăugăm la modul optiune noua:

Visual Basic

Funcția PageNumber(SheetName1 As String, SheetName2 As String) As Integer „Funcția PageNumber (SheetName1 și SheetName2 argumente ca text) care returnează valori întregi Dim FirstPage As Integer, LastPage As Integer „inițializarea aplicației. = 0 " valoare returnată = 0 FirstPage = Foi de lucru (SheetName1). Index "obținerea numărului de început al foii LastPage = Foi de lucru (SheetName2). Index "obținerea numărului de final al foii For i = FirstPage To LastPage - 1 "buclă prin foi PageNumber = PageNumber + Sheets(i) .PageSetup.Pages.Count "adaugă numărul de pagini ale foii curente la valoarea de returnare a funcției Next i End

Astfel, funcția rezultată caută numărul de pagini dintre două foi, care sunt date ca argumente.
Să revenim la Excel și să introducem o nouă formulă pentru numărarea numerelor de pagină:

În același timp, pe foile „Partea 3” și „Partea 4” (din exemplul precedent), am adăugat suplimentar date, astfel încât foaia a început să includă mai multe pagini.

Din păcate, dezavantajul acestei opțiuni pentru determinarea numărului paginii este viteza funcției.
La efectuarea operației PageSetup.Pages.Count, Excel accesează setările de imprimare ale imprimantei pentru fiecare pagină, care pe cărți marime mare conduce la un timp mare de calcul al funcției.

Caracteristici de calcul

Din moment ce funcţiile SheetNumberȘi Numărul paginii depind doar de variabilele text, apoi atunci când se modifică numărul de foi sau pagini din carte, acestea nu sunt recalculate automat.
Pentru a evita parțial problema de recalculare, am adăugat comanda la codul funcției (linia 3) Aplicație.Volatil Adevărat, care recalculează rezultatul funcției atunci când conținutul celulelor se modifică (analog cu recalcularea completă a formulelor prin apăsarea F9).
Dacă funcția încă încetinește semnificativ activitatea, atunci puteți exclude această linie din cod, dar în acest caz, nu uitați să vă asigurați că formulele sunt recalculate în versiunea finală a documentului.

Când numărul de foi din carte crește și navigarea prin aceasta devine problematică, vă sugerez să creați un cuprins pentru carte, cu link-uri către foile necesare.

introduceți în carte foaie albă

în fereastra „Insert Hyperlink”, selectați la ce să conectați hyperlinkul: „Link către un loc din document”. Adresa celulei - în ce celulă a foii va fi mutat cursorul. și selectați foaia de locație Singapore. În câmpul „Text”, indicați numele foii. După selectarea parametrilor, faceți clic pe OK.

Textul din celulă s-a schimbat aspect. Aceasta înseamnă că a fost creat un hyperlink pentru acesta. Să creăm hyperlinkuri către alte foi din carte în același mod. Vă rugăm să rețineți că hyperlinkul utilizat pentru navigare își schimbă culoarea.

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul. Pentru a evita studiul crearea manuală hyperlinkuri și apoi copierea lor în fiecare foaie, este mai bine să folosiți o altă metodă - funcția HYPERLINK.

Selectăm toate foile din carte în care dorim să adăugăm un link înapoi (pentru a selecta în masă foile, trebuie să țineți apăsată tasta Ctrl și să selectați foile dorite cu mouse-ul) și să introduceți următoarea funcție în orice celulă adecvată:

Această funcție va crea în celula curentă pe toate foile selectate există un hyperlink cu textul „Înapoi la Cuprins”, făcând clic pe care va reveni utilizatorul la foaia Cuprins.

Cum se creează un buton într-un meniu:

Pentru a face meniul mai plăcut din punct de vedere vizual, să adăugăm butoane

Mai întâi, să creăm forma butonului viitor: Inserare → Forme → Selectați orice formă:

Să tipărim textul în interiorul formei. Așa am desenat butonul.

Selectați forma → accesați fila Inserare → Hyperlink. Apoi, atribuiți-i parametri ca în primul paragraf și faceți clic pe OK. În mod similar, puteți crea alte butoane de hyperlink pe diferite foi ale cărții. Și adăugați puțină creativitate folosind meniul FORMAT

Introduceți o foaie goală în registrul de lucru și adăugați hyperlinkuri la foile de care aveți nevoie folosind comanda Inserare - Hyperlink. În fereastra care se deschide, selectați opțiunea din stânga Pune în documentși setați afișarea textului extern și adresa celulei la care va duce linkul:

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul. Pentru a nu fi nevoie să creați manual hyperlinkuri și apoi să le copiați în fiecare foaie, este mai bine să utilizați o altă metodă - funcția HYPERLINK. Selectăm toate foile din carte în care dorim să adăugăm un backlink (puteți folosi tastele pentru a selecta în masă foile Schimbși/sau Ctrl) și în orice celulă adecvată introducem o funcție de următoarea formă:

Această funcție va crea un hyperlink în celula curentă pe toate foile selectate cu textul „Înapoi la Cuprins”, făcând clic pe care va reveni utilizatorul la foaie Cuprins.

Metoda 2: Cuprins dinamic folosind formule

Aceasta, deși ușor exotică, este o modalitate foarte frumoasă și convenabilă de a crea un cuprins automat pentru cartea dvs. Exotic - deoarece folosește o caracteristică XLM nedocumentată GET.WORKBOOK, lăsat de dezvoltatori pentru compatibilitate cu versiunile mai vechi de Excel. Această funcție aruncă o listă cu toate foile din registrul de lucru curent într-o variabilă dată, din care apoi le putem extrage și le putem folosi în cuprinsul nostru.

Deschis Manager de nume pe filă Formule (Formule – Manager de nume)și creați o nouă gamă numită, să spunem Cuprins. În câmp Interval (referință) introduceți această formulă:

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

Acum în variabilă Cuprins conține numele noastre căutate. Pentru a le extrage de acolo în foaie, puteți folosi funcția INDEX, care „extrage” elemente din matrice după numărul lor:

Funcţie RÂND dă un număr linia curentăși, în acest caz, este nevoie doar pentru a nu crea manual o coloană separată cu numerele de serie ale elementelor extrase (1,2,3...). Astfel, în celula A1 vom avea numele primei foi, în A2 - numele celei de-a doua etc.

Nu-i rău. Cu toate acestea, după cum puteți vedea, funcția returnează nu numai numele foii, ci și numele registrului de lucru, de care nu avem nevoie. Pentru a-l elimina, folosim funcțiile ÎNLOCUIRE (SUBST)Și GĂSI, care va găsi simbolul de închidere paranteza pătrată(]) și înlocuiți tot textul până la și inclusiv acest caracter cu șirul gol (""). Să-l deschidem din nou Manager de nume din fila Formule (Formule - Manager de nume), dublu click deschide intervalul creat Cuprinsși schimbați formula:

=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;GĂSĂ("]";OBȚINE.CARTEA DE LUCRU(1));"")

Acum lista noastră de foi va arăta mult mai bine:

O mică complicație secundară este că formula noastră se află într-un interval numit Cuprins va fi recalculat numai atunci când este introdus, sau când cartea este forțată să se recalculeze prin apăsarea unei combinații de taste Ctrl+Alt+F9. Pentru a ocoli acest moment neplăcut, să adăugăm o mică „coadă” formulei noastre:

ÎNLOCUIȚI(GET.CARTEA DE LUCRU(1);1;GĂSĂ("]";GET.CARTEA DE LUCRU(1));"") &T(TDATE())=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;Găsește("]";OBȚINE.CARTEA DE LUCRU(1));"")&T(ACUM())

Funcţie TDATE (ACUM) oferă data curentă (cu ora) și funcția T transformă această dată în gol șir de text, care este apoi concatenat la numele foii noastre folosind operatorul de concatenare (&). Acestea. numele foii nu se schimba de fapt, ci din moment ce functia TDATE este recalculat și produce o nouă oră și dată pentru orice modificare a foii, apoi restul formulei noastre va fi forțat să recalculeze și - ca urmare - numele foilor vor fi actualizate constant.

Pentru a ascunde erorile #REFERINȚĂ (#REF), care va apărea dacă ne copiem formula cu funcția INDEX pe cantitate mare celule decât avem foi, putem folosi funcția DACA EROARE care prinde orice erori și le înlocuiește cu șirul gol (""):

Și, în sfârșit, pentru a adăuga hyperlinkuri „în direct” la numele foilor pentru o navigare rapidă, puteți utiliza aceeași funcție HYPERLINK, care va forma adresa pentru trecerea de la numele foii:

Metoda 3. Macro

Și, în sfârșit, puteți utiliza o macrocomandă simplă pentru a crea un cuprins. Adevărat, va trebui să-l rulați de fiecare dată când structura cărții se schimbă - spre deosebire de Metoda 2, macro-ul în sine nu le urmărește.

Deschideți Editorul Visual Basic făcând clic Alt+F11 sau selectând (în versiunile mai vechi de Excel) din meniu Instrumente - Macro - Editor Visual Basic(Instrumente - Macro - Editor Visual Basic) . În fereastra editorului care se deschide, creați un nou modul gol (meniu Inserare - Modul ) și copiați acolo textul acestei macrocomenzi:

Închideți Editorul Visual Ba sic și reveniți la Excel. Adaugă la carte Foaie albăși pune-l pe primul loc. Apoi apasaAlt+F8sau deschide meniulInstrumente - Macro - Macro. Găsiți macrocomanda pe care ați creat-o acoloSheetListși rulați-l. Macrocomanda va crea o listă de hyperlinkuri cu nume de foi pe prima foaie a registrului de lucru. Făcând clic pe oricare dintre ele, veți ajunge la foaia dorită.

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul, așa cum este descris în Metoda 1.

Felul meu. Versiunea mea

materiale - =INDIRECT("""&B4&"""&"!E8")
materiale vsp - =INDIRECT("""&B4&"""&"!E9")
iar mai departe de-a lungul coloanelor
=INDIRECT("""&B4&"""&"!E10")
=INDIRECT("""&B4&"""&"!E11")
=INDIRECT("""&B4&"""&"!E12")
=INDIRECT("""&B4&"""&"!E13")
=INDIRECT("""&B4&"""&"!E18")
=INDIRECT("""&B4&"""&"!E19")

Dacă numărul de foi din registrul de lucru Excel a depășit al doilea zece, atunci navigarea printre foi începe să devină o problemă. Unul dintre moduri frumoase Soluția sa este de a crea un cuprins cu hyperlinkuri care să conducă la foile corespunzătoare ale cărții:

Există mai multe moduri de a implementa acest lucru.

Video

Pune în document

HYPERLINK Schimbși/sau Ctrl

Cuprins.

Deschis Manager de nume pe filă Cuprins. În câmp Interval (referință) introduceți această formulă:

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

Acum în variabilă Cuprins INDEX

Funcţie RÂND

ÎNLOCUIRE (SUBST)Și GĂSI Manager de nume din fila Cuprinsși schimbați formula:

Cuprins Ctrl+Alt+F9

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

Funcţie TDATE (ACUM) T TDATE

Pentru a ascunde erorile #REFERINȚĂ (#REF) INDEXDACA EROARE

HYPERLINK

Metoda 3. Macro

Metoda 2

Alt+F11 Inserare - Modul

Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook Pentru fiecare foaie din ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name Next End With End Sub

Închideți Visual Basic Editor și reveniți la Excel. Adăugați o foaie goală la carte și plasați-o mai întâi. Apoi apasa Alt+F8 sau deschide meniul SheetList

Metoda 1.

Linkuri conexe

• Ce este o macrocomandă, cum să o creez, unde să copiați textul macrocomenzii, cum să rulați macrocomandă?
• Creați automat un cuprins de carte cu un singur buton (complet PLEX)
• Trimiterea de e-mailuri folosind funcția HYPERLINK
• Tranziție rapidă între foi Caiete de lucru Excel

Metoda 1: Hyperlinkuri create manual

Introduceți o foaie goală în registrul de lucru și adăugați hyperlinkuri la foile de care aveți nevoie folosind comanda Inserare - Hyperlink. În fereastra care se deschide, selectați opțiunea din stânga Pune în documentși setați afișarea textului extern și adresa celulei la care va duce linkul:

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul. Pentru a nu fi nevoie să creați manual hyperlinkuri și apoi să le copiați în fiecare foaie, este mai bine să utilizați o altă metodă - funcția HYPERLINK. Selectăm toate foile din carte în care dorim să adăugăm un backlink (puteți folosi tastele pentru a selecta în masă foile Schimbși/sau Ctrl) și în orice celulă adecvată introducem o funcție de următoarea formă:

Această funcție va crea un hyperlink în celula curentă pe toate foile selectate cu textul „Înapoi la Cuprins”, făcând clic pe care va reveni utilizatorul la foaie Cuprins.

Metoda 2: Cuprins dinamic folosind formule

Aceasta, deși ușor exotică, este o modalitate foarte frumoasă și convenabilă de a crea un cuprins automat pentru cartea dvs. Exotic - deoarece folosește o caracteristică XLM nedocumentată GET.WORKBOOK, lăsat de dezvoltatori pentru compatibilitate cu versiunile mai vechi de Excel. Această funcție aruncă o listă cu toate foile din registrul de lucru curent într-o variabilă dată, din care apoi le putem extrage și le putem folosi în cuprinsul nostru.

Deschis Manager de nume pe filă Formule (Formule – Manager de nume)și creați o nouă gamă numită, să spunem Cuprins. În câmp Interval (referință) introduceți această formulă:

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

Acum în variabilă Cuprins conține numele noastre căutate. Pentru a le extrage de acolo în foaie, puteți folosi funcția INDEX, care „extrage” elemente din matrice după numărul lor:

Funcţie RÂND oferă numărul de rând curent și, în acest caz, este necesar doar pentru a evita crearea manuală a unei coloane separate cu numerele de serie ale elementelor care sunt extrase (1,2,3...). Astfel, în celula A1 vom avea numele primei foi, în A2 - numele celei de-a doua etc.

Nu-i rău. Cu toate acestea, după cum puteți vedea, funcția returnează nu numai numele foii, ci și numele registrului de lucru, de care nu avem nevoie. Pentru a-l elimina, folosim funcțiile ÎNLOCUIRE (SUBST)Și GĂSI, care va găsi caracterul de închidere paranteză (]) și va înlocui tot textul până la și inclusiv acel caracter cu șirul gol (""). Să-l deschidem din nou Manager de nume din fila Formule (Formule - Manager de nume), faceți dublu clic pentru a deschide intervalul creat Cuprinsși schimbați formula:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"")
=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;GĂSĂ("]";OBȚINE.CARTEA DE LUCRU(1));"")

Acum lista noastră de foi va arăta mult mai bine:

O mică complicație secundară este că formula noastră se află într-un interval numit Cuprins va fi recalculat numai atunci când este introdus, sau când cartea este forțată să se recalculeze prin apăsarea unei combinații de taste Ctrl+Alt+F9. Pentru a ocoli acest moment neplăcut, să adăugăm o mică „coadă” formulei noastre:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;Găsește("]";OBȚINE.CARTEA DE LUCRU(1));"")&T(ACUM())

Funcţie TDATE (ACUM) oferă data curentă (cu ora) și funcția T transformă acea dată într-un șir de text gol, care este apoi concatenat la numele foii noastre folosind operatorul de concatenare (&). Acestea. numele foii nu se schimba de fapt, ci din moment ce functia TDATE este recalculat și produce o nouă oră și dată pentru orice modificare a foii, apoi restul formulei noastre va fi forțat să recalculeze și - ca urmare - numele foilor vor fi actualizate constant.

Pentru a ascunde erorile #REFERINȚĂ (#REF), care va apărea dacă ne copiem formula cu funcția INDEX pentru mai multe celule decât avem foi, putem folosi funcția DACA EROARE, care prinde orice erori și le înlocuiește cu șirul gol (""):

Și, în sfârșit, pentru a adăuga hyperlinkuri „în direct” la numele foilor pentru o navigare rapidă, puteți utiliza aceeași funcție HYPERLINK, care va forma adresa pentru trecerea de la numele foii:

Metoda 3. Macro

Și, în sfârșit, puteți utiliza o macrocomandă simplă pentru a crea un cuprins. Adevărat, va trebui să-l rulați de fiecare dată când structura cărții se schimbă - spre deosebire de Metoda 2, macro-ul în sine nu le urmărește.

Deschideți Editorul Visual Basic făcând clic Alt+F11 sau selectând (în versiunile mai vechi de Excel) din meniu Instrumente - Macro - Editor Visual Basic(Instrumente - Macro - Editor Visual Basic). În fereastra editorului care se deschide, creați un nou modul gol (meniu Inserare - Modul ) și copiați acolo textul acestei macrocomenzi:

Sub SheetList()
Dim sheet As Worksheet
Dim cell As Range
Cu ActiveWorkbook
Pentru fiecare foaie din ActiveWorkbook.Worksheets
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Foaie de lucru(1).Hyperlink-uri.Adăugați ancora:=celulă, Adresă:="", SubAdresă:="'" și foaie.Nume și „'" și „!A1”
celulă.Formulă = foaie.Nume
Următorul
Se termina cu
End Sub Închideți Editorul Visual Basic și reveniți la Excel. Adăugați o foaie goală la carte și plasați-o mai întâi. Apoi apasa Alt+F8 sau deschide meniul Instrumente - Macro - Macro. Găsiți macrocomanda pe care ați creat-o acolo SheetListși rulați-l. Macrocomanda va crea o listă de hyperlinkuri cu nume de foi pe prima foaie a registrului de lucru. Făcând clic pe oricare dintre ele, veți ajunge la foaia dorită.

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul, așa cum este descris în Metoda 1.

Felul meu. Versiunea mea

T
Numele foii - =IFERROR(REPLACE(INDEX(Cuprins,ROW()-3),1,FIND("]",INDEX(Cuprins,ROW()-3));"");"")

Data - =IFEROARE(IF(INDIRECT("'"&B4&"'"&"!A1″)=0;"";INDIRECT("'"&B4&"'"&"!A1″));"")

Nume - =INDIRECT(“‘”&B4&”‘”&”!A3″)

PO - =INDIRECT(“‘”&B4&”‘”&”!E5″)

impozit pe salariu - =INDIRECT(“‘”&B4&”‘”&”!E6″)

amortizare - =INDIRECT(“‘”&B4&”‘”&”!E7″)

materiale - =INDIRECT(“‘”&B4&”‘”&”!E8″)
materiale vsp - =INDIRECT(“‘”&B4&”‘”&”!E9″)

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

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

Dacă numărul de foi din registrul de lucru Excel a depășit al doilea zece, atunci navigarea printre foi începe să devină o problemă. Una dintre modalitățile frumoase de a rezolva acest lucru este crearea unui cuprins cu hyperlinkuri care să conducă la foile corespunzătoare ale cărții:

Există mai multe moduri de a implementa acest lucru.

Video

Pune în document

HYPERLINK Schimbși/sau Ctrl

Cuprins.

Deschis Manager de nume pe filă Cuprins. În câmp Interval (referință) introduceți această formulă:

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

Acum în variabilă Cuprins INDEX

Funcţie RÂND

ÎNLOCUIRE (SUBST)Și GĂSI Manager de nume din fila Cuprinsși schimbați formula:

Cuprins Ctrl+Alt+F9

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

Funcţie TDATE (ACUM) T TDATE

Pentru a ascunde erorile #REFERINȚĂ (#REF) INDEXDACA EROARE

HYPERLINK

Metoda 3. Macro

Metoda 2

Alt+F11 Inserare - Modul

Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook Pentru fiecare foaie din ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name Next End With End Sub

Închideți Visual Basic Editor și reveniți la Excel. Adăugați o foaie goală la carte și plasați-o mai întâi. Apoi apasa Alt+F8 sau deschide meniul SheetList

Metoda 1.

Linkuri conexe

• Ce este o macrocomandă, cum să o creez, unde să copiați textul macrocomenzii, cum să rulați macrocomandă?
• Creați automat un cuprins de carte cu un singur buton (complet PLEX)
• Trimiterea de e-mailuri folosind funcția HYPERLINK
• Comutați rapid între foile dintr-un registru de lucru Excel

Metoda 1: Hyperlinkuri create manual

Introduceți o foaie goală în registrul de lucru și adăugați hyperlinkuri la foile de care aveți nevoie folosind comanda Inserare - Hyperlink. În fereastra care se deschide, selectați opțiunea din stânga Pune în documentși setați afișarea textului extern și adresa celulei la care va duce linkul:

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul. Pentru a nu fi nevoie să creați manual hyperlinkuri și apoi să le copiați în fiecare foaie, este mai bine să utilizați o altă metodă - funcția HYPERLINK. Selectăm toate foile din carte în care dorim să adăugăm un backlink (puteți folosi tastele pentru a selecta în masă foile Schimbși/sau Ctrl) și în orice celulă adecvată introducem o funcție de următoarea formă:

Această funcție va crea un hyperlink în celula curentă pe toate foile selectate cu textul „Înapoi la Cuprins”, făcând clic pe care va reveni utilizatorul la foaie Cuprins.

Metoda 2: Cuprins dinamic folosind formule

Aceasta, deși ușor exotică, este o modalitate foarte frumoasă și convenabilă de a crea un cuprins automat pentru cartea dvs. Exotic - deoarece folosește o caracteristică XLM nedocumentată GET.WORKBOOK, lăsat de dezvoltatori pentru compatibilitate cu versiunile mai vechi de Excel. Această funcție aruncă o listă cu toate foile din registrul de lucru curent într-o variabilă dată, din care apoi le putem extrage și le putem folosi în cuprinsul nostru.

Deschis Manager de nume pe filă Formule (Formule – Manager de nume)și creați o nouă gamă numită, să spunem Cuprins. În câmp Interval (referință) introduceți această formulă:

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

Acum în variabilă Cuprins conține numele noastre căutate. Pentru a le extrage de acolo în foaie, puteți folosi funcția INDEX, care „extrage” elemente din matrice după numărul lor:

Funcţie RÂND oferă numărul de rând curent și, în acest caz, este necesar doar pentru a evita crearea manuală a unei coloane separate cu numerele de serie ale elementelor care sunt extrase (1,2,3...). Astfel, în celula A1 vom avea numele primei foi, în A2 - numele celei de-a doua etc.

Nu-i rău. Cu toate acestea, după cum puteți vedea, funcția returnează nu numai numele foii, ci și numele registrului de lucru, de care nu avem nevoie. Pentru a-l elimina, folosim funcțiile ÎNLOCUIRE (SUBST)Și GĂSI, care va găsi caracterul de închidere paranteză (]) și va înlocui tot textul până la și inclusiv acel caracter cu șirul gol (""). Să-l deschidem din nou Manager de nume din fila Formule (Formule - Manager de nume), faceți dublu clic pentru a deschide intervalul creat Cuprinsși schimbați formula:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"")
=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;GĂSĂ("]";OBȚINE.CARTEA DE LUCRU(1));"")

Acum lista noastră de foi va arăta mult mai bine:

O mică complicație secundară este că formula noastră se află într-un interval numit Cuprins va fi recalculat numai atunci când este introdus, sau când cartea este forțată să se recalculeze prin apăsarea unei combinații de taste Ctrl+Alt+F9. Pentru a ocoli acest moment neplăcut, să adăugăm o mică „coadă” formulei noastre:

REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1));"") &T(TDATE())=SUBST(OBȚINE.CARTEA DE LUCRU(1);1;Găsește("]";OBȚINE.CARTEA DE LUCRU(1));"")&T(ACUM())

Funcţie TDATE (ACUM) oferă data curentă (cu ora) și funcția T transformă acea dată într-un șir de text gol, care este apoi concatenat la numele foii noastre folosind operatorul de concatenare (&). Acestea. numele foii nu se schimba de fapt, ci din moment ce functia TDATE este recalculat și produce o nouă oră și dată pentru orice modificare a foii, apoi restul formulei noastre va fi forțat să recalculeze și - ca urmare - numele foilor vor fi actualizate constant.

Pentru a ascunde erorile #REFERINȚĂ (#REF), care va apărea dacă ne copiem formula cu funcția INDEX pentru mai multe celule decât avem foi, putem folosi funcția DACA EROARE, care prinde orice erori și le înlocuiește cu șirul gol (""):

Și, în sfârșit, pentru a adăuga hyperlinkuri „în direct” la numele foilor pentru o navigare rapidă, puteți utiliza aceeași funcție HYPERLINK, care va forma adresa pentru trecerea de la numele foii:

Metoda 3. Macro

Și, în sfârșit, puteți utiliza o macrocomandă simplă pentru a crea un cuprins. Adevărat, va trebui să-l rulați de fiecare dată când structura cărții se schimbă - spre deosebire de Metoda 2, macro-ul în sine nu le urmărește.

Deschideți Editorul Visual Basic făcând clic Alt+F11 sau selectând (în versiunile mai vechi de Excel) din meniu Instrumente - Macro - Editor Visual Basic(Instrumente - Macro - Editor Visual Basic). În fereastra editorului care se deschide, creați un nou modul gol (meniu Inserare - Modul ) și copiați acolo textul acestei macrocomenzi:

Sub SheetList()
Dim sheet As Worksheet
Dim cell As Range
Cu ActiveWorkbook
Pentru fiecare foaie din ActiveWorkbook.Worksheets
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Foaie de lucru(1).Hyperlink-uri.Adăugați ancora:=celulă, Adresă:="", SubAdresă:="'" și foaie.Nume și „'" și „!A1”
celulă.Formulă = foaie.Nume
Următorul
Se termina cu
End Sub Închideți Editorul Visual Basic și reveniți la Excel. Adăugați o foaie goală la carte și plasați-o mai întâi. Apoi apasa Alt+F8 sau deschide meniul Instrumente - Macro - Macro. Găsiți macrocomanda pe care ați creat-o acolo SheetListși rulați-l. Macrocomanda va crea o listă de hyperlinkuri cu nume de foi pe prima foaie a registrului de lucru. Făcând clic pe oricare dintre ele, veți ajunge la foaia dorită.

Pentru comoditate, puteți crea, de asemenea, backlink-uri pe toate foile cărții dvs., care vor duce înapoi la cuprinsul, așa cum este descris în Metoda 1.

Felul meu. Versiunea mea

T
Numele foii - =IFERROR(REPLACE(INDEX(Cuprins,ROW()-3),1,FIND("]",INDEX(Cuprins,ROW()-3));"");"")

Data - =IFEROARE(IF(INDIRECT("'"&B4&"'"&"!A1″)=0;"";INDIRECT("'"&B4&"'"&"!A1″));"")

Nume - =INDIRECT(“‘”&B4&”‘”&”!A3″)

PO - =INDIRECT(“‘”&B4&”‘”&”!E5″)

impozit pe salariu - =INDIRECT(“‘”&B4&”‘”&”!E6″)

amortizare - =INDIRECT(“‘”&B4&”‘”&”!E7″)

materiale - =INDIRECT(“‘”&B4&”‘”&”!E8″)
materiale vsp - =INDIRECT(“‘”&B4&”‘”&”!E9″)

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

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

Mod implicit Microsoft Excel nu produce numerotarea vizibilă a foilor. În același timp, în multe cazuri, mai ales dacă documentul este trimis pentru tipărire, acestea trebuie numerotate. Excel vă permite să faceți acest lucru folosind anteturi și subsoluri. Să ne uităm la diferitele opțiuni privind numerotarea foilor din această aplicație.

Numerotarea în Excel

Puteți numerota paginile în Excel folosind anteturi și subsoluri. Acestea sunt ascunse implicit, situate în zonele inferioare și superioare ale foii. Particularitatea lor este aceea inclusă în aceasta zonaînregistrările sunt end-to-end, adică sunt afișate pe toate paginile documentului.

Metoda 1: numerotarea regulată

Numerotarea obișnuită presupune numerotarea tuturor foilor unui document.

1. În primul rând, trebuie să activați afișarea antetelor și subsolurilor. Accesați fila „Inserare”.
2. Pe panglică, în blocul instrument „Text”, faceți clic pe butonul „Header and Footer”.
3. După aceasta, Excel intră în modul de marcare, iar anteturile și subsolurile sunt afișate pe foi. Sunt situate în zonele superioare și inferioare. În plus, fiecare dintre ele este împărțită în trei părți. Alegem în ce subsol, precum și în ce parte a acestuia se va efectua numerotarea. În cele mai multe cazuri, selectați partea stângă a antetului. Faceți clic pe partea în care intenționați să plasați numărul.
4. În fila „Designer” a blocului de file suplimentare „Lucrul cu antet și subsol”, faceți clic pe butonul „Număr pagină”, care se află pe panglică din grupul de instrumente „Elemente antet și subsol”.
5. După cum puteți vedea, apare o etichetă specială „&”. Pentru a-l converti într-un anumit număr de serie, faceți clic pe orice zonă a documentului.
6. Acum a apărut un număr de serie pe fiecare pagină a documentului Excel. Pentru a-l face să pară mai prezentabil și să iasă în evidență față de fundalul general, poate fi formatat. Pentru a face acest lucru, selectați intrarea din subsol și treceți cursorul peste ea. Apare un meniu de formatare în care puteți efectua următoarele acțiuni:
   • schimba tipul fontului;
   • faceți-l italic sau îndrăzneț;
   • redimensionare;
   • schimba culoarea.

   Selectați acțiunile pe care doriți să le efectuați pentru a schimba afișarea vizuală a numărului până când se obține un rezultat care vă satisface.

Metoda 2: numerotarea indicând numărul total de foi

De asemenea, puteți numerota paginile în Excel, indicând numărul total de pagini de pe fiecare foaie.

1. Activăm afișarea numerotării, așa cum este indicat în metoda anterioară.
2. Înainte de etichetă scriem cuvântul „Pagină”, iar după el scriem cuvântul „de la”.
3. Plasați cursorul în câmpul de subsol după cuvântul „de la”. Faceți clic pe butonul „Număr de pagini”, care se află pe panglică din fila „Acasă”.
4. Faceți clic oriunde în document, astfel încât valorile să fie afișate în loc de etichete.

Acum afișăm informații nu numai despre numărul curent al foii, ci și despre numărul total al acestora.

Metoda 3: numerotarea de pe pagina a doua

Există cazuri când nu este necesară numerotarea întregului document, ci doar începând de la un anumit loc. Să ne dăm seama cum să facem asta.

Pentru a seta numerotarea de pe a doua pagină, și acest lucru este adecvat, de exemplu, atunci când scrieți rezumate, disertații și lucrări științifice când este pornit Pagina titlu Prezența numerelor nu este permisă, trebuie să efectuați acțiunile indicate mai jos.

1. Să trecem la modul subsol. Apoi, treceți la fila „Header and Footer Designer”, situată în blocul de file „Working with Header and Footer”.
2. În blocul de instrumente „Opțiuni” de pe panglică, bifați opțiunea de setări „Antet și subsol speciale pentru prima pagină”.
3. Setăm numerotarea folosind butonul „Numărul paginii”, așa cum se arată deja mai sus, dar facem acest lucru pe orice pagină, cu excepția primei.

După cum puteți vedea, după aceasta toate foile sunt numerotate, cu excepția primei. Mai mult, prima pagină este luată în considerare în procesul de numerotare a altor foi, dar, cu toate acestea, numărul în sine nu este afișat pe ea.

Metoda 4: numerotarea de pe pagina specificată

În același timp, există situații în care este necesar ca documentul să înceapă nu de la prima pagină, ci, de exemplu, de la a treia sau a șaptea. O astfel de nevoie nu se întâmplă des, dar, cu toate acestea, uneori întrebarea pusă necesită și o soluție.

1. Efectuăm numerotarea în mod obișnuit, folosind butonul corespunzător de pe panglică, descriere detaliata ce s-a dat mai sus.
2. Accesați fila „Aspect pagină”.
3. Există o pictogramă în formă de săgeată oblică pe panglica din colțul din stânga jos al casetei de instrumente Configurare pagină. Facem clic pe el.
4. Se deschide fereastra de parametri, accesați fila „Pagină” dacă a fost deschisă într-o altă filă. În câmpul parametrului „Numărul primei pagini”, introduceți numărul de la care doriți să numerotați. Faceți clic pe butonul „OK”.

După cum puteți vedea, după aceasta numărul primei pagini efective din document s-a schimbat cu cel care a fost specificat în parametri. În consecință, numerotarea foilor ulterioare s-a deplasat și ea.

Lecţie: Cum să eliminați antetele și subsolurile în Excel

Numerotați paginile din tabel procesor Excel destul de simplu. Această procedură se efectuează cu modul antet și subsol activat. În plus, utilizatorul poate personaliza singur numerotarea: formatați afișarea numărului, adăugați o indicație a numărului total de foi ale documentului, număr dintr-un anumit loc etc.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, descriind esența problemei în detaliu. Specialistii nostri vor incerca sa raspunda cat mai repede posibil.