Kako uporabljati Excel kot profesionalec: 18 preprostih Excelovih nasvetov, trikov in bližnjic

Včasih se zdi, da je Excel preveč dober, da bi bil resničen. Vse kar moram storiti je, da vnesem formulo in skoraj vse, kar bi kdajkoli potreboval ročno, lahko naredim samodejno. Ali želite združiti dva lista s podobnimi podatki? Excel to zmore. Morate narediti preprosto matematiko? Excel to zmore. Ali želite združiti informacije v več celicah? Excel to zmore.

Edina težava je, da je za začetnike težko uporabna. Čeprav je ročno vnašanje podatkov enostavno, je lahko nočna mora naučiti vse formule in bližnjice, ki jih potrebujete za obvladovanje orodja. Poleg tega tudi če formule uporabljate pravilno, vedno obstaja možnost, da boste naleteli sporočila o napakah. Preprosto povedano, Excel je lahko zelo težko uporabljati.

Ne skrbi. V tem prispevku bom našel najboljše nasvete, trike in bližnjice, s katerimi lahko trenutno svojo igro Excel popeljete na naslednjo stopnjo. Napredno znanje programa Excel ni potrebno.

Kaj je Excel?

Microsoft Excel je programska oprema za preglednice, ki jo tržniki, računovodje, analitiki podatkov in drugi strokovnjaki uporabljajo za shranjevanje, organiziranje in sledenje naborom podatkov. Je del zbirke izdelkov Microsoft Office. Med druge možnosti spadajo Google Preglednice in številke. Tu najdete več možnosti programa Excel.

Če želite začeti s svojim izobraževanjem v Excelu, si oglejte spodnji video o tem, kako uporabljati Excel za začetnike.

Excel se v glavnem uporablja za ustvarjanje finančnih dokumentov zaradi svojih močnih računskih moči. Programsko opremo pogosto najdete v računovodskih pisarnah in skupinah, saj omogoča računovodjem, da samodejno vidijo vsote, povprečja in vsote. Z Excelom lahko zlahka osmislijo podatke o svojem podjetju.

Čeprav je Excel v prvi vrsti znan kot računovodsko orodje, lahko strokovnjaki na katerem koli področju uporabljajo njegove funkcije in formule - zlasti tržniki -, ker ga je mogoče uporabiti za sledenje kakršnim koli vrstam podatkov. Odstrani potrebo po urah in urah za štetje celic ali kopiranje in lepljenje številk zmogljivosti. Excel ima običajno bližnjico ali hitro rešitev, ki pospeši postopek.

Ne veste, kako lahko dejansko uporabljate Excel v svoji skupini? Tu je seznam dokumentov, ki jih lahko ustvarite:

  • Izkaz poslovnega izida: S pomočjo preglednice Excel lahko sledite prodajni dejavnosti podjetja in finančnemu stanju.
  • Bilance stanja: Bilance stanja so med najpogostejšimi vrstami dokumentov, ki jih lahko ustvarite z Excelom. Omogoča vam celostni prikaz finančnega stanja podjetja.
  • Koledar: Preprosto lahko ustvarite mesečni koledar preglednic za sledenje dogodkom ali drugim datumom občutljivim informacijam.

Tu je nekaj dokumentov, ki jih lahko ustvarite posebej za tržnike.

To je le majhen vzorec vrst tržnih in poslovnih dokumentov, ki jih lahko ustvarite v Excelu. Ustvarili smo obsežen seznam Excelovih predlog zdaj lahko uporabite za trženje, izdajanje računov, upravljanje projektov, pripravo proračuna in še več.

Spodaj lahko prenesete Excelove predloge za vse svoje tržne potrebe.

Ko prenesete predloge, je čas, da začnete uporabljati programsko opremo. Najprej zajemimo osnove.

Osnove programa Excel

Če šele začenjate uporabljati Excel, obstaja nekaj osnovnih ukazov, s katerimi se predlagamo, da se z njimi seznanite. To so stvari, kot so:

  • Ustvarjanje nove preglednice iz nič.
  • Izvajanje osnovnih izračunov, kot so seštevanje, odštevanje, množenje in deljenje.
  • Pisanje in oblikovanje besedila in naslovov stolpcev.
  • Uporaba Excelovih funkcij samodejnega polnjenja.
  • Dodajanje ali brisanje posameznih stolpcev, vrstic in preglednic. Spodaj bomo preučili, kako dodati stvari, kot so več stolpcev in vrstic.
  • Med pomikanjem po njih v preglednici naj bodo naslovi stolpcev in vrstic vidni, tako da boste med premikanjem po dokumentu vedeli, katere podatke vnašate.

