Formula za broj listova i stranica u Excelu. Formula za brojeve listova i stranica u Excelu Kako napraviti sadržaj u Excelu

Analizirajmo formule Brojevi listova i stranica u Excelu(i standardne i prilagođene funkcije) za izradu tablice sadržaja i prikladnu navigaciju kroz dokument.

Kada stvaramo sadržaj za izvješće, knjigu ili članak, važno nam je znati ne samo nazive poglavlja i staviti hiperveze na njih, već i njihov položaj u dokumentu kako bi prilikom čitanja bilo prikladnije za kako bismo se kretali kroz sadržaj dokumenta i lakše pronašli informacije koje su nam potrebne.
Brojeve listova u sadržaj možete dodati ručno, ali u tom slučaju proces dodavanja brojeva može potrajati dosta vremena. Osim toga, ako promijenite broj stranice jednog poglavlja, promijenit će se brojevi za sva sljedeća poglavlja, tako da se ova metoda ne može nazvati prikladnom i učinkovitom.

Razmotrimo opcije koje će nam omogućiti da automatski saznamo brojeve listova i stranica.
Međutim, prije svega, shvatimo koja je točno razlika između lista i stranice u Excelu.
Excel list je tablica sastavljena od ćelija strukturiranih kao podaci u recima i stupcima, a stranica je prikaz lista koji se može ispisati. Drugim riječima, ovisno o postavkama ispisa, jedan list može sadržavati više stranica, pa ćemo problem pronalaska brojeva listova i stranica riješiti zasebno.

Formula broja lista u Excelu

Počevši od Excela 2013, standard funkcija SHEET, koji vraća broj lista iz postojeće veze.
Međutim, u više ranije verzije Excel standard Nažalost, nema funkcija, pa ćemo za ovaj slučaj koristiti mogućnosti VBA i napisati prilagođenu funkciju koja će vratiti svoj serijski broj na temelju naziva lista.

Funkcija SHEET (počevši od programa Excel 2013)

Sintaksa i opis funkcije:

SHEET(vrijednost)
Vraća broj navedenog lista.

• Značenje (izborni argument) — naziv lista ili veze za koju trebate saznati broj lista.

Ako argument nije naveden, funkcija prema zadanim postavkama vraća broj lista na kojem je funkcija navedena.
Pogledajmo principe korištenja funkcije koristeći primjere:

Kada radite s funkcijom, imajte na umu da formule =SHEET("Lista2") I =LIST(A1)(u ovom slučaju ćelija A1, na primjer, sadrži tekst "Sheet2") može vratiti različite rezultate, budući da je u prvom slučaju argument predstavljen kao tekst (tražite list prema nazivu teksta), au drugom - kao poveznicu (tražite list prema ćeliji koja upućuje).

Broj lista u VBA

Da biste izradili prilagođenu funkciju, idite na uređivač (Alt + F11), zatim izradite novi modul i dodajte mu sljedeći kod:

Visual Basic

Funkcija SheetNumber(SheetName As String) Kao Integer "SheetNumber funkcija (SheetName argument kao tekst) koja vraća cjelobrojne vrijednosti SheetNumber = Worksheets(SheetName).Index "izjednačavanje indeksnog broja lista s povratnom vrijednošću End Function

Sada možemo koristiti ovu funkciju na nama prikladan način - ili izravno unesite formulu u bilo koju ćeliju ili upotrijebite čarobnjaka za funkcije (funkciju možete pronaći u kategoriji Korisnik definiran):

Broj stranice u VBA

Možete umetnuti pomoću zaglavlja i podnožja, ali ako želimo dodati broj stranice u ćeliju na listu, tada ova metoda nije prikladna. Upotrijebimo parametar lista PageSetup.Pages.Count, koji određuje broj stranica danog lista za ispis.
Algoritam za traženje broja stranice je sljedeći: za sve listove koji se nalaze između dva zadana lista zbrajamo broj stranica na njima.
Vraćamo se u Visual Basic editor i dodajemo ga u modul nova značajka:

Visual Basic

Funkcija PageNumber(SheetName1 As String, SheetName2 As String) As Integer "PageNumber funkcija (SheetName1 i SheetName2 argumenti kao tekst) koja vraća cjelobrojne vrijednosti Dim FirstPage As Integer, LastPage As Integer "inicijalizacija Application.Volatile True varijable "automatski ponovni izračun PageNumber = 0 " povratna vrijednost = 0 FirstPage = Worksheets(SheetName1).Index "dohvaćanje početnog broja lista LastPage = Worksheets(SheetName2).Index "dohvaćanje završnog broja lista For i = FirstPage To LastPage - 1 "petlja kroz listove PageNumber = PageNumber + Sheets(i) .PageSetup.Pages.Count "dodaje broj stranica trenutnog lista povratnoj vrijednosti funkcije Next i End

