Okuqukethwe
Ingabe unawo amathebula anedatha ku-Excel engashintshwa usayizi, okungukuthi inani lemigqa (amakholomu) lingakhuphuka noma lehle ngesikhathi somsebenzi? Uma amasayizi wethebula "entanta", kuzodingeka ukuthi uhlale uqapha lesi sikhathi futhi usilungise:
- izixhumanisi kumafomula ombiko abhekisela etafuleni lethu
- ububanzi bokuqala bamathebula e-pivot akhiwe ngokuya ngetafula lethu
- ububanzi bokuqala bamashadi akhiwe ngokwetafula lethu
- ububanzi bokwehliswayo okusebenzisa ithebula lethu njengomthombo wedatha
Konke lokhu sekukonke ngeke kukuvumele ukuthi ubhore 😉
Kuzoba lula kakhulu futhi kulunge kakhulu ukwakha ububanzi “berabha” obuguquguqukayo, obuzolungisa ngokuzenzakalelayo usayizi enanini langempela lemigqa namakholomu edatha. Ukuze wenze lokhu, kunezindlela eziningana.
Indlela 1. Ithebula elihlakaniphile
Gqamisa ibanga lakho lamaseli bese ukhetha kuthebhu Ikhaya - Fometha Njengethebula (Ekhaya - Fometha Njengethebula):
Uma ungawudingi umklamo onemigqa owengezwa etafuleni njengomphumela oseceleni, ungawuvala kuthebhu evelayo. Umakhi (Umklamo). Ithebula ngalinye elidalwe ngale ndlela lithola igama elingashintshwa libe elifaneleka kakhulu endaweni efanayo kuthebhu Umakhi (Umklamo) ensimini Igama lethebula (Igama Lethebula).
Manje singasebenzisa izixhumanisi eziguqukayo "kuthebula elihlakaniphile" lethu:
- Ithebula 1 - isixhumanisi setafula lonke ngaphandle komugqa wesihloko (A2:D5)
- Ithebula1[#Konke] - isixhumanisi setafula lonke (A1:D5)
- Ithebula1[Peter] – ireferensi yekholomu yobubanzi ngaphandle kweheda yeseli yokuqala (C2:C5)
- Ithebula1[#Izihloko] – isixhumanisi “kusihloko” esinamagama amakholomu (A1:D1)
Izinkomba ezinjalo zisebenza kahle kumafomula, isibonelo:
= I-SUM (Ithebula1[eMoscow]) - ukubalwa kwesamba sekholomu "eMoscow"
or
=VPR(F5;Ithebula 1;3;0) – sesha kuthebula lenyanga kuseli F5 bese ukhipha isamba sayo sase-St. Petersburg (yini i-VLOOKUP?)
Izixhumanisi ezinjalo zingasetshenziswa ngempumelelo uma udala amathebula e-pivot ngokukhetha kuthebhu Faka – Ithebula Le-Pivot (Faka – Ithebula Le-Pivot) kanye nokufaka igama lethebula elihlakaniphile njengomthombo wedatha:
Uma ukhetha ucezu lwetafula elinjalo (isibonelo, amakholomu amabili okuqala) bese udala umdwebo wanoma yiluphi uhlobo, khona-ke lapho ungeza imigqa emisha, zizokwengezwa ngokuzenzakalelayo kumdwebo.
Lapho udala izinhlu zokudonsela phansi, izixhumanisi eziqondile eziya ezintweni zetafula ezihlakaniphile azikwazi ukusetshenziswa, kodwa ungakwazi ukunqanda lo mkhawulo usebenzisa iqhinga lobuqili - sebenzisa umsebenzi. INDALO (INDIRECT), okuguqula umbhalo ube isixhumanisi:
Labo. isixhumanisi setafula elihlakaniphile ngendlela yeyunithi yezinhlamvu yombhalo (ezimpawu zokucaphuna!) iphenduka isixhumanisi esigcwele, futhi uhlu lokudonsela phansi luvame ukukubona.
Indlela yesi-2: Ibanga legama elinamandla
Uma ukuguqula idatha yakho ibe ithebula elihlakaniphile kungafuneki ngesizathu esithile, ungasebenzisa indlela eyinkimbinkimbi, kodwa ecashile kakhulu futhi eguquguqukayo - dala ububanzi obuqanjwe ashukumisayo ku-Excel obubhekisela etafuleni lethu. Bese, njengasendabeni yetafula elihlakaniphile, ungasebenzisa ngokukhululekile igama lebanga elidaliwe kunoma imaphi amafomula, imibiko, amashadi, njll. Ake siqale ngesibonelo esilula:
Umsebenzi: yenza ububanzi obunegama obuguqukayo obuzobhekisela kuhlu lwamadolobha futhi lunwebe ngokuzenzakalelayo futhi lunciphe ngosayizi lapho wengeza amadolobha amasha noma uwasusa.
Sizodinga imisebenzi emibili ye-Excel eyakhelwe ngaphakathi etholakala kunoma iyiphi inguqulo − I-POICPOZ (IMESHI) ukucacisa iseli lokugcina lobubanzi, kanye INDEX (INDEX) ukuze udale isixhumanisi esiguqukayo.
Ukuthola iseli lokugcina kusetshenziswa okuthi MATCH
MATCH(inani_lokubheka, ububanzi, uhlobo_lokufanisa) – umsebenzi osesha inani elinikeziwe kububanzi (umugqa noma ikholomu) futhi ibuyisela inombolo ye-ordinal yeseli lapho itholwe khona. Isibonelo, ifomula MATCH(“March”;A1:A5;0) izobuyisela inombolo 4 njengomphumela, ngoba igama elithi “March” litholakala kuseli lesine kukholomu A1:A5. Ukuphikisana komsebenzi wokugcina Match_Type = 0 kusho ukuthi sifuna okufanayo ncamashi. Uma le mpikiswano ingacacisiwe, umsebenzi uzoshintshela kumodi yokusesha ukuze uthole inani elincane eliseduze - yilokhu kanye okungasetshenziswa ngempumelelo ukuthola iseli lokugcina elibanjwe ohlwini lwethu.
Ingqikithi yeqhinga ilula. I-MATCH isesha amaseli kububanzi obusuka phezulu kuye phansi, futhi, ngokombono, kufanele ime lapho ithola inani elincane eliseduze kunalelo elinikeziwe. Uma ucacisa inani ngokusobala elikhulu kunanoma yiliphi elitholakala kuthebula njengenani elifiswayo, khona-ke okuthi MATCH kuzofika ekugcineni kwetafula, kungatholi lutho futhi kunikeze inombolo yokulandelana kweseli lokugcina eligcwalisiwe. Futhi siyayidinga!
Uma kunezinombolo kuphela ohlwini lwethu, khona-ke singacacisa inombolo njengenani elifunekayo, ngokusobala elikhulu kunanoma iyiphi yalezi ezisethebulani:
Ukuze uthole isiqinisekiso, ungasebenzisa inombolo 9E + 307 (9 izikhathi 10 emandleni angu-307, okungukuthi 9 ngo-307 zero) - inombolo enkulu i-Excel engasebenza nayo ngokomthetho.
Uma kukhona amanani ombhalo kukholomu yethu, njengenani elilingana nenani elikhulu kunawo wonke ngangokunokwenzeka, ungafaka ukwakhiwa kwe-REPEAT(“i”, 255) – iyunithi yezinhlamvu yombhalo enezinhlamvu ezingama-255 “i” – uhlamvu lokugcina izinhlamvu zamagama. Njengoba i-Excel empeleni iqhathanisa amakhodi ezinhlamvu lapho isesha, noma yimuphi umbhalo kuthebula lethu ngokwezobuchwepheshe uzoba “mncane” kunomugqa omude othi “yyyyyyyyyyyyy”:
Dala isixhumanisi usebenzisa i-INDEX
Manje njengoba sesiyazi indawo yento yokugcina engenalutho kuthebula, kusasele ukwenza isixhumanisi salo lonke uhla lwethu. Kulokhu sisebenzisa umsebenzi:
INDEX(ububanzi; inombolo_yomugqa; inombolo_yekholomu)
Inikeza okuqukethwe kweseli ukusuka kububanzi ngomugqa kanye nenombolo yekholomu, okungukuthi isibonelo, umsebenzi =INDEX(A1:D5;3;4) kuthebula lethu elinamadolobha kanye nezinyanga zendlela yangaphambilini izonikeza 1240 - okuqukethwe. kusukela kumugqa wesi-3 nakukholomu yesi-4, okungukuthi amaseli D3. Uma kunekholomu eyodwa kuphela, khona-ke inombolo yayo ingashiywa, okungukuthi INHLOKO YEfomula(A2:A6;3) izonikeza okuthi “Samara” kusithombe-skrini sokugcina.
Futhi kune-nuance eyodwa engabonakali ngokuphelele: uma i-INDEX ingangeni nje esitokisini ngemuva kophawu =, njengenjwayelo, kodwa isetshenziswa njengengxenye yokugcina yereferensi kububanzi obungemva kwekholoni, lapho-ke ayisakhiphi. okuqukethwe kweseli, kodwa ikheli layo! Ngakho, ifomula efana ne-$A$2:INDEX($A$2:$A$100;3) izonikeza ireferensi kububanzi A2:A4 ekukhishweni.
Futhi kulapho umsebenzi we-MATCH ungena khona, esiwufaka ngaphakathi kwe-INDEX ukuze sinqume ngokuguquguqukayo ukuphela kohlu:
=$A$2:INDEX($A$2:$A$100; UMESHI(REP(“Mina”;255);A2:A100))
Dala ububanzi obuqanjwe igama
Kusele ukukupakisha konke kuphelele. Vula ithebhu formula (Amafomula) Bese uqhafaza Umphathi wegama (Umphathi Wegama). Ewindini elivulayo, chofoza inkinobho Dala (okusha), faka igama lethu lebanga nefomula kunkambu Ibanga (Inkomba):
Kusele ukuchofoza OK futhi ububanzi obulungile bungasetshenziswa kunoma yimaphi amafomula, uhlu lokudonsela phansi noma amashadi.
- Kusetshenziswa umsebenzi we-VLOOKUP ukuxhumanisa amathebula namanani okubheka
- Ulwakha kanjani uhlu lokudonsela phansi olugcwala ngokuzenzakalela
- Ungadala kanjani ithebula le-pivot ukuze uhlaziye inani elikhulu ledatha