Za globok potop o teh osnovah si oglejte našo celovit vodnik Microsoft Excel.

V duhu učinkovitejšega dela in izogibanja dolgočasnemu ročnemu delu je tu nekaj Excelovih formul in funkcij, ki jih boste morali poznati.

Excel formule

Preprosto vas lahko prevzame širok spekter Formule Excela ki jih lahko uporabite za smiselnost podatkov. Če šele začenjate uporabljati Excel, se lahko za izvajanje nekaterih zapletenih funkcij zanesete na naslednje formule - ne da bi to povečalo zapletenost vaše učne poti.

  • Znak enačbe: Preden ustvarite katero koli formulo, boste morali napisati enačbo (=) v celici, kjer želite, da se prikaže rezultat.
  • Poleg tega: Če želite dodati vrednosti dveh ali več celic, uporabite + znak. Primer: = C5 + D3.
  • Odštevanje: Če želite odšteti vrednosti dveh ali več celic, uporabite - znak. Primer: = C5-D3.
  • Množenje: Če želite pomnožiti vrednosti dveh ali več celic, uporabite * znak. Primer: = C5 * D3.
  • delitev: Za razdelitev vrednosti dveh ali več celic uporabite / znak. Primer: = C5 / D3.

Če vse to združite, lahko ustvarite formulo, ki v eni celici sešteva, odšteva, množi in deli. Primer: =(C5-D3)/((A5+B6)*3).

Za bolj zapletene formule boste morali okrog izrazov uporabiti oklepaje, da se izognete nenamerni uporabi Vrstni red delovanja PEMDAS. Upoštevajte, da lahko v formulah uporabite navadna števila.

Funkcije Excel

Funkcije Excel avtomatizirajo nekatere naloge, ki bi jih uporabili v tipični formuli. Na primer, namesto da bi uporabili + Če želite dodati obseg celic, uporabite funkcijo SUM. Oglejmo si še nekaj funkcij, ki bodo pomagale avtomatizirati izračune in naloge.

  • SUM: Funkcija SUM samodejno doda vrsto celic ali številk. Za dokončanje vsote vnesete začetno in končno celico, vmes pa dvopičje. Evo, kako je to videti: SUM (celica1: celica2). Primer: = SUM (C5: C30).
  • POVPREČNA: Funkcija AVERAGE povpreči vrednosti celotnega obsega celic. Sintaksa je enaka funkciji SUM: POVPREČNO (celica1: celica2). primer: = POVPREČNO (C5: C30).
  • IF: Funkcija IF omogoča vrnitev vrednosti na podlagi logičnega testa. Sintaksa je naslednja: ČE (logični_test, vrednost_pri_true, [vrednost_pri_pravi]]. Primer: = ČE (A2> B2, "Presežek proračuna", "V redu").
  • VLOOKUP: Funkcija VLOOKUP vam pomaga poiskati kar koli v vrsticah vašega lista. Sintaksa je: VLOOKUP (iskalna vrednost, matrična tabela, številka stolpca, približno ujemanje (TRUE) ali natančno ujemanje (FALSE)). Primer: = VLOOKUP ([@ odvetnik], tbl_Attorneys, 4, FALSE).
  • INDEX: Funkcija INDEX vrne vrednost znotraj obsega. Sintaksa je naslednja: INDEX (matrika, številka_vrstice, [številka_ stolpca]).
  • MATCH: Funkcija MATCH išče določen element v območju celic in vrne položaj tega elementa. Uporablja se lahko v tandemu s funkcijo INDEX. Sintaksa je: MATCH (lookup_value, lookup_array, [match_type]).
  • COUNTIF: Funkcija COUNTIF vrne število celic, ki izpolnjujejo določena merila ali imajo določeno vrednost. Sintaksa je: COUNTIF (obseg, merila). primer: = COUNTIF (A2: A5, "London").

V redu, ste pripravljeni vstopiti v grozljivo? Pojdimo k njemu. (In vsem oboževalcem Harryja Potterja tam zunaj ... vabljeni vnaprej.)

Opombe: GIF-ji in vizualni elementi so iz prejšnje različice Excela. Kopija je bila posodobljena, tako da vsebuje navodila za uporabnike novejših in starejših različic Excela.

1. Uporabite vrtilne tabele za prepoznavanje in razumevanje podatkov.

