Un o'r dulliau rheoli a logisteg allweddol yw dadansoddiad ABC. Gyda hyn, gallwch ddosbarthu adnoddau'r fenter, cynhyrchion, cwsmeriaid, ac ati. yn nhrefn pwysigrwydd. Ar yr un pryd, yn ôl lefel y pwysigrwydd, rhoddir un o dri chategori i bob un o'r unedau uchod: A, B, neu C. Mae gan Excel offer yn ei fagiau sy'n ei gwneud yn haws gwneud y math hwn o ddadansoddiad. Gadewch i ni weld sut i'w defnyddio, a beth yw'r dadansoddiad ABC.
Defnyddio dadansoddiad ABC
Mae dadansoddiad ABC yn fath o amrywiad amodau modern sydd wedi'i wella a'i addasu i egwyddor Pareto. Yn ôl methodoleg ei ymddygiad, rhennir pob elfen o'r dadansoddiad yn dri chategori yn nhrefn pwysigrwydd:
- Categori A - elfennau sy'n gyffredin yn fwy 80% pwysau penodol;
- Categori B - elfennau, y mae eu cyfanswm yn dod 5% hyd at 15% pwysau penodol;
- Categori C - yr elfennau sy'n weddill, sef cyfanswm eu cyfanswm 5% a phwysau llai penodol.
Mae rhai cwmnïau'n defnyddio technegau mwy datblygedig ac yn rhannu'r elfennau heb fod yn 3, ond yn 4 neu 5 grŵp, ond byddwn yn dibynnu ar y cynllun clasurol o ddadansoddi ABC.
Dull 1: dadansoddi trwy ddidoli
Yn Excel, mae dadansoddiad ABC yn cael ei berfformio gan ddefnyddio didoli. Mae'r holl eitemau wedi'u didoli o'r mwyaf i'r lleiaf. Yna cyfrifir pwysau penodol cronnus pob elfen, y mae categori penodol wedi'i neilltuo ar ei sail. Gadewch i ni ddefnyddio enghraifft benodol i ddarganfod sut mae'r dechneg hon yn cael ei chymhwyso'n ymarferol.
Mae gennym dabl gyda rhestr o nwyddau y mae'r cwmni yn eu gwerthu, a'r swm cyfatebol o refeniw o'u gwerthiant am gyfnod penodol o amser. Ar waelod y tabl, caiff cyfanswm y refeniw ei grynhoi ar gyfer pob eitem o nwyddau. Y dasg yw defnyddio dadansoddiad ABC i rannu'r cynhyrchion hyn yn grwpiau yn ôl eu pwysigrwydd i'r cwmni.
- Dewiswch y tabl gyda'r cyrchwr data, gan ddal botwm chwith y llygoden, ac eithrio'r pennawd a'r rhes derfynol. Ewch i'r tab "Data". Cliciwch ar y botwm. "Trefnu"wedi'i leoli mewn bloc o offer Msgstr "Didoli a hidlo" ar y tâp.
Gallwch hefyd wneud yn wahanol. Dewiswch yr ystod uchod o'r tabl, yna symudwch i'r tab "Cartref" a chliciwch ar y botwm Msgstr "Didoli a hidlo"wedi'i leoli mewn bloc o offer Golygu ar y tâp. Mae rhestr yn cael ei gweithredu lle rydym yn dewis sefyllfa ynddi. "Didoli Custom".
- Wrth gymhwyso unrhyw un o'r camau uchod, lansir y ffenestr gosodiadau. Edrychwn ar y paramedr Msgstr "Mae fy data yn cynnwys penawdau" mae tic wedi'i osod. Yn achos ei absenoldeb, gosodwch.
Yn y maes "Colofn" nodi enw'r golofn lle mae'r data ar refeniw.
Yn y maes "Trefnu" mae angen i chi nodi yn ôl pa feini prawf penodol fydd yn cael eu datrys. Rydym yn gadael y gosodiadau rhagosodedig - "Gwerthoedd".
Yn y maes "Gorchymyn" gosod y sefyllfa "Disgynnol".
Ar ôl gwneud y gosodiadau hyn cliciwch ar y botwm "OK" ar waelod y ffenestr.
- Ar ôl cyflawni'r weithred hon, cafodd yr holl eitemau eu didoli yn ôl refeniw o'r uchaf i'r isaf.
- Nawr mae angen i ni gyfrifo cyfran pob un o'r elfennau ar gyfer y cyfanswm. Rydym yn creu colofn ychwanegol at y dibenion hyn, y byddwn yn ei galw "Rhannu". Yng nghell gyntaf y golofn hon rhowch arwydd "="ac wedi hynny rydym yn nodi'r cyfeiriad at y gell lle mae swm yr elw o werthu'r cynnyrch perthnasol wedi'i leoli. Nesaf, gosodwch yr arwydd rhannu ("/"). Ar ôl hynny, rydym yn nodi cyfesurynnau'r gell, sy'n cynnwys cyfanswm gwerthiant nwyddau ledled y fenter.
O ystyried y ffaith y byddwn yn copïo'r fformiwla a nodwyd i gelloedd eraill y golofn "Rhannu" defnyddio marcydd llenwi, cyfeiriad y ddolen i'r elfen sy'n cynnwys cyfanswm y refeniw ar gyfer y fenter, mae angen i ni ei drwsio. I wneud hyn, gwnewch y ddolen absoliwt. Dewiswch gyfesurynnau'r gell benodedig yn y fformiwla a phwyswch yr allwedd F4. Fel y gwelwn, ymddangosodd arwydd doler o flaen y cyfesurynnau, sy'n dangos bod y ddolen wedi dod yn absoliwt. Dylid nodi mai'r cyfeiriad at swm refeniw'r eitem gyntaf yn y rhestr (Eitem 3rhaid iddo aros yn gymharol.
Yna, i wneud cyfrifiadau, cliciwch ar y botwm. Rhowch i mewn.
- Fel y gwelwch, dangosir cyfran y refeniw o'r cynnyrch cyntaf a restrir yn y rhestr yn y gell darged. I wneud copi o'r fformiwla yn yr ystod isod, rhowch y cyrchwr yng nghornel dde isaf y gell. Caiff ei drawsnewid yn farciwr llenwi sy'n edrych fel croes fach. Cliciwch ar fotwm chwith y llygoden a llusgwch y ddolen lenwi i ddiwedd y golofn.
- Fel y gwelwch, mae'r golofn gyfan wedi'i llenwi â data sy'n disgrifio'r gyfran o refeniw o werthu pob cynnyrch. Ond mae gwerth y pwysau penodol yn cael ei arddangos mewn fformat rhifol, ac mae angen i ni ei drawsnewid yn ganran. I wneud hyn, dewiswch gynnwys y golofn "Rhannu". Yna symudwch i'r tab "Cartref". Ar y rhuban yn y grŵp gosodiadau "Rhif" Mae maes yn dangos fformat y data. Yn ddiofyn, os na wnaethoch chi unrhyw driniaethau ychwanegol, dylid gosod y fformat yno. "Cyffredinol". Rydym yn clicio ar yr eicon ar ffurf triongl sydd wedi'i leoli ar ochr dde'r cae hwn. Yn y rhestr o fformatau sy'n agor, dewiswch y sefyllfa "Diddordeb".
- Fel y gwelwch, cafodd yr holl werthoedd colofnau eu trosi'n ganrannau. Fel y dylai fod, yn y llinell "Cyfanswm" nodwyd 100%. Y gyfran o nwyddau y disgwylir iddynt gael eu lleoli yn y golofn o fwy i lai.
- Nawr dylem greu colofn lle byddai'r gyfran gronnus gyda chyfanswm cronnus yn cael ei harddangos. Hynny yw, ym mhob rhes, caiff pwysau penodol yr holl nwyddau hynny sydd wedi'u lleoli yn y rhestr uchod eu hychwanegu at bwysau penodol unigol cynnyrch penodol. Ar gyfer yr eitem gyntaf yn y rhestr (Eitem 3) bydd y pwysau penodol unigol a'r gyfran gronedig yn gyfartal, ond ar gyfer yr holl rai dilynol, bydd yn rhaid ychwanegu cyfran gronedig yr eitem flaenorol yn y rhestr at y dangosydd unigol.
Felly, yn y rhes gyntaf rydym yn trosglwyddo i'r golofn "Rhannu Cronnus" cyfradd colofn "Rhannu".
- Nesaf, gosodwch y cyrchwr yn yr ail gell golofn. "Rhannu Cronnus". Yma mae'n rhaid i ni gymhwyso'r fformiwla. Rhoesom arwydd yn hafal a phlygu cynnwys y gell "Rhannu" cynnwys yr un rhes a chell "Rhannu Cronnus" o'r llinell uchod. Mae pob cysylltiad yn gymharol, hynny yw, nid ydym yn gwneud unrhyw driniaethau gyda nhw. Wedi hynny, cliciwch ar y botwm. Rhowch i mewn i arddangos y canlyniad terfynol.
- Nawr mae angen i chi gopïo'r fformiwla hon i gelloedd y golofn hon, sydd wedi'u lleoli isod. I wneud hyn, defnyddiwch y marciwr llenwi, yr ydym eisoes wedi troi ato i gopïo'r fformiwla yn y golofn "Rhannu". Ar yr un pryd, y llinyn "Cyfanswm" nid oes angen cipio oherwydd bod y canlyniad cronedig yn 100% yn cael ei arddangos ar yr eitem olaf o'r rhestr. Fel y gwelwch, llenwyd holl elfennau ein colofn ar ôl hynny.
- Wedi hynny rydym yn creu colofn "Grŵp". Bydd angen i ni grwpio cynhyrchion yn gategorïau A, B a C yn ôl y gyfran gronnus a nodir. Fel y cofiwn, rhennir pob elfen yn grwpiau yn ôl y cynllun canlynol:
- A - tan 80%;
- B - y canlynol 15%;
- Gyda - yn weddill 5%.
Felly, yr holl nwyddau, y mae cyfran gronnus y pwysau penodol ohonynt yn mynd i mewn i'r ffin i 80%neilltuo categori A. Nwyddau sydd â phwysau penodol cronedig o 80% hyd at 95% neilltuo categori B. Y grŵp cynnyrch sy'n weddill gyda gwerth mwy 95% mae pwysau penodol cronedig yn pennu categori C.
- Er eglurder, gallwch lenwi'r grwpiau hyn mewn gwahanol liwiau. Ond mae hyn yn ddewisol.
Felly, rydym wedi rhannu'r elfennau yn grwpiau yn ôl lefel pwysigrwydd, gan ddefnyddio'r dadansoddiad ABC. Wrth ddefnyddio rhai dulliau eraill, fel y crybwyllwyd uchod, defnyddiwch raniad yn fwy o grwpiau, ond erys yr egwyddor o rannu yn ddigyfnewid bron.
Gwers: Didoli a hidlo yn Excel
Dull 2: defnyddio fformiwla gymhleth
Wrth gwrs, defnyddio didoli yw'r ffordd fwyaf cyffredin o gynnal dadansoddiad ABC yn Excel. Ond mewn rhai achosion mae'n ofynnol iddo gynnal y dadansoddiad hwn heb aildrefnu'r rhesi yn y tabl ffynhonnell. Yn yr achos hwn, bydd fformiwla gymhleth yn cael ei hachub. Er enghraifft, byddwn yn defnyddio'r un tabl ffynhonnell ag yn yr achos cyntaf.
- Ychwanegwch at y tabl gwreiddiol sy'n cynnwys enw'r nwyddau a'r elw o werthu pob un ohonynt, y golofn "Grŵp". Fel y gwelwch, yn yr achos hwn ni allwn ychwanegu colofnau gyda chyfrifiad cyfrannau unigol a chronnus.
- Dewiswch y gell gyntaf yn y golofn. "Grŵp"yna cliciwch ar y botwm. "Mewnosod swyddogaeth"wedi'i leoli ger y bar fformiwla.
- Gweithredir Meistri swyddogaeth. Symud i gategori "Cysylltiadau ac araeau". Dewiswch swyddogaeth "SELECT". Cliciwch ar y botwm. "OK".
- Mae'r ffenestr dadl swyddogaeth yn cael ei gweithredu. DEWIS. Mae ei chystrawen fel a ganlyn:
= SELECT (Index_number; Value1; Value2; ...)Amcan y swyddogaeth hon yw cynhyrchu un o'r gwerthoedd penodedig, yn dibynnu ar y rhif mynegai. Gall nifer y gwerthoedd gyrraedd 254, ond dim ond tri enw sydd eu hangen arnom sy'n cyfateb i'r categorïau o ddadansoddiad ABC: A, B, Gyda. Gallwn fynd i mewn i'r cae ar unwaith "Value1" y symbol "A"yn y maes "Value2" - "B"yn y maes "Value3" - "C".
- Ond gyda dadl "Rhif mynegai" Bydd yn rhaid i chi glymu'n drylwyr, gan gynnwys rhai gweithredwyr ychwanegol ynddo. Gosodwch y cyrchwr yn y maes "Rhif mynegai". Nesaf, cliciwch ar yr eicon sydd â ffurf triongl i'r chwith o'r botwm "Mewnosod swyddogaeth". Mae rhestr o weithredwyr a ddefnyddiwyd yn ddiweddar yn agor. Mae angen swyddogaeth arnom MATCH. Gan nad yw ar y rhestr, cliciwch ar y pennawd "Nodweddion eraill ...".
- Yn rhedeg y ffenestr eto. Meistri swyddogaeth. Unwaith eto ewch i'r categori "Cysylltiadau ac araeau". Mae yna sefyllfa yno "MATCH"dewiswch ef a chliciwch ar y botwm "OK".
- Agor ffenestr dadl y gweithredwr MATCH. Mae ei chystrawen fel a ganlyn:
= MATCH (Gwerth wedi'i chwilio; arae a welwyd; Match_type)Pwrpas y swyddogaeth hon yw pennu rhif safle'r elfen benodol. Hynny yw, dim ond yr hyn sydd ei angen arnom ar gyfer y maes "Rhif mynegai" swyddogaethau DEWIS.
Yn y maes "Array gwylio" Gallwch osod y mynegiad canlynol ar unwaith:
{0:0,8:0,95}Dylai fod yn union mewn braces cyrliog, fel fformiwla arae. Nid yw'n anodd dyfalu bod y rhifau hyn (0; 0,8; 0,95) dynodi ffiniau'r gyfran gronedig rhwng grwpiau.
Maes "Math Mapio" ddim yn orfodol ac yn yr achos hwn ni fyddwn yn ei lenwi.
Yn y maes "Gwerth a geisir" gosod y cyrchwr. Yna, unwaith eto, drwy'r eicon a ddisgrifir uchod ar ffurf triongl, symudwn i Dewin Swyddogaeth.
- Y tro hwn i mewn Dewin swyddogaeth symud i gategori "Mathemategol". Dewiswch enw "SUMMESLI" a chliciwch ar y botwm "OK".
- Mae'r ffenestr dadl swyddogaeth yn dechrau. Symiau. Mae'r gweithredwr penodedig yn crynhoi'r celloedd sy'n bodloni'r amod penodedig. Ei chystrawen yw:
= CRYNODEB (amrediad; maen prawf; range_summing)Yn y maes "Ystod" nodwch gyfeiriad y golofn "Refeniw". At y dibenion hyn, rydym yn gosod y cyrchwr yn y maes, ac yna, ar ôl clampio botwm chwith y llygoden, dewiswch holl gelloedd y golofn gyfatebol, ac eithrio'r gwerth "Cyfanswm". Fel y gwelwch, mae'r cyfeiriad yn cael ei arddangos ar unwaith yn y maes. Yn ogystal, mae angen i ni wneud y ddolen hon yn absoliwt. I wneud hyn, gwnewch ei ddewis a phwyswch yr allwedd F4. Amlygir y cyfeiriad gydag arwyddion doler.
Yn y maes "Maen Prawf" mae angen i ni osod cyflwr. Rhowch y mynegiad canlynol:
">"&Yna, yn syth ar ei ôl, byddwn yn rhoi cyfeiriad cell gyntaf y golofn. "Refeniw". Rydym yn gwneud y cyfesurynnau llorweddol yn y cyfeiriad hwn yn absoliwt, gan ychwanegu arwydd doler o'r bysellfwrdd o flaen y llythyr. Mae'r cyfesurynnau fertigol yn gymharol, hynny yw, ni ddylai fod arwydd o flaen y rhif.
Wedi hynny, peidiwch â phwyso'r botwm "OK", a chliciwch ar enw'r swyddogaeth MATCH yn y bar fformiwla.
- Yna byddwn yn dychwelyd i'r ffenestr dadl swyddogaeth. MATCH. Fel y gwelwch, yn y maes "Gwerth a geisir" data a roddwyd gan y gweithredwr Symiau. Ond nid dyna'r cyfan. Ewch i'r maes hwn ac ychwanegwch yr arwydd at y data presennol. "+" heb ddyfynbrisiau. Yna byddwn yn rhoi cyfeiriad cell gyntaf y golofn. "Refeniw". Ac eto rydym yn gwneud cyfesurynnau llorweddol y ddolen hon yn absoliwt, ac yn fertigol rydym yn gadael perthynas.
Nesaf, cymerwch holl gynnwys y cae "Gwerth a geisir" mewn cromfachau, yna rhowch yr arwydd rhaniad ("/"). Wedi hynny, unwaith eto drwy'r eicon triongl, ewch i'r ffenestr dewis swyddogaeth.
- Fel y tro diwethaf i redeg Dewin swyddogaeth chwilio am y gweithredwr a ddymunir yn y categori "Mathemategol". Y tro hwn, gelwir y swyddogaeth a ddymunir "SUMM". Dewiswch ef a chliciwch ar y botwm. "OK".
- Agor ffenestr dadl y gweithredwr SUM. Ei brif bwrpas yw crynhoi data mewn celloedd. Mae cystrawen y datganiad hwn yn eithaf syml:
= SUM (Number1; Number2; ...)At ein dibenion, dim ond maes sydd ei angen arnom. "Number1". Rhowch gyfesurynnau'r amrediad colofnau "Refeniw", ac eithrio'r gell sy'n cynnwys y cyfansymiau. Rydym eisoes wedi perfformio llawdriniaeth debyg yn y maes. "Ystod" swyddogaethau Symiau. Ar yr adeg honno, rydym yn gwneud cyfesurynnau absoliwt o'r ystod trwy eu dewis, a gwasgu'r allwedd F4.
Wedi hynny cliciwch ar yr allwedd "OK" ar waelod y ffenestr.
- Fel y gwelwch, cynhyrchodd cymhlethdod y swyddogaethau a gofnodwyd gyfrifiad a rhoddodd y canlyniad yng nghell gyntaf y golofn "Grŵp". Neilltuwyd grŵp i'r eitem gyntaf. "A". Mae'r fformiwla lawn a ddefnyddiwyd gennym ar gyfer y cyfrifiad hwn fel a ganlyn:
= SELECT (MATCH ((B $ 2 $: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")Ond, wrth gwrs, ym mhob achos, bydd y cyfesurynnau yn y fformiwla hon yn wahanol. Felly, ni ellir ei ystyried yn gyffredinol. Ond, gan ddefnyddio'r llawlyfr a roddwyd uchod, gallwch fewnosod cyfesurynnau unrhyw dabl a chymhwyso'r dull hwn yn llwyddiannus mewn unrhyw sefyllfa.
- Fodd bynnag, nid dyma'r cyfan. Dim ond ar gyfer rhes gyntaf y tabl y gwnaethom gyfrifo. Llenwi'r golofn ddata yn llwyr "Grŵp", mae angen i chi gopïo'r fformiwla hon i'r ystod isod (ac eithrio'r gell res "Cyfanswm") defnyddio'r marciwr llenwi, fel y gwnaethom fwy nag unwaith. Ar ôl cofnodi'r data, gellir ystyried bod y dadansoddiad ABC yn gyflawn.
Fel y gwelwch, nid yw'r canlyniadau a gafwyd gan ddefnyddio'r amrywiad wrth ddefnyddio fformiwla gymhleth yn wahanol i'r canlyniadau a gyflawnwyd gennym drwy ddidoli. Mae pob un o'r cynhyrchion yn cael eu neilltuo i'r un categorïau, ond nid oedd y llinellau'n newid eu sefyllfa gychwynnol.
Gwers: Dewin swyddogaeth Excel
Gall Excel hwyluso dadansoddiad ABC yn fawr i ddefnyddiwr. Cyflawnir hyn gan ddefnyddio offeryn fel didoli. Ar ôl hynny, cyfrifir y pwysau penodol unigol, y gyfran gronedig ac, mewn gwirionedd, y rhaniad yn grwpiau. Mewn achosion lle na chaniateir newid yn lleoliad cychwynnol y rhesi yn y tabl, gallwch ddefnyddio'r dull gan ddefnyddio fformiwla gymhleth.
