Y 15 fformiwla uchaf yn Excel

Mae Excel yn bendant yn un o'r rhaglenni mwyaf hanfodol. Mae wedi gwneud bywydau llawer o ddefnyddwyr yn haws. Mae Excel yn caniatáu ichi awtomeiddio hyd yn oed y cyfrifiadau mwyaf cymhleth, a dyma brif fantais y rhaglen hon.

Fel rheol, mae defnyddiwr safonol yn defnyddio set gyfyngedig o swyddogaethau yn unig, tra bod yna lawer o fformiwlâu sy'n eich galluogi i weithredu'r un tasgau, ond yn llawer cyflymach.

Gall hyn fod yn ddefnyddiol os oes rhaid i chi gyflawni llawer o gamau gweithredu o'r un math yn gyson sy'n gofyn am nifer fawr o weithrediadau.

Wedi dod yn ddiddorol? Yna croeso i'r adolygiad o'r fformiwlâu 15 Excel mwyaf defnyddiol.

Peth terminoleg

Cyn i chi ddechrau adolygu'r swyddogaethau yn uniongyrchol, mae angen i chi ddeall beth ydyw. Mae'r cysyniad hwn yn golygu fformiwla a osodwyd gan y datblygwyr, yn unol â pha gyfrifiadau a wneir a cheir canlyniad penodol yn yr allbwn. 

Mae dwy brif ran i bob swyddogaeth: enw a dadl. Gall fformiwla gynnwys un swyddogaeth neu sawl swyddogaeth. I ddechrau ei ysgrifennu, mae angen i chi glicio ddwywaith ar y gell ofynnol ac ysgrifennu'r arwydd cyfartal.

Rhan nesaf y ffwythiant yw'r enw. Mewn gwirionedd, dyma enw'r fformiwla, a fydd yn helpu Excel i ddeall yr hyn y mae'r defnyddiwr ei eisiau. Fe'i dilynir gan ddadleuon mewn cromfachau. Mae'r rhain yn baramedrau swyddogaeth sy'n cael eu hystyried i gyflawni rhai gweithrediadau. Mae sawl math o ddadl: rhifol, testun, rhesymegol. Hefyd, yn eu lle, defnyddir cyfeiriadau at gelloedd neu ystod benodol yn aml. Mae pob dadl yn cael ei gwahanu oddi wrth y llall gyda hanner colon.

Cystrawen yw un o'r prif gysyniadau sy'n nodweddu swyddogaeth. Mae'r term hwn yn cyfeirio at dempled ar gyfer mewnosod gwerthoedd penodol er mwyn gwneud i'r swyddogaeth weithio.

Ac yn awr gadewch i ni wirio hyn i gyd yn ymarferol.

Fformiwla 1: VLOOKUP

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl dod o hyd i'r wybodaeth ofynnol yn y tabl, ac arddangos y canlyniad a ddychwelwyd mewn cell benodol. Mae'r talfyriad ar gyfer enw'r ffwythiant yn sefyll am “Vertical View”.

Cystrawen

Mae hon yn fformiwla eithaf cymhleth sydd â 4 dadl, ac mae gan ei defnydd lawer o nodweddion.

Y cystrawen yw:

=VLOOKUP(lookup_value, tabl, column_number, [range_lookup])

Gadewch i ni edrych yn agosach ar yr holl ddadleuon:

1. Y gwerth i edrych i fyny.
2. Bwrdd. Mae'n angenrheidiol bod gwerth chwilio yn y golofn gyntaf, yn ogystal â gwerth a ddychwelir. Mae'r olaf wedi'i leoli yn unrhyw le. Gall y defnyddiwr benderfynu'n annibynnol ble i fewnosod canlyniad y fformiwla. 
3. Rhif colofn.
4. Gwylio egwyl. Os nad yw hyn yn angenrheidiol, yna gallwch hepgor gwerth y ddadl hon. Mae'n fynegiant boolean sy'n nodi pa mor fanwl gywir yw'r cydweddu y dylai'r ffwythiant ddod o hyd iddo. Os nodir y paramedr “Gwir”, yna bydd Excel yn edrych am y gwerth agosaf at yr un a nodir fel y gwerth chwilio. Os nodir y paramedr “Gau”, yna bydd y swyddogaeth yn chwilio am y gwerthoedd hynny sydd yn y golofn gyntaf yn unig.