Vrtilne tabele se uporabljajo za reorganizacijo podatkov v preglednici. Podatkov, ki jih imate, ne bodo spremenili, lahko pa seštejejo vrednosti in primerjajo različne informacije v preglednici, odvisno od tega, kaj želite, da naredijo.

Oglejmo si primer. Recimo, da želim pogledati, koliko ljudi je v vsaki hiši v Hogwartsu. Morda mislite, da nimam preveč podatkov, toda za daljše nabore podatkov mi bo to prišlo prav.

Če želite ustvariti vrtilno tabelo, grem na datum > Vrteča miza. Če uporabljate najnovejšo različico Excela, pojdite na Vstavi > Vrteča miza. Excel bo samodejno zapolnil vrtilno tabelo, vendar lahko vedno spremenite vrstni red podatkov. Potem imate na izbiro štiri možnosti.

  • Filter poročila: To vam omogoča ogled samo določenih vrstic v naboru podatkov. Če bi na primer želel ustvariti filter po hišah, bi lahko v Gryffindor namesto vseh študentov vključil samo študente.
  • Oznake stolpcev: To bi bile vaše glave v naboru podatkov.
  • Oznake vrstic: To so lahko vaše vrstice v naboru podatkov. Oznake Vrstica in Stolpec lahko vsebujejo podatke iz stolpcev (npr. Ime lahko povlečete na oznako Vrstica ali Stolpec - odvisno samo od tega, kako si želite ogledati podatke.)
  • vrednost: Ta razdelek vam omogoča drugačen pogled na vaše podatke. Namesto da vnesete katero koli številsko vrednost, lahko seštevate, štejete, povprečite, največ, najmanj, štejete številke ali opravite nekaj drugih manipulacij s svojimi podatki. Dejansko privzeto, ko polje povlečete na Value, vedno šteje.

Ker želim prešteti število študentov v vsaki hiši, grem do graditelja vrtilnih tabel in povlečem stolpec Hiša tako na vrstice kot na vrednosti. To bo seštelo število študentov, povezanih z vsako hišo.Ustvarjanje vrtilne tabele Excel

2. Dodajte več vrstic ali stolpcev.

Ko se igrate s svojimi podatki, boste morda ugotovili, da nenehno dodajate več vrstic in stolpcev. Včasih boste morda morali dodati na stotine vrstic. To početje posebej bi bilo zelo dolgočasno. Na srečo vedno obstaja lažji način.

Če želite v preglednico dodati več vrstic ali stolpcev, označite enako število že obstoječih vrstic ali stolpcev, ki jih želite dodati. Nato z desno tipko miške kliknite in izberite »Vstavi«.

V spodnjem primeru želim dodati dodatne tri vrstice. Če označim tri vrstice in nato kliknem na vstavi, lahko v preglednico hitro in enostavno dodam dodatne tri prazne vrstice.

vstavite Spaces v Excelu

3. Za poenostavitev podatkov uporabite filtre.

Ko gledate zelo velike nabore podatkov, vam običajno ni treba gledati vsake posamezne vrstice hkrati. Včasih si želite ogledati samo podatke, ki ustrezajo določenim merilom.

Tam pridejo filtri.

Filtri omogočajo razčlenitev podatkov, da lahko hkrati pogledate samo nekatere vrstice. V Excelu lahko v vsak stolpec podatkov dodate filter - od tam pa lahko nato izberete, katere celice si želite ogledati hkrati.

Oglejmo si spodnji primer. Filter dodajte tako, da kliknete jeziček Podatki in izberete »Filter«. Če kliknete puščico poleg naslovov stolpcev, boste lahko izbrali, ali želite, da so vaši podatki razvrščeni v naraščajočem ali padajočem vrstnem redu, in katere vrstice želite prikazati.

V mojem primeru Harryja Potterja recimo, da želim videti le študente v Gryffindorju. Z izbiro filtra Gryffindor druge vrstice izginejo.

Excel filtri v akcijiPro Nasvet: Kopirajte in prilepite vrednosti v preglednico, ko je vklopljen filter, da naredite dodatno analizo v drugi preglednici.

4. Odstranite podvojene podatkovne točke ali nize.

Večji nabori podatkov imajo ponavadi podvojene vsebine. Morda imate seznam več stikov v podjetju in želite videti le število podjetij, ki jih imate. V takih situacijah je odstranjevanje dvojnikov zelo priročno.

