Okuqukethwe
Uma udinga ukuhlunga uhlu, kunezindlela eziningi enkonzweni yakho, elula kunazo zonke izinkinobho zokuhlunga kuthebhu noma kumenyu. Idatha (Idatha - Hlunga). Nokho, kunezimo lapho ukuhlungwa kohlu kudinga ukwenziwa ngokuzenzakalelayo, okungukuthi amafomula. Lokhu kungase kudingeke, isibonelo, lapho kukhiqizwa idatha yohlu oluya phansi, lapho kubalwa idatha yamashadi, njll. Luhlungwa kanjani uhlu ngefomula ngokushesha?
Indlela 1. Idatha yezinombolo
Uma uhlu luqukethe ulwazi lwezinombolo kuphela, ukuluhlela kungenziwa kalula kusetshenziswa imisebenzi OKUPHELELE (OKUNCANE) и LINE (UMUGQA):
Umsebenzi OKUPHELELE (OKUNCANE) ikhipha kumalungu afanayo (ikholomu A) into encane kakhulu engu-n-th emugqeni. Labo. OKUNCANE(A:A;1) inombolo encane kunawo wonke kukholamu, EMNCANE(A:A;2) eyesibili encane, njalo njalo.
Umsebenzi LINE (UMUGQA) ibuyisela inombolo yomugqa yeseli eshiwo, okungukuthi ROW(A1)=1, ROW(A2)=2 njll. Kulesi simo, isetshenziswa kalula njengendawo ekhiqizayo yokulandelana kwezinombolo n=1,2,3… uhlu lwethu oluhleliwe. Ngempumelelo efanayo, kube nokwenzeka ukwenza ikholomu eyengeziwe, uyigcwalise ngesandla ngokulandelana kwezinombolo 1,2,3 … futhi ubhekisele kuyo esikhundleni somsebenzi othi ROW.
Indlela 2. Uhlu lombhalo kanye namafomula avamile
Uma uhlu luqukethe izinombolo, kodwa umbhalo, khona-ke umsebenzi ONCINANE ngeke usasebenza, ngakho-ke kufanele uhambe ngendlela ehlukile, ende kancane.
Okokuqala, ake sengeze ikholomu yesevisi enefomula lapho inombolo ye-serial yegama ngalinye ohlwini oluhleliwe oluzayo izobalwa kusetshenziswa umsebenzi. COUNTIF (COUNTIF):
Enguqulweni yesiNgisi kuzoba:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Itemu lokuqala liwumsebenzi wokubala inani lamaseli angaphansi kwaleli lamanje. Eyesibili iyinetha yokuphepha uma kwenzeka noma yiliphi igama livela izikhathi ezingaphezu kwesisodwa. Khona-ke ngeke babe nezinombolo ezifanayo, kodwa zikhula ngokulandelana.
Manje izinombolo ezitholiwe kufanele zihlelwe ngokulandelana ngendlela ekhuphukayo. Ukuze wenze lokhu ungasebenzisa umsebenzi OKUPHELELE (OKUNCANE) ngendlela yokuqala:
Nokho, ekugcineni, kusasele nje ukukhipha amagama ohlwini ngezinombolo zawo. Ukuze wenze lokhu, ungasebenzisa ifomula elandelayo:
Umsebenzi OKUNINGI KUDANWE (IMESHI) isesha kukholomu B ukuthola inombolo ye-serial efunwayo (1, 2, 3, njll.) futhi, empeleni, ibuyisela inombolo yomugqa lapho le nombolo itholakala khona. Umsebenzi INDEX (INDEX) ikhipha kukholamu A igama kule nombolo yomugqa.
Indlela 3: I-Array Formula
Le ndlela, eqinisweni, iyi-algorithm efanayo yokubeka njengakuNdlela-2, kodwa isetshenziswe ngefomula yamalungu afanayo. Ukwenza ifomula ibe lula, ububanzi bamaseli C1:C10 bunikezwe igama Uhlu (khetha amaseli, cindezela I-Ctrl + F3 nenkinobho Dala):
Kuseli E1, kopisha ifomula yethu:
=INHLOKO(Uhlu; UMESHI(OMNCANE(COUNTIF(Uhlu;); “<"&Uhlu); Umugqa(1:1)); COUNTIF(Uhlu; "<"&Uhlu); 0))
Noma enguqulweni yesiNgisi:
=INDEX(Uhlu, FANISA(OKUNCINCI(COUNTIF(Uhlu, «<"&Uhlu), UMGQUBO(1:1)), COUNTIF(Uhlu, "<"&Uhlu), 0))
bese ucindezela Ctrl + Shift + Fakaukuyifaka njengefomula yamalungu afanayo. Bese ifomula ewumphumela ingakopishwa phansi bonke ubude bohlu.
Uma ufuna ukuthi ifomula icabangele hhayi ububanzi obugxilile, kodwa ukwazi ukulungisa lapho wengeza izici ezintsha ohlwini, uzodinga ukushintsha kancane isu.
Okokuqala, uhla Lohlu luzodinga ukusethwa ngamandla. Ukuze wenze lokhu, lapho udala, udinga ukucacisa hhayi uhla olumisiwe C3:C10, kodwa ifomula ekhethekile ezobhekisela kuwo wonke amanani atholakalayo, kungakhathaliseki inombolo yawo. Chofoza I-Alt + F3 noma uvule ithebhu Amafomula – Isiphathi Segama (Amafomula — Umphathi Wegama), dala igama elisha nasensimini isixhumanisi (Inkomba) faka ifomula elandelayo (ngicabanga ukuthi ububanzi bedatha ezohlungwa buqala kuseli C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Okwesibili, ifomula yamalungu afanayo engenhla izodinga ukwelulwa phansi ngomkhawulo - ngokulindela idatha eyengeziwe efakwe esikhathini esizayo. Kulokhu, ifomula yamalungu afanayo izoqala ukunikeza iphutha #NUMBER kumaseli angakagcwaliswa. Ukuyinqamula, ungasebenzisa umsebenzi IFERROR, edinga ukwengezwa "nxazonke" ifomula yethu yamalungu afanayo:
=I-IFERROR(INHLOKO(Uhlu; UMESHI(OMNCINCI(COUNTIF(Uhlu;); “<"&Uhlu); UMGWEBO(1:1)); COUNTIF(Uhlu; "<"&Uhlu); 0));»»)
=IFERROR(INDEX(Uhlu, FANSI(EMNCINCI(COUNTIF(Uhlu, «<"&Uhlu), UMGWAQO(1:1)), COUNTIF(Uhlu, "<"&Uhlu), 0));"")
Ibamba iphutha elingu-#NUMBER bese ikhipha okuyize (izingcaphuno ezingenalutho) esikhundleni salokho.
:
- Hlunga ububanzi ngombala
- Ayini amafomula afanayo futhi kungani edingeka
- HLUNGA ukuhlela kanye namalungu afanayo ashukumisayo ku-Office 365 entsha