Yn y llun hwn, rydym yn ceisio darganfod faint o olygfeydd a gynhyrchwyd ar gyfer yr ymholiad “prynu tabled” gan ddefnyddio'r fformiwla.

Fformiwla 2: Os

Mae'r swyddogaeth hon yn angenrheidiol os yw'r defnyddiwr am osod cyflwr penodol y dylid cyfrifo gwerth neu allbwn penodol o dano. Gall gymryd dau opsiwn: gwir a ffug.

Cystrawen

Mae gan y fformiwla ar gyfer y swyddogaeth hon dair prif ddadl, ac mae'n edrych fel hyn:

=IF(mynegiant_rhesymegol, “value_if_true”, “value_if_false”).

Yma, mae mynegiant rhesymegol yn golygu fformiwla sy'n disgrifio'r maen prawf yn uniongyrchol. Gyda'i help, bydd y data'n cael ei wirio i weld a yw'n cydymffurfio ag amod penodol. Yn unol â hynny, mae’r ddadl “gwerth os yw’n ffug” wedi’i bwriadu ar gyfer yr un dasg, a’r unig wahaniaeth yw ei bod yn ddrych gyferbyn o ran ystyr. Mewn geiriau syml, os na chadarnheir y cyflwr, yna mae'r rhaglen yn cyflawni rhai gweithredoedd.

Mae ffordd arall o ddefnyddio'r swyddogaeth IF – swyddogaethau nythu. Gall fod llawer mwy o amodau yma, hyd at 64. Mae enghraifft o ymresymiad sy'n cyfateb i'r fformiwla a roddir yn y sgrinlun fel a ganlyn. Os yw cell A2 yn hafal i ddau, yna mae angen i chi ddangos y gwerth “Ie”. Os oes ganddo werth gwahanol, yna mae angen i chi wirio a yw cell D2 yn hafal i ddau. Os oes, yna mae angen i chi ddychwelyd y gwerth “na”, os yw'r amod yma yn troi allan i fod yn ffug, yna dylai'r fformiwla ddychwelyd y gwerth “efallai”.

Ni argymhellir defnyddio swyddogaethau nythu yn rhy aml, oherwydd eu bod yn eithaf anodd eu defnyddio, mae gwallau yn bosibl. A bydd yn cymryd amser hir i'w trwsio. 

swyddogaeth IF gellir ei ddefnyddio hefyd i benderfynu a yw cell benodol yn wag. Er mwyn cyflawni'r nod hwn, mae angen defnyddio un swyddogaeth arall − ISBLANK.

Dyma'r gystrawen:

=IF(ISBLANK(rhif cell), “Gwag”, “Ddim yn wag”).

Yn ogystal, mae'n bosibl defnyddio yn lle'r swyddogaeth ISBLANK cymhwyso'r fformiwla safonol, ond nodwch gan dybio nad oes unrhyw werthoedd yn y gell.

OS - dyma un o'r swyddogaethau mwyaf cyffredin sy'n hawdd iawn i'w defnyddio ac mae'n caniatáu ichi ddeall pa mor wir yw gwerthoedd penodol, cael canlyniadau ar gyfer meini prawf amrywiol, a hefyd penderfynu a yw cell benodol yn wag.

Mae'r swyddogaeth hon yn sylfaen ar gyfer rhai fformiwlâu eraill. Nawr byddwn yn dadansoddi rhai ohonynt yn fanylach.

Fformiwla 3: SUMIF

swyddogaeth SYMIAU caniatáu i chi grynhoi'r data, yn amodol ar eu cydymffurfiaeth â meini prawf penodol.

Cystrawen

Mae gan y swyddogaeth hon, fel yr un blaenorol, dair dadl. Er mwyn ei ddefnyddio, mae angen i chi ysgrifennu fformiwla o'r fath, gan amnewid y gwerthoedd angenrheidiol yn y mannau priodol.

=SUMIF(ystod, cyflwr, [sum_range])

Gadewch i ni ddeall yn fanylach beth yw pob un o'r dadleuon:

1. Cyflwr. Mae'r ddadl hon yn caniatáu ichi drosglwyddo celloedd i'r swyddogaeth, sy'n destun crynhoi ymhellach.
2. Ystod crynhoi. Mae'r ddadl hon yn ddewisol ac yn caniatáu ichi nodi'r celloedd i grynhoi os yw'r cyflwr yn anwir.