Če želite odstraniti dvojnike, označite vrstico ali stolpec, iz katerega želite odstraniti dvojnike. Nato pojdite na zavihek Podatki in izberite »Odstrani dvojnike« (ki je v podnaslovu Orodja v starejši različici Excela). Pojavilo se bo pojavno okno, ki bo potrdilo, s katerimi podatki želite delati. Izberite »Odstrani dvojnike« in že ste pripravljeni.

Odstranjevanje dvojnikov v Excelu

To funkcijo lahko uporabite tudi za odstranitev celotne vrstice na podlagi podvojene vrednosti stolpca. Če imate torej tri vrstice s podatki o Harryju Potterju in morate videti le eno, lahko izberete celoten nabor podatkov in nato odstranite dvojnike na podlagi e-pošte. Rezultatni seznam bo imel samo enolična imena brez kakršnih koli dvojnikov.

5. Prestavite vrstice v stolpce.

Ko imate v preglednici vrstice podatkov, se lahko odločite, da želite elemente v eni od teh vrstic dejansko pretvoriti v stolpce (ali obratno). Kopiranje in lepljenje vsake posamezne glave bi trajalo veliko časa - toda funkcija prenosa vam omogoča, da preprosto premaknete podatke vrstic v stolpce ali obratno.

Najprej označite stolpec, ki ga želite prenesti v vrstice. Z desno miškino tipko kliknite in izberite »Kopiraj«. Nato v preglednici izberite celice, v katerih želite, da se začne prva vrstica ali stolpec. Z desno miškino tipko kliknite celico in izberite »Posebno lepljenje«. Prikaže se modul - na dnu boste videli možnost prenosa. Označite to polje in izberite V redu. Vaš stolpec bo zdaj prenesen v vrstico ali obratno.

Orodje za prenos v Excelu

V novejših različicah Excela se bo namesto pojavnega pojavil spustni meni.

Excel orodje za prenos v novejših različicah

6. Razdelite besedilne informacije med stolpce.

Kaj če želite informacije, ki so v eni celici, razdeliti na dve različni celici? Na primer, morda želite nekomu izvleči ime podjetja prek njegovega e-poštnega naslova. Ali pa želite za predloge za e-poštno trženje ločiti polno ime nekoga v ime in priimek.

Zahvaljujoč Excelu sta mogoča oba. Najprej označite stolpec, ki ga želite razdeliti. Nato pojdite na zavihek Podatki in izberite »Besedilo v stolpce«. Prikaže se modul z dodatnimi informacijami.

Najprej morate izbrati »Ločeno« ali »Fiksna širina«.

  • »Ločeno« pomeni, da želite razstaviti stolpec na podlagi znakov, kot so vejice, presledki ali zavihki.
  • »Fiksna širina« pomeni, da želite v vseh stolpcih izbrati natančno lokacijo, za katero želite, da se zgodi delitev.

V spodnjem primeru izberite »Ločeno«, da bomo lahko polno ime ločili na ime in priimek.

Potem je čas, da izberemo Ločila. To je lahko zavihek, podpičje, vejica, presledek ali kaj drugega. (»Nekaj ​​drugega« je lahko na primer znak »@«, ki se uporablja v e-poštnem naslovu.) V našem primeru izberite prostor. Excel vam bo nato prikazal predogled, kako bodo videti novi stolpci.

Ko ste zadovoljni s predogledom, pritisnite »Naprej«. Na tej strani boste lahko izbrali napredne formate. Ko končate, kliknite »Končaj«.

Orodje Excel besedilo v stolpec

7. Uporabite formule za enostavne izračune.

Poleg precej zapletenih izračunov vam lahko Excel pomaga tudi pri preprostih računih, kot je sestavljanje, odštevanje, množenje ali deljenje podatkov.

  • Če želite dodati, uporabite znak +.
  • Za odštevanje uporabite znak -.
  • Če želite pomnožiti, uporabite znak *.
  • Za delitev uporabite znak /.

S oklepaji lahko tudi zagotovite, da so najprej opravljeni določeni izračuni. V spodnjem primeru (10 + 10 * 10) smo drugo in tretjo 10 pomnožili skupaj, preden smo dodali dodatnih 10. Če pa bi to naredili (10 + 10) * 10, bi se najprej sestavili prvi in ​​drugi 10 .

Excel preproste formule v akciji

8. Pridobite povprečje številk v celicah.

Če želite povprečje nabora števil, lahko uporabite formulo = POVPREČNO (celica1: celica2). Če želite povzeti stolpec števil, lahko uporabite formulo = SUM (celica1: celica2).