Dakle, rezultirajuća funkcija traži broj stranica između dva lista, koji su dani kao argumenti.
Vratimo se u Excel i unesite novu formulu za brojanje brojeva stranica:

Istovremeno smo na listovima “3. dio” i “4. dio” (iz prethodnog primjera) dodatno dodali podatke tako da je list počeo sadržavati nekoliko stranica.

Nažalost, nedostatak ove opcije za određivanje broja stranice je brzina funkcije.
Prilikom izvođenja operacije PageSetup.Pages.Count, Excel pristupa postavkama ispisa pisača za svaku stranicu, koja na knjigama velika veličina dovodi do dugog vremena izračuna za funkciju.

Značajke izračuna

Budući da funkcije SheetNumber I Broj stranice ovise samo o tekstualnim varijablama, onda kada se promijeni broj listova ili stranica u knjizi, oni se ne izračunavaju automatski ponovno.
Kako bismo djelomično izbjegli problem ponovnog izračuna, dodali smo naredbu kodu funkcije (linija 3) Application.Volatile True, koji ponovno izračunava rezultat funkcije kada se promijeni sadržaj ćelija (analogno potpunom ponovnom izračunavanju formula pritiskom na F9).
Ako funkcija i dalje značajno usporava rad, tada možete isključiti ovaj redak iz koda, ali u tom slučaju ne zaboravite provjeriti jesu li formule ponovno izračunate u konačnoj verziji dokumenta.

Kada broj listova u vašoj knjizi poraste i navigacija kroz nju postane problematična, predlažem da napravite tablicu sadržaja za knjigu, s poveznicama na potrebne listove.

umetnuti u knjigu prazan list

u prozoru "Umetni hipervezu" odaberite na što želite povezati hipervezu: "Veza na mjesto u dokumentu." Adresa ćelije - na koju će se ćeliju lista kursor premjestiti. i odaberite mjesto list Singapur. U polju "Tekst" navedite naziv lista. Nakon odabira parametara kliknite OK.

Tekst u ćeliji je promijenjen izgled. To znači da je za njega stvorena hiperveza. Postavimo hiperveze na druge listove u knjizi na isti način. Imajte na umu da hiperveza koja se koristi za navigaciju mijenja svoju boju.

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj. Da izbjegnem učenje ručna izrada hiperveze i zatim ih kopirati na svaki list, bolje je koristiti drugu metodu - funkciju HYPERLINK.

Označimo sve listove u knjizi na koje želimo dodati povratnu poveznicu (za masovni odabir listova potrebno je držati pritisnutu tipku Ctrl i odabrati željene listove mišem), te u bilo koju odgovarajuću ćeliju upišemo sljedeću funkciju:

Ova funkcija će stvoriti in trenutna ćelija na svim odabranim listovima nalazi se hiperlink s tekstom “Natrag na sadržaj”, čijim klikom se korisnik vraća na list sa sadržajem.

Kako stvoriti gumb na izborniku:

Kako bi izbornik bio vizualno ugodniji, dodajmo gumbe

Prvo, kreirajmo oblik budućeg gumba: Umetni → Oblici → Odaberite bilo koji oblik:

Ispišimo tekst unutar oblika. Ovako smo nacrtali gumb.

Odaberite oblik → idite na karticu Umetanje → Hiperveza. Zatim mu dodijelite parametre kao u prvom paragrafu i kliknite OK. Slično, možete stvoriti druge gumbe hiperveze na različitim listovima knjige. I dodajte malo kreativnosti pomoću izbornika FORMAT

Umetnite prazan list u radnu knjigu i pomoću naredbe dodajte hiperveze na listove koji su vam potrebni Umetni - hiperveza. U prozoru koji se otvori odaberite opciju s lijeve strane Stavite u dokument i postavite vanjski prikaz teksta i adresu ćelije u koju će veza voditi:

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj. Kako ne biste morali ručno stvarati hiperveze i zatim ih kopirati na svaki list, bolje je koristiti drugu metodu - funkciju HIPERVEZA. Odaberemo sve listove u knjizi na koje želimo dodati povratnu vezu (možete koristiti tipke za masovni odabir listova Shift i/ili Ctrl) i u bilo koju prikladnu ćeliju upisujemo funkciju sljedećeg oblika:

Ova funkcija će stvoriti hipervezu u trenutnoj ćeliji na svim odabranim listovima s tekstom "Natrag na sadržaj", klikom na koji će se korisnik vratiti na list Sadržaj.

Metoda 2: Dinamički sadržaj pomoću formula

