Okuqukethwe
Uma unohlu olukhulu oluhlelwe ngekholomu ethile, kungaba kuhle ukuhlukanisa ngokuzenzakalelayo amasethi omugqa ahlukanise imigqa evundlile ukuze kucace:
Esibonelweni esingenhla, lena imigqa ephakathi kwamazwe, kodwa, ngokuvamile, phakathi kwanoma yiziphi izinto eziphindaphindiwe kukholamu efanayo. Ake sibheke izindlela ezimbalwa zokuqalisa lokhu.
Indlela 1. Ilula
Indlela eshesha kakhulu yokwenza lokhu ilula kakhulu ngokufometha okunemibandela, okuzodweba umngcele ongezansi wamaseli uma okuqukethwe kweseli kukholomu A kungalingani nokuqukethwe kweseli elilandelayo kukholamu efanayo. Khetha wonke amaseli kuthebula ngaphandle kweheda bese ukhetha The main ithebhu yomyalo Ukufometha Okunemibandela - Dala Umthetho (Ikhaya - Ukufometha Okunemibandela - Umthetho Omusha). Khetha uhlobo lomthetho Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ozofometha (Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ozofometha) bese ufaka ifomula elandelayo ensimini:
Naka amadola emakhelini ukuze ulungise izinhlamvu zekholomu, kodwa hhayi izinombolo zomugqa, ngoba. siqhathanisa kuphela amazwe akukholomu A. Akufanele kube nezikhala kufomula.
Chofoza inkinobho Framework (Ifomethi) nasefasiteleni elivuliwe kuthebhu Border (Imingcele) vula umugqa wombala oyifunayo emngceleni ongezansi. Ngemva kokuchofoza OK umthetho wethu uzosebenza futhi imigqa egijimayo evundlile izovela phakathi kwamaqembu emigqa
Indlela 2. Ngokusekela kokuhlunga izinombolo nezinsuku
Ububi obuncane kodwa obuphawuleka kakhulu bendlela yokuqala ukuthi imingcele enjalo ngeke ihlale isebenza kahle lapho ihlunga uhlu ngamanye amakholomu. Ngakho-ke, isibonelo, uma sihlunga ithebula lethu ngezinsuku (ngoJanuwari kuphela), imigqa ngeke isabonakala phakathi kwawo wonke amazwe, njengangaphambili:
Kulokhu, ungakwazi ukuphuma usebenzisa umsebenzi IZITHOMBE ZAMABILI (SUBTOTAL), engenza imisebenzi yezibalo ehlukahlukene (isamba, isilinganiso, ukubala, njll.), kodwa "bona" amaseli ahlungiwe kuphela. Isibonelo, ake sihlele ithebula lethu ngekholomu yokugcina enosuku bese sidweba umugqa ohlukanisayo phakathi kwezinsuku. Ekufometheni okunemibandela, kuzodingeka udale isimiso esifana nendlela yokuqala, kodwa ungasebenzisi izixhumanisi eziqondile ekuqhathaniseni amaseli D2 kanye no-D3, kodwa uwavale njengama-agumenti kumsebenzi othi SUBTOTAL:
Impikiswano yokuqala yomsebenzi (inombolo 109) i-opcode yokuhlanganisa. Eqinisweni, asengezi lutho lapha futhi senza, empeleni, ukusebenza okuwubuwula okufana ne-SUM (D2), okuyinto, vele, elingana no-D2. Kodwa lo msebenzi wehluka ku-SUM ngokuqondile ngoba wenza izenzo kumaseli abonakalayo kuphela, okungukuthi namaseli asele ngemva kwesihlungi esikrinini azoqhathaniswa, okuyilokho ebesikufuna.
Indlela 3. Ngokusekelwa kokuhlunga kwanoma iyiphi idatha
Njengoba ubona kalula, indlela yesibili nayo ine-drawback: umsebenzi wesamba ungasetshenziswa kuphela ezinombolweni noma izinsuku (okuyizinombolo ku-Excel), kodwa hhayi ukubhala. Okusho ukuthi, uma sifuna ukudweba umugqa phakathi kwamazwe, njengasendleleni yokuqala, kodwa ukuze kuboniswe kahle ngemva kokuhlunga, kuzodingeka sisebenzise indlela eyinkimbinkimbi kakhulu. Khetha lonke ithebula futhi ngaphandle kwesihloko, dala umthetho omusha osuselwe kufomula bese ufaka ukwakhiwa okulandelayo kunkambu yokuqinisekisa:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Enguqulweni yesiNgisi kuzoba:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Ngokuchofoza inkinobho Framework (Ifomethi) setha umngcele ngomugqa obomvu phezulu bese uchofoza OK. Umphumela wokuhlukaniswa ngezwe uzosebenza kahle ngisho nangemva kokuhlunga, isibonelo, ngedethi:
- Gqamisa Izinsuku Nezikhathi Ngokufometha Okunemibandela
- I-Excel isebenza kanjani ngempela namadethi nezikhathi
- Indlela yokusebenzisa ukufometha okunemibandela ukugqamisa amaseli ngesimo ku-Excel