9. Uporabite pogojno oblikovanje, da celice samodejno spremenijo barvo na podlagi podatkov.

Pogojno oblikovanje vam omogoča spreminjanje barve celice glede na informacije v celici. Če želite na primer označiti nekatere številke, ki so nadpovprečne ali v zgornjih 10% podatkov v preglednici, lahko to storite. Če želite v Excelu obarvati skupne kode med različnimi vrsticami, lahko to storite. To vam bo pomagalo hitro videti informacije, ki so za vas pomembne.

Za začetek označite skupino celic, za katere želite uporabiti pogojno oblikovanje. Nato v meniju Domov izberite »Pogojno oblikovanje« in v spustnem meniju izberite svojo logiko. (Če želite nekaj drugačnega, lahko ustvarite tudi svoje pravilo.) Pojavi se okno, ki vas pozove, da navedete več informacij o svojem pravilu oblikovanja. Ko končate, izberite »V redu« in vaši rezultati se bodo samodejno prikazali.

Pogojno oblikovanje v Excelu

10. Uporabite formulo IF Excel za avtomatizacijo nekaterih Excelovih funkcij.

Včasih ne želimo šteti, kolikokrat se vrednost pojavi. Namesto tega želimo v celico vnesti različne informacije, če obstaja ustrezna celica s temi informacijami.

Na primer, v spodnji situaciji želim dodeliti deset točk vsem, ki pripadajo hiši Gryffindor. Namesto da ročno vtipkam desetice poleg imena vsakega študenta Gryffindorja, lahko s formulo IF Excel rečem, da če je študent v Gryffindorju, potem bi moral dobiti deset točk.

Formula je: ČE (logični_test, vrednost_pri_true, [vrednost_pri_pravi]]

Primer, prikazan spodaj: = IF (D2 = "Gryffindor", "10", "0")

Na splošno bi bila formula IF (logični test, vrednost true, vrednost false). Poglobimo se v vsako od teh spremenljivk.

  • Logični_TEST: Logični test je del izjave "IF". V tem primeru je logika D2 = "Gryffindor", ker želimo zagotoviti, da celica, ki ustreza študentu, izpiše "Gryffindor." Tu obvezno postavite Gryffindor v narekovaje.
  • Value_if_Tue: To je tisto, kar želimo, da celica prikaže, če je vrednost resnična. V tem primeru želimo, da celica kaže »10«, da je študent prejel 10 točk. Navedite le, če želite, da je rezultat besedilo namesto števila.
  • Vrednost_če_False: To je tisto, kar želimo, da celica prikaže, če je vrednost false. V tem primeru želimo, da kateri koli študent, ki ni v Gryffindorju, v celici prikaže "0". Navedite le, če želite, da je rezultat besedilo namesto števila.

Excel IF formula v akciji

Opombe: V zgornjem primeru sem podelil 10 točk vsem v Gryffindorju. Če bi pozneje želel sešteti skupno število točk, tega ne bi mogel, ker so desetice v narekovajih, tako da so besedila in ne število, ki ga lahko sešteje Excel.

11. Uporabite dolarske znake, da formula ene celice ostane enaka, ne glede na to, kam se premika.

Ste že kdaj videli dolarski znak v Excelovi formuli? Ko se uporablja v formuli, ne predstavlja ameriškega dolarja; namesto tega zagotavlja, da se natančno nahajata stolpec in vrstica, tudi če kopirate isto formulo v sosednje vrstice.

Saj je sklic na celico - ko se na primer sklicujete na celico A5 iz celice C5 - privzeto relativen. V tem primeru se dejansko sklicujete na celico, ki je pet stolpcev na levi (C minus A) in v isti vrstici (5). To se imenuje relativna formula. Ko kopirate relativno formulo iz ene celice v drugo, bo prilagodila vrednosti v formuli glede na to, kam je bila premaknjena. Toda včasih želimo, da te vrednosti ostanejo enake, ne glede na to, ali se premikajo ali ne - in to lahko storimo tako, da formulo spremenimo v absolutno formulo.

Če želite relativno formulo (= A5 + C5) spremeniti v absolutno formulo, bi pred vrednostmi vrstic in stolpcev stopili z dolarskimi znaki, kot je ta: (= $ A $ 5 + $ C $ 5). (Več o tem na strani za podporo za Microsoft Office tukaj.)

12. S funkcijo VLOOKUP povlecite podatke z enega območja lista na drugega.