Ovo je, iako pomalo egzotičan, vrlo lijep i praktičan način za izradu automatskog sadržaja za vašu knjigu. Egzotično - jer koristi nedokumentiranu značajku XLM DOBITI.RADNA KNJIŽNICA, koji su programeri ostavili za kompatibilnost sa starijim verzijama Excela. Ova funkcija izbacuje popis svih listova u trenutnoj radnoj knjizi u zadanu varijablu, iz koje ih zatim možemo izdvojiti i koristiti u našoj tablici sadržaja.

Otvoren Upravitelj imena na kartici Formule (Formule – Upravitelj naziva) i stvorite novi imenovani raspon pod nazivom, recimo Sadržaj. U polju Raspon (referenca) unesite ovu formulu:

DOBITI.RADNU.KNJIŽICU(1)
=GET.WORKBOOK(1)

Sada u varijabli Sadržaj sadrži naša tražena imena. Da biste ih izvukli od tamo na list, možete koristiti funkciju INDEKS, koji "izvlači" elemente iz niza po njihovom broju:

Funkcija RED daje broj trenutna linija a, u ovom slučaju, potreban je samo kako se ne bi ručno kreirao poseban stupac s rednim brojevima ekstrahiranih elemenata (1,2,3...). Tako ćemo u ćeliji A1 imati naziv prvog lista, u A2 - naziv drugog itd.

Nije loše. Međutim, kao što vidite, funkcija vraća ne samo naziv lista, već i naziv radne knjige, koji nam ne treba. Da bismo ga uklonili, koristimo funkcije ZAMIJENI (ZAMJENI) I PRONAĆI, koji će pronaći simbol zatvaranja uglata zagrada(]) i zamijenite sav tekst do i uključujući ovaj znak s praznim nizom (""). Otvorimo ga ponovno Upravitelj imena iz tab Formule (Formule - Upravitelj naziva), dvostruki klik otvorite kreirani raspon Sadržaj i promijenite njegovu formulu:

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

Sada će naš popis listova izgledati puno bolje:

Mala sporedna komplikacija je to što je naša formula u imenovanom rasponu Sadržaj ponovno će se izračunati samo kada se unese ili kada je knjiga prisiljena na ponovni izračun pritiskom na kombinaciju tipki Ctrl+Alt+F9. Kako bismo zaobišli ovaj neugodan trenutak, dodajmo mali "repić" našoj formuli:

ZAMIJENI(GET.RADNA BILJEŽNICA(1);1;FIND("]";GET.RADNA BILJEŽNICA(1));"") &T(TDATE())=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Funkcija TDATUM (SADA) daje trenutni datum (s vremenom) i funkciju T pretvara ovaj datum u prazan tekstualni niz, koji se zatim ulančava u naziv našeg lista pomoću operatora ulančavanja (&). Oni. naziv lista se zapravo ne mijenja, ali budući da funkcija TDATUM se ponovno izračunava i proizvodi novo vrijeme i datum za bilo koju promjenu u listu, tada će ostatak naše formule također biti prisiljen ponovno izračunati i - kao rezultat - nazivi listova će se stalno ažurirati.

Za skrivanje grešaka #REFERENCA (#REF), koji će se pojaviti ako kopiramo našu formulu s funkcijom INDEKS na velika količina stanica nego listova, možemo koristiti funkciju IFERROR koji hvata sve pogreške i zamjenjuje ih praznim nizom (""):

I na kraju, za dodavanje "živih" hiperveza nazivima listova za brzu navigaciju, možete koristiti istu funkciju HIPERVEZA, koji će oblikovati adresu za prijelaz iz naziva lista:

Metoda 3. Makro

I konačno, možete koristiti jednostavnu makronaredbu za izradu tablice sadržaja. Istina, morat ćete ga pokrenuti svaki put kada se promijeni struktura knjige - za razliku od Metoda 2, sama makronaredba ih ne prati.

Otvorite Visual Basic Editor klikom Alt+F11 ili odabirom (u starijim verzijama Excela) iz izbornika Alati - Makro - Visual Basic Editor(Alati - Makro - Visual Basic Editor) . U prozoru uređivača koji se otvori kreirajte novi prazan modul (izbornik Umetak - Modul ) i tamo kopirajte tekst ove makronaredbe:

Zatvorite Visual Ba Editor sic i vratite se u Excel. Dodaj u knjigu Prazan list i staviti ga na prvo mjesto. Zatim klikniteAlt+F8ili otvorite izbornikAlati - Makro - Makroi. Tamo pronađite makronaredbu koju ste izradiliSheetListi pokrenite ga. Makronaredba će stvoriti popis hiperveza s nazivima listova na prvom listu radne knjige. Klikom na bilo koji od njih doći ćete do željenog lista.

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj, kao što je opisano u Metoda 1.