Felly, yn y sefyllfa hon, crynhodd Excel y data ar yr ymholiadau hynny lle mae nifer y trawsnewidiadau yn fwy na 100000.

Fformiwla 4: SUMMESLIMN

Os oes sawl cyflwr, yna defnyddir swyddogaeth gysylltiedig CRYNODEB.

Cystrawen

Mae'r fformiwla ar gyfer y swyddogaeth hon yn edrych fel hyn:

=SUMIFS(cryno_amrediad, cyflwr_ystod1, cyflwr1, [cyflwr_ystod2, cyflwr2], …)

Mae angen yr ail a’r drydedd ddadl, sef “Ystod amod 1” ac “Ystod amod 1”.

Fformiwla 5: COUNTIF a COUNTIFS

Mae'r swyddogaeth hon yn ceisio pennu nifer y celloedd nad ydynt yn wag sy'n cyd-fynd â'r amodau a roddwyd o fewn yr ystod a roddwyd gan y defnyddiwr.

Cystrawen

I fynd i mewn i'r swyddogaeth hon, rhaid i chi nodi'r fformiwla ganlynol:

= COUNTIF (ystod, meini prawf)

Beth yw ystyr y dadleuon a roddwyd?

1. Mae ystod yn set o gelloedd y mae'r cyfrif i'w wneud yn eu plith.
2. Meini prawf – amod a gaiff ei ystyried wrth ddewis celloedd.

Er enghraifft, yn yr enghraifft hon, roedd y rhaglen yn cyfrif nifer yr ymholiadau allweddol, lle mae nifer y cliciau mewn peiriannau chwilio yn fwy na chan mil. O ganlyniad, dychwelodd y fformiwla'r rhif 3, sy'n golygu bod tri gair allweddol o'r fath.

Wrth siarad am swyddogaeth gysylltiedig CYFRIFON, yna mae, yn debyg i'r enghraifft flaenorol, yn darparu'r gallu i ddefnyddio sawl maen prawf ar unwaith. Mae ei fformiwla fel a ganlyn:

=COUNTIFS(condition_range1, condition1, [condition_range2, condition2],…)

Ac yn debyg i'r achos blaenorol, mae “Amrediad Cyflwr 1” ac “Amod 1” yn ddadleuon gofynnol, tra gellir hepgor eraill os nad oes angen o'r fath. Mae'r swyddogaeth uchaf yn darparu'r gallu i gymhwyso hyd at 127 o ystodau ynghyd ag amodau.

Fformiwla 6: IFERROR

Mae'r ffwythiant hwn yn dychwelyd gwerth a bennir gan y defnyddiwr os deuir ar draws gwall wrth werthuso fformiwla. Os yw'r gwerth canlyniadol yn gywir, mae hi'n ei adael.

Cystrawen

Mae dwy ddadl i'r swyddogaeth hon. Mae'r gystrawen fel a ganlyn:

=IFERROR(gwerth;value_if_error)

Disgrifiad o'r dadleuon:

1. Y gwerth yw'r fformiwla ei hun, wedi'i wirio am fygiau.
2. Y gwerth os gwall yw'r canlyniad sy'n ymddangos ar ôl canfod y gwall.

Os byddwn yn siarad am enghreifftiau, yna bydd y fformiwla hon yn dangos y testun "Gwall wrth gyfrifo" os yw rhannu'n amhosibl.

Fformiwla 7: CHWITH

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl dewis y nifer gofynnol o nodau o ochr chwith y llinyn.

Mae ei chystrawen fel a ganlyn:

=LEFT(testun,[num_chars])

Dadleuon posib:

1. Testun – y llinyn yr ydych am gael darn penodol ohono.
2. Mae nifer y nodau yn union nifer y cymeriadau i'w hechdynnu.

Felly, yn yr enghraifft hon, gallwch weld sut mae'r swyddogaeth hon yn cael ei defnyddio i weld sut olwg fydd ar deitlau tudalennau'r wefan. Hynny yw, a fydd y llinyn yn ffitio mewn nifer penodol o nodau ai peidio.

Fformiwla 8: PSTR

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl cael y nifer gofynnol o nodau o'r testun, gan ddechrau gyda nod penodol yn y cyfrif.

Mae ei chystrawen fel a ganlyn:

=MID(testun, start_position, number_of_characters).