Ste že kdaj imeli dve skupini podatkov v dveh različnih preglednicah, ki jih želite združiti v eno preglednico?

Na primer, v eni preglednici imate lahko na primer seznam imen ljudi poleg njihovih e-poštnih naslovov, v drugi pa e-poštne naslove istih ljudi poleg imen njihovih podjetij, vendar želite imena, e-poštne naslove in imena podjetij. ljudi na enem mestu.

Takšne nabore podatkov moram veliko kombinirati - in kadar to storim, je VLOOKUP moja formula za uporabo.

Preden uporabite formulo, pa se prepričajte, da imate vsaj en stolpec, ki je enak na obeh mestih. Prebrskajte svoje nabore podatkov, da se prepričate, da je stolpec podatkov, ki ga uporabljate za združevanje podatkov, popolnoma enak, brez dodatnih presledkov.

Formula: = VLOOKUP (iskalna vrednost, matrična tabela, številka stolpca, približno ujemanje (TRUE) ali natančno ujemanje (FALSE))

Formula s spremenljivkami iz spodnjega primera: = PREGLED (C2, List2! A: B, 2, FALSE)

V tej formuli je več spremenljivk. Naslednje velja, če želite združiti podatke iz lista 1 in lista 2 na list 1.

  • Iskalna vrednost: To je enaka vrednost, ki jo imate v obeh preglednicah. Izberite prvo vrednost v prvi preglednici. V primeru, ki sledi, to pomeni prvi e-poštni naslov na seznamu ali celica 2 (C2).
  • Tabela Array: Polje tabele je obseg stolpcev na listu 2, iz katerega boste povlekli podatke, vključno s stolpcem podatkov, ki je enak vaši iskalni vrednosti (v našem primeru e-poštni naslovi) v listu 1, pa tudi stolpec podatkov poskušate kopirati na list 1. V našem primeru je to "List2! A: B." "A" pomeni stolpec A v listu 2, ki je stolpec v listu 2, kjer so navedeni podatki, enaki naši iskalni vrednosti (e-poštni naslov) v listu 1. "B" pomeni stolpec B, ki vsebuje informacije, ki so na voljo samo v listu 2, ki jih želite prevesti v list 1.
  • Številka stolpca: To pove Excelu, v katerem stolpcu se nahajajo novi podatki, ki jih želite kopirati na list 1. V našem primeru bi bil to stolpec, v katerem se nahaja »Hiša«. »Hiša« je drugi stolpec v našem obsegu stolpcev ( tabela), zato je naša številka stolpca 2. [Opombe: Vaš obseg je lahko več kot dva stolpca. Če so na primer na listu 2 na primer trije stolpci - e-pošta, starost in hiša - in še vedno želite hišo vključiti na list 1, lahko še vedno uporabite VLOOKUP. Preprosto spremenite "2" v "3", tako da povleče vrednost v tretjem stolpcu: = VLOOKUP (C2: Sheet2! A: C, 3, false).]
  • Približno ujemanje (TRUE) ali natančno ujemanje (FALSE): Uporabite FALSE, da zagotovite, da vnesete samo natančna ujemanja vrednosti. Če uporabite TRUE, bo funkcija potegnila približna ujemanja.

V spodnjem primeru list 1 in list 2 vsebujeta seznama, ki opisujejo različne informacije o istih ljudeh, skupna nit med obema pa sta njihova e-poštna naslova. Recimo, da želimo združiti oba nabora podatkov, tako da se vse informacije o hiši iz lista 2 prevedejo v list 1.

Excel VLOOKUP funkcija

Torej, ko vtipkamo formulo = PREGLED (C2, List2! A: B, 2, FALSE), vse podatke o hišah vnesemo v list 1.

Upoštevajte, da bo VLOOKUP potegnil nazaj le vrednosti z drugega lista, ki so na desni strani stolpca z vašimi enakimi podatki. To lahko privede do nekaterih omejitev, zato nekateri raje raje uporabljajo funkcije INDEX in MATCH.

13. Za vlečenje podatkov iz vodoravnih stolpcev uporabite formuli INDEX in MATCH.

Tako kot VLOOKUP tudi funkcije INDEX in MATCH povlečejo podatke iz drugega nabora podatkov na eno osrednje mesto. Tu so glavne razlike:

  • VLOOKUP je veliko preprostejša formula. Če delate z velikimi nabori podatkov, ki bi zahtevali na tisoče iskanj, bo uporaba funkcij INDEX in MATCH znatno zmanjšala čas nalaganja v Excelu.
  • Formuli INDEX in MATCH delujeta od desne proti levi, medtem ko formule VLOOKUP delujejo le kot iskanje od leve proti desni. Z drugimi besedami, če morate opraviti iskanje, ki ima stolpec za iskanje na desni strani stolpca z rezultati, bi morali te stolpce preurediti, da naredite VLOOKUP. To je lahko dolgočasno pri velikih naborih podatkov in / ali vodi do napak.