Moj način. Moja verzija

materijali - =INDIRECT("""&B4&"""&"!E8")
vsp materijali - =INDIRECT("""&B4&"""&"!E9")
i dalje po stupcima
=INDIRECT("""&B4&"""&"!E10")
=INDIRECT("""&B4&"""&"!E11")
=INDIRECT("""&B4&"""&"!E12")
=INDIRECT("""&B4&"""&"!E13")
=INDIRECT("""&B4&"""&"!E18")
=INDIRECT("""&B4&"""&"!E19")

Ako je broj listova u vašoj Excel radnoj knjizi premašio drugu deseticu, kretanje kroz listove postaje problem. Jedan od lijepe načine Njegovo rješenje je stvoriti tablicu sadržaja s hipervezama koje vode do odgovarajućih listova knjige:

Postoji nekoliko načina da se to provede.

Video

Stavite u dokument

HIPERVEZA Shift i/ili Ctrl

Sadržaj.

Otvoren Upravitelj imena na kartici Sadržaj. U polju Raspon (referenca) unesite ovu formulu:

DOBITI.RADNU.KNJIŽICU(1)
=GET.WORKBOOK(1)

Sada u varijabli Sadržaj INDEKS

Funkcija RED

ZAMIJENI (ZAMJENI) I PRONAĆI Upravitelj imena iz tab Sadržaj i promijenite njegovu formulu:

Sadržaj Ctrl+Alt+F9

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

Funkcija TDATUM (SADA) T TDATUM

Za skrivanje grešaka #REFERENCA (#REF) INDEKSIFERROR

HIPERVEZA

Metoda 3. Makro

Metoda 2

Alt+F11 Umetak - Modul

Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook Za svaki list u ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Adresa:="", PodAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name Next End With End Sub

Zatvorite Visual Basic Editor i vratite se u Excel. Dodajte prazan list u knjigu i postavite ga prvi. Zatim kliknite Alt+F8 ili otvorite izbornik SheetList

Metoda 1.

Povezani Linkovi

• Što je makro, kako ga kreirati, gdje kopirati tekst makronaredbe, kako pokrenuti makro?
• Automatski izradite sadržaj knjige jednim gumbom (PLEX dodatak)
• Slanje e-pošte pomoću funkcije HYPERLINK
• Brzi prijelaz između listova Excel radne knjige

Metoda 1: Ručno stvorene hiperveze

Umetnite prazan list u radnu knjigu i pomoću naredbe dodajte hiperveze na listove koji su vam potrebni Umetni - hiperveza. U prozoru koji se otvori odaberite opciju s lijeve strane Stavite u dokument i postavite vanjski prikaz teksta i adresu ćelije u koju će veza voditi:

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj. Kako ne biste morali ručno stvarati hiperveze i zatim ih kopirati na svaki list, bolje je koristiti drugu metodu - funkciju HIPERVEZA. Odaberemo sve listove u knjizi na koje želimo dodati povratnu vezu (možete koristiti tipke za masovni odabir listova Shift i/ili Ctrl) i u bilo koju prikladnu ćeliju upisujemo funkciju sljedećeg oblika:

Ova funkcija će stvoriti hipervezu u trenutnoj ćeliji na svim odabranim listovima s tekstom "Natrag na sadržaj", klikom na koji će se korisnik vratiti na list Sadržaj.

Metoda 2: Dinamički sadržaj pomoću formula

Ovo je, iako pomalo egzotičan, vrlo lijep i praktičan način za izradu automatskog sadržaja za vašu knjigu. Egzotično - jer koristi nedokumentiranu značajku XLM DOBITI.RADNA KNJIŽNICA, koji su programeri ostavili za kompatibilnost sa starijim verzijama Excela. Ova funkcija izbacuje popis svih listova u trenutnoj radnoj knjizi u zadanu varijablu, iz koje ih zatim možemo izdvojiti i koristiti u našoj tablici sadržaja.

Otvoren Upravitelj imena na kartici Formule (Formule – Upravitelj naziva) i stvorite novi imenovani raspon pod nazivom, recimo Sadržaj. U polju Raspon (referenca) unesite ovu formulu:

DOBITI.RADNU.KNJIŽICU(1)
=GET.WORKBOOK(1)

Sada u varijabli Sadržaj sadrži naša tražena imena. Da biste ih izvukli od tamo na list, možete koristiti funkciju INDEKS, koji "izvlači" elemente iz niza po njihovom broju:

Funkcija RED daje trenutni broj retka i, u ovom slučaju, potreban je samo kako bi se izbjeglo ručno stvaranje zasebnog stupca sa serijskim brojevima elemenata koji se izdvajaju (1,2,3...). Tako ćemo u ćeliji A1 imati naziv prvog lista, u A2 - naziv drugog itd.

Nije loše. Međutim, kao što vidite, funkcija vraća ne samo naziv lista, već i naziv radne knjige, koji nam ne treba. Da bismo ga uklonili, koristimo funkcije ZAMIJENI (ZAMJENI) I PRONAĆI, koji će pronaći zatvarajući znak uglate zagrade (]) i zamijeniti sav tekst do i uključujući taj znak praznim nizom (""). Otvorimo ga ponovno Upravitelj imena iz tab Formule (Formule - Upravitelj naziva), dvaput kliknite za otvaranje stvorenog raspona Sadržaj i promijenite njegovu formulu:

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

Sada će naš popis listova izgledati puno bolje:

Mala sporedna komplikacija je to što je naša formula u imenovanom rasponu Sadržaj ponovno će se izračunati samo kada se unese ili kada je knjiga prisiljena na ponovni izračun pritiskom na kombinaciju tipki Ctrl+Alt+F9. Kako bismo zaobišli ovaj neugodan trenutak, dodajmo mali "repić" našoj formuli:

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

Funkcija TDATUM (SADA) daje trenutni datum (s vremenom) i funkciju T pretvara taj datum u prazan tekstualni niz, koji se zatim povezuje s nazivom našeg lista pomoću operatora za spajanje (&). Oni. naziv lista se zapravo ne mijenja, ali budući da funkcija TDATUM se ponovno izračunava i proizvodi novo vrijeme i datum za bilo koju promjenu u listu, tada će ostatak naše formule također biti prisiljen ponovno izračunati i - kao rezultat - nazivi listova će se stalno ažurirati.

Za skrivanje grešaka #REFERENCA (#REF), koji će se pojaviti ako kopiramo našu formulu s funkcijom INDEKS za više ćelija nego što imamo listova, možemo koristiti funkciju IFERROR, koji hvata sve pogreške i zamjenjuje ih praznim nizom (""):

I na kraju, za dodavanje "živih" hiperveza nazivima listova za brzu navigaciju, možete koristiti istu funkciju HIPERVEZA, koji će oblikovati adresu za prijelaz iz naziva lista:

Metoda 3. Makro

I konačno, možete koristiti jednostavnu makronaredbu za izradu tablice sadržaja. Istina, morat ćete ga pokrenuti svaki put kada se promijeni struktura knjige - za razliku od Metoda 2, sama makronaredba ih ne prati.

Otvorite Visual Basic Editor klikom Alt+F11 ili odabirom (u starijim verzijama Excela) iz izbornika Alati - Makro - Visual Basic Editor(Alati - Makro - Visual Basic Editor). U prozoru uređivača koji se otvori kreirajte novi prazan modul (izbornik Umetak - Modul ) i tamo kopirajte tekst ove makronaredbe:

Sub SheetList()
Dim list Kao radni list
Zatamnjena ćelija kao raspon
Uz ActiveWorkbook
Za svaki list u ActiveWorkbook.Radni listovi
Postavi ćeliju = Radni listovi(1).Ćelije(list.Indeks, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
ćelija.Formula = list.Naziv
Sljedeći
Završi sa
End Sub Zatvorite Visual Basic Editor i vratite se u Excel. Dodajte prazan list u knjigu i postavite ga prvi. Zatim kliknite Alt+F8 ili otvorite izbornik Alati - Makro - Makroi. Tamo pronađite makronaredbu koju ste izradili SheetList i pokrenite ga. Makronaredba će stvoriti popis hiperveza s nazivima listova na prvom listu radne knjige. Klikom na bilo koji od njih doći ćete do željenog lista.

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj, kao što je opisano u Metoda 1.

Moj način. Moja verzija

T
Naziv lista - =IFERROR(REPLACE(INDEX(Sadržaj,RED()-3),1,FIND("]",INDEX(Sadržaj,RED()-3));"");"")

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

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

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

porez na plaću - =INDIRECT(“‘”&B4&”‘”&”!E6″)

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

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

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

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

Ako je broj listova u vašoj Excel radnoj knjizi premašio drugu deseticu, kretanje kroz listove postaje problem. Jedan od lijepih načina da se to riješi je stvaranje tablice sadržaja s hipervezama koje vode do odgovarajućih stranica knjige:

Postoji nekoliko načina da se to provede.

Video

Stavite u dokument

HIPERVEZA Shift i/ili Ctrl

Sadržaj.

Otvoren Upravitelj imena na kartici Sadržaj. U polju Raspon (referenca) unesite ovu formulu:

DOBITI.RADNU.KNJIŽICU(1)
=GET.WORKBOOK(1)

Sada u varijabli Sadržaj INDEKS

Funkcija RED

ZAMIJENI (ZAMJENI) I PRONAĆI Upravitelj imena iz tab Sadržaj i promijenite njegovu formulu:

Sadržaj Ctrl+Alt+F9

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

Funkcija TDATUM (SADA) T TDATUM

Za skrivanje grešaka #REFERENCA (#REF) INDEKSIFERROR

HIPERVEZA

Metoda 3. Makro

Metoda 2

Alt+F11 Umetak - Modul

Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook Za svaki list u ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell , Adresa:="", PodAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name Next End With End Sub

Zatvorite Visual Basic Editor i vratite se u Excel. Dodajte prazan list u knjigu i postavite ga prvi. Zatim kliknite Alt+F8 ili otvorite izbornik SheetList

Metoda 1.

Povezani Linkovi

• Što je makro, kako ga kreirati, gdje kopirati tekst makronaredbe, kako pokrenuti makro?
• Automatski izradite sadržaj knjige jednim gumbom (PLEX dodatak)
• Slanje e-pošte pomoću funkcije HYPERLINK
• Brzo prebacivanje između listova u Excel radnoj knjizi

Metoda 1: Ručno stvorene hiperveze

Umetnite prazan list u radnu knjigu i pomoću naredbe dodajte hiperveze na listove koji su vam potrebni Umetni - hiperveza. U prozoru koji se otvori odaberite opciju s lijeve strane Stavite u dokument i postavite vanjski prikaz teksta i adresu ćelije u koju će veza voditi:

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj. Kako ne biste morali ručno stvarati hiperveze i zatim ih kopirati na svaki list, bolje je koristiti drugu metodu - funkciju HIPERVEZA. Odaberemo sve listove u knjizi na koje želimo dodati povratnu vezu (možete koristiti tipke za masovni odabir listova Shift i/ili Ctrl) i u bilo koju prikladnu ćeliju upisujemo funkciju sljedećeg oblika:

Ova funkcija će stvoriti hipervezu u trenutnoj ćeliji na svim odabranim listovima s tekstom "Natrag na sadržaj", klikom na koji će se korisnik vratiti na list Sadržaj.

Metoda 2: Dinamički sadržaj pomoću formula

Ovo je, iako pomalo egzotičan, vrlo lijep i praktičan način za izradu automatskog sadržaja za vašu knjigu. Egzotično - jer koristi nedokumentiranu značajku XLM DOBITI.RADNA KNJIŽNICA, koji su programeri ostavili za kompatibilnost sa starijim verzijama Excela. Ova funkcija izbacuje popis svih listova u trenutnoj radnoj knjizi u zadanu varijablu, iz koje ih zatim možemo izdvojiti i koristiti u našoj tablici sadržaja.

Otvoren Upravitelj imena na kartici Formule (Formule – Upravitelj naziva) i stvorite novi imenovani raspon pod nazivom, recimo Sadržaj. U polju Raspon (referenca) unesite ovu formulu:

DOBITI.RADNU.KNJIŽICU(1)
=GET.WORKBOOK(1)

Sada u varijabli Sadržaj sadrži naša tražena imena. Da biste ih izvukli od tamo na list, možete koristiti funkciju INDEKS, koji "izvlači" elemente iz niza po njihovom broju:

Funkcija RED daje trenutni broj retka i, u ovom slučaju, potreban je samo kako bi se izbjeglo ručno stvaranje zasebnog stupca sa serijskim brojevima elemenata koji se izdvajaju (1,2,3...). Tako ćemo u ćeliji A1 imati naziv prvog lista, u A2 - naziv drugog itd.

Nije loše. Međutim, kao što vidite, funkcija vraća ne samo naziv lista, već i naziv radne knjige, koji nam ne treba. Da bismo ga uklonili, koristimo funkcije ZAMIJENI (ZAMJENI) I PRONAĆI, koji će pronaći zatvarajući znak uglate zagrade (]) i zamijeniti sav tekst do i uključujući taj znak praznim nizom (""). Otvorimo ga ponovno Upravitelj imena iz tab Formule (Formule - Upravitelj naziva), dvaput kliknite za otvaranje stvorenog raspona Sadržaj i promijenite njegovu formulu:

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

Sada će naš popis listova izgledati puno bolje:

Mala sporedna komplikacija je to što je naša formula u imenovanom rasponu Sadržaj ponovno će se izračunati samo kada se unese ili kada je knjiga prisiljena na ponovni izračun pritiskom na kombinaciju tipki Ctrl+Alt+F9. Kako bismo zaobišli ovaj neugodan trenutak, dodajmo mali "repić" našoj formuli:

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