Ehangu dadl:

1. Mae testun yn llinyn sy'n cynnwys y data gofynnol.
2. Y man cychwyn yn uniongyrchol yw lleoliad y cymeriad hwnnw, sy'n gweithredu fel y dechrau ar gyfer echdynnu'r testun.
3. Nifer y nodau – nifer y nodau y dylai'r fformiwla eu tynnu o'r testun.

Yn ymarferol, gellir defnyddio'r swyddogaeth hon, er enghraifft, i symleiddio enwau teitlau trwy ddileu'r geiriau sydd ar eu dechrau.

Fformiwla 9: CYNNIG

Mae'r ffwythiant hwn yn priflythrennu pob llythyren sydd mewn llinyn arbennig. Mae ei chystrawen fel a ganlyn:

= ANGEN (testun)

Dim ond un ddadl sydd - y testun ei hun, a fydd yn cael ei phrosesu. Gallwch ddefnyddio cyfeirnod cell.

Fformiwla 10: ISAF

Yn ei hanfod, swyddogaeth wrthdro sy'n gosod llythrennau bach bob llythyren o'r testun neu'r gell a roddwyd.

Mae ei chystrawen yn debyg, dim ond un ddadl sy'n cynnwys y testun neu'r cyfeiriad cell.

Fformiwla 11: CHWILIO

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl dod o hyd i'r elfen ofynnol ymhlith ystod o gelloedd a rhoi ei safle.

Y templed ar gyfer y fformiwla hon yw:

=MATCH(lookup_value, lookup_array, match_type)

Mae angen y ddwy ddadl gyntaf, mae'r un olaf yn ddewisol.

Mae tair ffordd o gydweddu:

1. Llai na neu'n hafal i 1.
2. Yn union – 0.
3. Y gwerth lleiaf, sy'n hafal i -1 neu'n fwy.

Yn yr enghraifft hon, rydym yn ceisio penderfynu pa un o'r geiriau allweddol sy'n cael ei ddilyn gan hyd at 900 o gliciau, yn gynhwysol.

Fformiwla 12: DLSTR

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl pennu hyd llinyn penodol.

Mae ei gystrawen yn debyg i'r un flaenorol:

=DLSTR(testun)

Felly, gellir ei ddefnyddio i bennu hyd disgrifiad yr erthygl wrth hyrwyddo SEO y wefan.

Mae hefyd yn dda ei gyfuno â'r swyddogaeth IF.

Fformiwla 13: CYSYLLTU

Mae'r swyddogaeth hon yn ei gwneud hi'n bosibl gwneud sawl llinell o un. At hynny, caniateir nodi yn y dadleuon gyfeiriadau cell a'r gwerth ei hun. Mae'r fformiwla yn ei gwneud hi'n bosibl ysgrifennu hyd at 255 o elfennau gyda chyfanswm hyd heb fod yn fwy na 8192 nod, sy'n ddigon ar gyfer ymarfer.

Y cystrawen yw:

=CONCATENATE(testun1,testun2,testun3);

Fformiwla 14: PROPNACH

Mae'r swyddogaeth hon yn cyfnewid nodau priflythrennau a llythrennau bach.

Mae'r gystrawen yn syml iawn:

=PROPLAN(testun)

Fformiwla 15: ARGRAFFU

Mae'r fformiwla hon yn ei gwneud hi'n bosibl tynnu pob nod anweledig (er enghraifft, toriad llinell) o'r erthygl.

Mae ei chystrawen fel a ganlyn:

=PRINT(testun)

Fel dadl, gallwch chi nodi cyfeiriad y gell.

Casgliadau

Wrth gwrs, nid yw'r rhain i gyd yn swyddogaethau a ddefnyddir yn Excel. Roeddem am ddod â rhai nad yw'r defnyddiwr taenlen cyffredin wedi clywed amdanynt neu nad ydynt yn eu defnyddio'n aml. Yn ystadegol, y swyddogaethau a ddefnyddir amlaf yw cyfrifo a chanfod gwerth cyfartalog. Ond mae Excel yn fwy na rhaglen taenlen yn unig. Ynddo, gallwch chi awtomeiddio unrhyw swyddogaeth yn llwyr. 

Rwy'n mawr obeithio ei fod wedi gweithio allan, ac fe ddysgoch chi lawer o bethau defnyddiol i chi'ch hun.