Torej, če želim združiti informacije v listu 1 in listu 2 na list 1, vendar vrednosti stolpcev v listih 1 in 2 niso enake, potem bi za VLOOKUP moral preklopiti med stolpci. V tem primeru bi se raje odločil za INDEX in MATCH.

Oglejmo si primer. Recimo, da list 1 vsebuje seznam imen ljudi in njihove e-poštne naslove v Hogwartsu, list 2 pa seznam e-poštnih naslovov ljudi in pokrovitelja, ki ga ima vsak študent. (Za oboževalce, ki niso Harry Potter tam zunaj, ima vsaka čarovnica ali skrbnik živali, imenovan "Patronus", povezan z njim.) Podatki, ki živijo v obeh listih, so stolpci z e-poštnimi naslovi, vendar ta stolpec je v različnih številkah stolpcev na vsakem listu. Namesto VLOOKUP bi uporabil formuli INDEX in MATCH, tako da mi ne bi bilo treba zamenjati nobenega stolpca.

Kakšna je torej formula? Formula je pravzaprav formula MATCH, ugnezdena znotraj formule INDEX. Videli boste, da sem tukaj razlikoval formulo MATCH z drugo barvo.

Formula: = INDEX (matrična tabela, formula MATCH)

To postane: = INDEX (matrična tabela, MATCH (iskalna_vrednost, iskalna_matrika))

Formula s spremenljivkami iz spodnjega primera: = KAZALO (List2! A: A, (UGARNICA (List1! C: C, List2! C: C, 0)))

Tu so spremenljivke:

  • Tabela Array: Obseg stolpcev na listu 2, ki vsebujejo nove podatke, ki jih želite prenesti na list 1. V našem primeru "A" pomeni stolpec A, ki vsebuje informacije "Patronus" za vsako osebo.
  • Iskalna vrednost: To je stolpec v listu 1, ki vsebuje enake vrednosti v obeh preglednicah. V primeru, ki sledi, to pomeni stolpec »e-pošta« na listu 1, ki je stolpec C. Torej: List1! C: C.
  • Iskalno polje: To je stolpec v listu 2, ki vsebuje enake vrednosti v obeh preglednicah. V primeru, ki sledi, se to nanaša na stolpec »e-pošta« na listu 2, ki je prav tako tudi stolpec C. Torej: List2! C: C.

Ko imate spremenljivke naravnost, vnesite formuli INDEX in MATCH v zgornjo celico praznega stolpca Patronus na listu 1, kjer želite, da kombinirane informacije živijo.

Funkcije Excel INDEX in MATCH delujejo

14. S funkcijo COUNTIF lahko Excel šteje besede ali številke v poljubnem obsegu celic.

Namesto da ročno štejete, kako pogosto se določena vrednost ali številka prikaže, pustite, da Excel opravi delo namesto vas. S funkcijo COUNTIF lahko Excel šteje, kolikokrat se beseda ali številka pojavi v katerem koli obsegu celic.

Recimo na primer, da želim prešteti, kolikokrat se beseda »Gryffindor« pojavi v mojem naboru podatkov.

Formula: = COUNTIF (obseg, merila)

Formula s spremenljivkami iz spodnjega primera: = ŠTEVILO (D: D, "Gryffindor")

V tej formuli je več spremenljivk:

  • Območje: Obseg, ki ga želimo, da formula pokriva. Ker se v tem primeru osredotočamo samo na en stolpec, z "D: D" označimo, da sta prvi in ​​zadnji stolpec D. Če bi gledal stolpca C in D, bi uporabil "C: D . "
  • Merila: Ne glede na število ali del besedila, ki ga želite, da Excel šteje. Navedite le, če želite, da je rezultat besedilo namesto števila. V našem primeru je merilo "Gryffindor."

Preprosto vtipkanje formule COUNTIF v katero koli celico in pritisk na tipko Enter bo pokazal, kolikokrat se v naboru podatkov prikaže beseda "Gryffindor".

Funkcija Excel COUNTIF

15. Kombinirajte celice z &.