Funkcija TDATUM (SADA) daje trenutni datum (s vremenom) i funkciju T pretvara taj datum u prazan tekstualni niz, koji se zatim povezuje s nazivom našeg lista pomoću operatora za spajanje (&). Oni. naziv lista se zapravo ne mijenja, ali budući da funkcija TDATUM se ponovno izračunava i proizvodi novo vrijeme i datum za bilo koju promjenu u listu, tada će ostatak naše formule također biti prisiljen ponovno izračunati i - kao rezultat - nazivi listova će se stalno ažurirati.

Za skrivanje grešaka #REFERENCA (#REF), koji će se pojaviti ako kopiramo našu formulu s funkcijom INDEKS za više ćelija nego što imamo listova, možemo koristiti funkciju IFERROR, koji hvata sve pogreške i zamjenjuje ih praznim nizom (""):

I na kraju, za dodavanje "živih" hiperveza nazivima listova za brzu navigaciju, možete koristiti istu funkciju HIPERVEZA, koji će oblikovati adresu za prijelaz iz naziva lista:

Metoda 3. Makro

I konačno, možete koristiti jednostavnu makronaredbu za izradu tablice sadržaja. Istina, morat ćete ga pokrenuti svaki put kada se promijeni struktura knjige - za razliku od Metoda 2, sama makronaredba ih ne prati.

Otvorite Visual Basic Editor klikom Alt+F11 ili odabirom (u starijim verzijama Excela) iz izbornika Alati - Makro - Visual Basic Editor(Alati - Makro - Visual Basic Editor). U prozoru uređivača koji se otvori kreirajte novi prazan modul (izbornik Umetak - Modul ) i tamo kopirajte tekst ove makronaredbe:

Sub SheetList()
Dim list Kao radni list
Zatamnjena ćelija kao raspon
Uz ActiveWorkbook
Za svaki list u ActiveWorkbook.Radni listovi
Postavi ćeliju = Radni listovi(1).Ćelije(list.Indeks, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
ćelija.Formula = list.Naziv
Sljedeći
Završi sa
End Sub Zatvorite Visual Basic Editor i vratite se u Excel. Dodajte prazan list u knjigu i postavite ga prvi. Zatim kliknite Alt+F8 ili otvorite izbornik Alati - Makro - Makroi. Tamo pronađite makronaredbu koju ste izradili SheetList i pokrenite ga. Makronaredba će stvoriti popis hiperveza s nazivima listova na prvom listu radne knjige. Klikom na bilo koji od njih doći ćete do željenog lista.

Radi praktičnosti, također možete stvoriti povratne veze na svim listovima svoje knjige, koje će vas voditi natrag na sadržaj, kao što je opisano u Metoda 1.

Moj način. Moja verzija

T
Naziv lista - =IFERROR(REPLACE(INDEX(Sadržaj,RED()-3),1,FIND("]",INDEX(Sadržaj,RED()-3));"");"")

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

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

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

porez na plaću - =INDIRECT(“‘”&B4&”‘”&”!E6″)

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

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

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

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

Zadano Microsoft Excel ne daje vidljivu numeraciju listova. Istodobno, u mnogim slučajevima, osobito ako se dokument šalje na ispis, potrebno ih je numerirati. Excel vam omogućuje da to učinite pomoću zaglavlja i podnožja. Pogledajmo različite opcije o tome kako numerirati listove u ovoj aplikaciji.

Numeriranje u Excelu

Stranice u Excelu možete numerirati pomoću zaglavlja i podnožja. Oni su prema zadanim postavkama skriveni, nalaze se u donjem i gornjem dijelu lista. Njihova je posebnost što su uključeni u ovo područje zapisi su end-to-end, odnosno prikazuju se na svim stranicama dokumenta.

Metoda 1: redovito numeriranje

Redovno numeriranje podrazumijeva numeriranje svih listova dokumenta.

1. Prije svega, morate omogućiti prikaz zaglavlja i podnožja. Idite na karticu "Umetni".
2. Na vrpci, u bloku alata "Tekst", kliknite gumb "Zaglavlje i podnožje".
3. Nakon toga Excel prelazi u način označavanja, a zaglavlja i podnožja se prikazuju na listovima. Nalaze se u gornjim i donjim područjima. Osim toga, svaki od njih je podijeljen u tri dijela. Sami biramo u kojem podnožju, kao iu kojem dijelu će se numerirati. U većini slučajeva odaberite lijevu stranu zaglavlja. Kliknite na dio gdje planirate staviti broj.
4. Na kartici "Dizajner" bloka dodatnih kartica "Rad sa zaglavljem i podnožjem" kliknite gumb "Broj stranice" koji se nalazi na vrpci u grupi alata "Elementi zaglavlja i podnožja".
5. Kao što vidite, pojavljuje se posebna oznaka "&". Da biste ga pretvorili u određeni serijski broj, kliknite bilo koje područje dokumenta.
6. Sada se na svakoj stranici Excel dokumenta pojavio serijski broj. Kako bi izgledao vidljivije i istaknuo se od opće pozadine, može se oblikovati. Da biste to učinili, odaberite unos u podnožju i zadržite pokazivač iznad njega. Pojavljuje se izbornik za oblikovanje u kojem možete izvršiti sljedeće radnje:
   • promijeniti vrstu fonta;
   • neka bude kurziv ili podebljan;
   • promijeniti veličinu;
   • promijeniti boju.

   Odaberite radnje koje želite izvršiti za promjenu vizualnog prikaza broja dok se ne postigne rezultat koji vas zadovoljava.

Metoda 2: numeriranje koje označava ukupan broj listova

Također možete numerirati stranice u Excelu, navodeći ukupan broj stranica na svakom listu.

1. Aktiviramo prikaz numeriranja, kao što je navedeno u prethodnoj metodi.
2. Ispred oznake napišemo riječ “Stranica”, a iza nje napišemo riječ “od”.
3. Postavite kursor u polje podnožja nakon riječi "from". Kliknite na gumb "Broj stranica", koji se nalazi na vrpci u kartici "Početna".
4. Kliknite bilo gdje u dokumentu kako bi se umjesto oznaka prikazale vrijednosti.

Sada prikazujemo informacije ne samo o trenutnom broju listova, već io njihovom ukupnom broju.

Metoda 3: numeriranje od druge stranice

Postoje slučajevi kada nije potrebno numerirati cijeli dokument, već samo počevši od određenog mjesta. Smislimo kako to učiniti.

Kako biste postavili numeriranje od druge stranice, a to je prikladno, na primjer, kada pišete sažetke, disertacije i znanstveni radovi kada je uključen Naslovnica Prisutnost brojeva nije dopuštena, morate izvršiti radnje navedene u nastavku.

1. Prijeđimo u način podnožja. Zatim prijeđite na karticu "Dizajner zaglavlja i podnožja", koja se nalazi u bloku kartica "Rad s zaglavljem i podnožjem".
2. U bloku alata "Opcije" na vrpci označite opciju postavki "Posebno zaglavlje i podnožje za prvu stranicu".
3. Postavljamo numeriranje pomoću gumba "Broj stranice", kao što je već prikazano gore, ali to radimo na bilo kojoj stranici osim prve.

Kao što vidite, nakon ovoga svi listovi su numerirani, osim prvog. Štoviše, prva stranica se uzima u obzir u procesu numeriranja ostalih listova, ali ipak sam broj nije prikazan na njoj.

Metoda 4: numeriranje s navedene stranice

Istodobno, postoje situacije kada je potrebno da dokument ne počne od prve stranice, već, na primjer, od treće ili sedme. Takva se potreba ne događa često, ali, ipak, ponekad postavljeno pitanje zahtijeva i rješenje.

1. Numeriranje vršimo na uobičajeni način, pomoću odgovarajućeg gumba na vrpci, Detaljan opisšto je gore navedeno.
2. Idite na karticu "Izgled stranice".
3. Postoji ikona u obliku kose strelice na vrpci u donjem lijevom kutu alatnog okvira Postavljanje stranice. Kliknemo na njega.
4. Otvara se prozor s parametrima, idite na karticu "Stranica" ako je otvorena u drugoj kartici. U polje parametra “Broj prve stranice” unesite broj od kojeg želite numerirati. Kliknite na gumb "OK".

Kao što vidite, nakon toga se broj stvarne prve stranice u dokumentu promijenio u onaj koji je naveden u parametrima. Sukladno tome, pomaknulo se i numeriranje sljedećih listova.

Lekcija: Kako ukloniti zaglavlja i podnožja u Excelu

Numerirajte stranice u tablici Excel procesor prilično jednostavno. Ovaj se postupak izvodi s uključenim načinom zaglavlja i podnožja. Osim toga, korisnik može sebi prilagoditi numeriranje: formatirati prikaz broja, dodati naznaku ukupnog broja listova dokumenta, broj s određenog mjesta itd.

Drago nam je što smo vam mogli pomoći u rješavanju problema.

Postavite svoje pitanje u komentarima, detaljno opisujući bit problema. Naši stručnjaci pokušat će odgovoriti što je brže moguće.