Zbirke podatkov ponavadi ločujejo podatke, da so čim bolj natančne. Na primer, namesto stolpca, ki prikazuje polno ime osebe, ima lahko baza podatkov podatke kot ime in nato priimek v ločenih stolpcih. Lahko pa ima tudi lokacijo osebe, ločeno po mestu, državi in ​​poštni številki. V Excelu lahko združite celice z različnimi podatki v eno celico z uporabo znaka "&" v svoji funkciji.

Formula s spremenljivkami iz spodnjega primera: = A2 & "" & B2

Pojdimo skozi formulo skupaj na primeru. Pretvarjamo se, da želimo v enem stolpcu združiti imena in priimke v polna imena. Če želite to narediti, najprej postavimo kazalec v prazno celico, kjer želimo, da se prikaže polno ime. Nato označimo eno celico, ki vsebuje ime, vtipkamo znak »&« in nato celico z ustreznim priimkom.

Ampak še niste končali - če je vse, kar vnesete = A2 in B2, med imenom in priimkom osebe ne bo presledka. Če želite dodati ta potreben prostor, uporabite funkcijo = A2 & "" & B2. Navedeni narekovaji okoli presledka navajajo Excelu, naj vstavi presledek med imenom in priimkom.

Če želite to narediti za več vrstic, preprosto povlecite vogal prve celice navzdol, kot je prikazano v primeru.

Excel kombinacija celic

16. Dodajte potrditvena polja.

Če za spremljanje podatkov o strankah uporabljate Excelov list in želite nadzorovati nekaj, kar ni merljivo, lahko v stolpec vstavite potrditvena polja.

Če na primer z Excelovim listom upravljate svoje prodajne možnosti in želite slediti, ali ste jih poklicali v zadnjem četrtletju, bi lahko imeli možnost »Called this quarter?« v stolpcu in označite celice v njem, ko pokličete ustreznega odjemalca.

Evo, kako to storiti.

V preglednici označite celico, v katero želite dodati potrditvena polja. Nato kliknite RAZVOJNIK. Nato v razdelku NADZOR OBLIK potrdite polje ali izbirni krog, označen na spodnji sliki.

Excelova potrditvena polja

Ko se polje prikaže v celici, ga kopirajte, označite celice, v katerih želite, da se prikaže, in ga nato prilepite.

17. Hiperpovezava celice do spletnega mesta.

Če svoj list uporabljate za sledenje meritvam družabnih medijev ali spletnih mest, je lahko koristno, če imate referenčni stolpec s povezavami, ki jim sledi vsaka vrstica. Če dodate URL neposredno v Excel, bi moral biti samodejno klikljiv. Če pa morate hiperpovezati besede, na primer naslov strani ali naslov objave, ki ji sledite, sledite navodilom.

Označite besede, ki jih želite postaviti na hiperpovezavo, nato pritisnite Shift K. Od tam se bo prikazalo polje, ki vam omogoča, da postavite URL hiperpovezave. Kopirajte in prilepite URL v to polje in pritisnite ali kliknite Enter.

Če bližnjica iz katerega koli razloga ne deluje, lahko to storite tudi ročno, tako da označite celico in kliknete Vstavi > Hiperpovezava.

18. Dodajte spustne menije.

Včasih boste s svojo preglednico spremljali procese ali druge kvalitativne stvari. Namesto da na list ponavljate besede, na primer »Da«, »Ne«, »Stališče kupca«, »Prodajni potencial« ali »Prospekt«, lahko s spustnimi meniji hitro označite opisne stvari o svojih stikih ali kar koli drugega sledim.

Tu je opisano, kako celicam dodate spustne menije.

Označite celice, v katerih naj bodo spustni meniji, nato kliknite meni Podatki v zgornji vrstici za krmarjenje in pritisnite Preverjanje

Možnost spustnega menija Excel

Od tam boste videli polje Nastavitve preverjanja veljavnosti podatkov. Oglejte si možnosti Dovoli, nato kliknite Seznami in izberite Spustni seznam. Preverite spustni gumb In-Cell in pritisnite OK.

Drugi viri pomoči za Excel

Uporabite Excel za avtomatizacijo procesov v svoji skupini

Tudi če niste računovodja, lahko še vedno uporabljate Excel za avtomatizacijo nalog in procesov v svoji skupini. Z nasveti in triki, ki smo jih delili v tej objavi, boste zagotovo uporabili Excel v največji možni meri in kar najbolje izkoristili programsko opremo za rast vašega podjetja.

 

Original člen