Unu el la ĉefaj metodoj de administrado kaj loĝistiko estas la analizo de ABC. Per ĝi, vi povas klasifiki la rimedojn de la entrepreno, produktoj, klientoj, ktp. laŭ ordo de graveco. Samtempe, laŭ la nivelo de graveco, ĉiu el la supre menciitaj unuoj ricevas unu el tri kategorioj: A, B aŭ C. Excel havas ilojn en sia pakaĵo, kiuj faciligas la realigon de ĉi tiu speco de analizo. Ni vidu kiel uzi ilin, kaj kio estas la analizo de ABC.
Uzante analizon ABC
Analizo ABC estas speco de plibonigita kaj adaptita al modernaj kondiĉoj varianto de la principo Pareto. Laŭ la metodaro de ĝia konduto, ĉiuj elementoj de la analizo estas dividitaj en tri kategoriojn laŭ ordo de graveco:
- Kategorio A - elementoj pli komunaj 80% specifa pezo;
- Kategorio B - elementoj, el kiuj la tuto estas 5% ĝis 15% specifa pezo;
- Kategorio C - la ceteraj elementoj, kies tuta sumo estas 5% kaj malpli specifa pezo.
Iuj kompanioj uzas pli altnivelajn teknikojn kaj dividas la elementojn ne en 3, sed en 4 aŭ 5 grupojn, sed ni dependas de la klasika skemo de ABC-analizo.
Metodo 1: analizo per ordigado
En Excel, analizo ABC realigas uzante ordigado. Ĉiuj aĵoj estas ordigitaj de plej granda al plej malgranda. Tiam la tuteca specifa pezo de ĉiu ero estas kalkulita, sur kiu baziĝas certa kategorio. Ni uzu specifan ekzemplon por ekscii kiel ĉi tiu tekniko estas praktike aplikita.
Ni havas tablon kun listo de varoj kiujn la kompanio vendas, kaj la responda kvanto de enspezoj de ilia vendo por iu tempodaŭro. Ĉe la fino de la tabelo, la tutaj enspezoj resumas por ĉiuj varoj. La tasko estas uzi ABC-analizon por dividi ĉi tiujn produktojn en grupojn laŭ ilia graveco por la kompanio.
- Elektu la tablon kun la datum-kursoro, tenante la maldekstran musbutonon, ekskluzive de la titolo kaj la fina vico. Iru al la langeto "Datumoj". Alklaku la butonon. "Ordigi"lokita en bloko de iloj "Ordigi kaj filtri" sur la bendo.
Vi povas ankaŭ fari malsame. Elektu la supran gamon de la tabelo, tiam movu vin al la langeto "Hejmo" kaj alklaku la butonon "Ordigi kaj filtri"lokita en bloko de iloj Redaktado sur la bendo. Listo estas aktivigita en kiu ni elektas pozicion en ĝi. "Laŭmenda Varo".
- Kiam vi aplikas iun el la supraj agoj, la ordiga agorda fenestro estas lanĉita. Ni rigardas la parametron "Miaj datumoj enhavas titolojn" tick estis difinita. En kazo de foresto, instalu.
En la kampo "Kolumno" specifi la nomon de la kolumno en kiu la datumoj pri enspezoj.
En la kampo "Ordigi" vi devas specifi per kiuj specifaj kriterioj estos ordigita. Ni lasas la antaŭdifinitajn agordojn - "Valoroj".
En la kampo "Ordo" starigu la pozicion "Descendente".
Post fari ĉi tiujn agordojn alklaku la butonon "Bone" ĉe la fino de la fenestro.
- Post plenumado de ĉi tiu ago, ĉiuj aĵoj estis ordigitaj laŭ enspezo de la plej alta al la plej malalta.
- Nun ni bezonas kalkuli la proporcion de ĉiu el la elementoj por la totalo. Ni kreas por ĉi tiuj celoj plian kolumnon, kiun ni vokos "Dividi". En la unua ĉelo de ĉi tiu kolumno metis signon "="post kio ni indikas la referencon al la ĉelo en kiu la sumo de la produkto de la vendo de la koncerna produkto troviĝas. Poste starigu la dividan signon ("/"). Poste ni indikas la koordinatojn de la ĉelo, kiu enhavas la tutan sumon de vendo de varoj tra la tuta entrepreno.
Konsiderante la fakton, ke ni kopios la indikitan formulon al aliaj ĉeloj de la kolumno "Dividi" per pleniga markilo, ni bezonas ripari la adreson de la ligilo al la elemento kun la tuta sumo de enspezo por la entrepreno. Por fari tion, faru la ligon absoluta. Elektu la koordinatojn de la specifita ĉelo en la formulo kaj premu la klavon F4. Kiel ni povas vidi, dolara signo aperis antaŭ la koordinatoj, kio indikas, ke la ligo fariĝis absoluta. Oni notu, ke la referenco al la kvanto de enspezo de la unua ero en la listo (Item 3) devas resti relativa.
Tiam, por fari kalkulojn, alklaku la butonon. Eniru.
- Kiel vi povas vidi, la proporcio de enspezoj de la unua produkto listita en la listo montras en la cela ĉelo. Por fari kopion de la formulo en la suba rango, metu la kursoron en la maldekstran angulon de la ĉelo. I transformiĝas al pleniga markilo simila al malgranda kruco. Alklaku la maldekstran musbutonon kaj trenu la plenigaĵon sub la fino de la kolumno.
- Kiel vi povas vidi, la tuta kolumno estas plena de datumoj priskribantaj la parton de enspezo de la vendo de ĉiu produkto. Sed la valoro de la specifa pezo estas montrita en nombra formato, kaj ni devas transformi ĝin en procenton. Por fari tion, elektu la enhavon de la kolumno "Dividi". Poste moviĝu al la langeto "Hejmo". Sur la rubando en la agorda grupo "Nombro" Estas kampo kun la datuma formato. Defaŭlte, se vi ne plenumis pliajn manipuladojn, la formato devas esti difinita tie. "Enerala". Ni alklakas la ikonon en la formo de triangulo lokita dekstre de ĉi tiu kampo. En la listo de malfermitaj formatoj, elektu la pozicion "Intereso".
- Kiel vi povas vidi, ĉiuj kolumnaj valoroj estis konvertitaj al procentoj. Kiel devus esti, en la linio "Tuta" indikis 100%. La proporcio de varoj atendataj troviĝi en la kolumno de pli granda al pli malgranda.
- Nun ni devus krei kolumnon en kiu la tuteca akcio kun tuteca sumo estus montrita. Tio estas, en ĉiu vico, la specifa pezo de ĉiuj tiuj varoj, kiuj troviĝas en la supra listo, aldoniĝos al la individua specifa pezo de specifa produkto. Por la unua ero en la listo (Item 3) la individua specifa pezo kaj la akumulita akcio estos egalaj, sed por ĉiuj postaj, la akumulita parto de la antaŭa ero en la listo devas esti aldonita al la individua indikilo.
Do, en la unua vico ni transloĝiĝas al la kolumno "Akumulita Interŝanĝo" kolumna imposto "Dividi".
- Poste starigu la kursoron en la ĉelo de la dua kolumno. "Akumulita Interŝanĝo". Jen ni devas apliki la formulon. Ni metis signon egalas kaj faldi la enhavon de la ĉelo "Dividi" sama vico kaj ĉela enhavo "Akumulita Interŝanĝo" de la supra linio. Ĉiuj ligoj estas relativaj, te ni plenumas kun ili manipulojn. Post tio, alklaku la butonon. Eniru por montri la finan rezulton.
- Nun vi bezonas kopii ĉi tiun formulon al la ĉeloj de ĉi tiu kolumno, kiuj troviĝas sube. Por fari tion, uzu la plenigan markilon, al kiu ni jam uzis kopii la formulon en la kolumno "Dividi". Samtempe la ĉeno "Tuta" kapto ne estas necesa ĉar la akumulita rezulto en 100% estos montrata sur la lasta ero de la listo. Kiel vi povas vidi, ĉiuj elementoj de nia kolumno post tio plenigxis.
- Post tio ni kreas kolumnon "Grupo". Ni bezonos kolekti produktojn laŭ kategorioj A, B kaj C laŭ la tuteca akcio indikita. Kiel ni memoras, ĉiuj elementoj estas dividitaj en grupojn laŭ la sekva skemo:
- A - ĝis 80%;
- B - la sekva 15%;
- Kun - restas 5%.
Tiel, ĉiuj varoj, la tuteca akcio de la specifa pezo de kiu eniras la limon al 80%atribui kategorion A. Varoj kun specifa pezo de amasigita 80% ĝis 95% atribui kategorion B. La cetera produktokrupo kun pli valoro 95% akirita specifa pezo asigni kategorion C.
- Por klareco, vi povas plenigi tiujn grupojn laŭ malsamaj koloroj. Sed ĉi tio estas nedeviga.
Tiel, ni detruis la elementojn en grupojn laŭ la nivelo de graveco, uzante la analizon ABC. Kiam oni uzas aliajn metodojn, kiel menciite supre, apliku subdiskon en pli da grupoj, sed la principo de dispartigo restas preskaŭ senŝanĝa.
Leciono: Ordigo kaj filtrado en Excel
Metodo 2: uzante kompleksan formulon
Kompreneble, la uzo de ordigado estas la plej ofta maniero efektivigi analizon ABC en Excel. Sed en iuj kazoj necesas efektivigi ĉi tiun analizon sen rearangi la vicojn en la fontta tablo. En ĉi tiu kazo, kompleksa formulo savos. Ekzemple, ni uzos la saman fontan tabelon kiel en la unua kazo.
- Aldonu al la originala tabelo enhavanta la nomon de la varoj kaj la produktojn de la vendo de ĉiu el ili, la kolumno "Grupo". Kiel vi povas vidi, en ĉi tiu kazo ni ne povas aldoni kolumnojn kun la kalkulo de individuaj kaj tutmondaj akcioj.
- Elektu la unuan ĉelon en la kolumno. "Grupo"tiam alklaku la butonon. "Enmetu funkcion"situanta proksime al la formala stango.
- Aktivigo estas farita Funkcio majstroj. Movu al kategorio "Ligoj kaj tabeloj". Elektu funkcion "ELEKTU". Alklaku la butonon. "Bone".
- La funkcia argumenta fenestro estas aktivigita. SELECTION. Ia sintakso estas jene:
= ELEKTU (Nombro de Indekso; Valoro1; Valoro2; ...)La celo de ĉi tiu funkcio estas eligi unu el la specifitaj valoroj, depende de la indekso-nombro. La nombro de valoroj povas atingi 254, sed ni bezonas nur tri nomojn, kiuj respondas al la kategorioj de analizo ABC: A, B, Kun. Ni povas tuj eniri sur la kampon "Valoro1" la simbolo "A"en la kampo "Valoro2" - "B"en la kampo "Valoro3" - "C".
- Sed kun argumento "Indekso numero" Estos necese ĵetiĝi plene, enmetante en ĝi kelkajn aldonajn operatorojn. Metu la kursoron sur la kampon "Indekso numero". Poste klaku sur la ikono kun la formo de triangulo maldekstre de la butono "Enmetu funkcion". Listo de ĵus uzitaj operatoroj malfermiĝas. Ni bezonas funkcion DIVIDUO. Ĉar ĝi ne estas en la listo, alklaku la apudskribon "Aliaj ecoj ...".
- Ruliĝas la fenestro denove. Funkcio majstroj. Denove iru al la kategorio "Ligoj kaj tabeloj". Ni trovas tie pozicion "MATCH"elektu ĝin kaj alklaku la butonon "Bone".
- Operatora argumenta fenestro malfermiĝas DIVIDUO. Ia sintakso estas jene:
= MATCH (Serĉita valoro; Serĉita tabelo; matrico-tipo)La celo de ĉi tiu funkcio estas determini la pozician nombron de la specifa elemento. Tio estas, ĝuste kion ni bezonas por la kampo "Indekso numero" funkcioj SELECTION.
En la kampo "Vida tabelo" Vi povas tuj agordi la jenan esprimon:
{0:0,8:0,95}I devus esti precize en krispaj krampoj, kiel tabela formulo. Ne estas malfacile diveni, ke ĉi tiuj nombroj (0; 0,8; 0,95) indikas la limojn de la akumulita interŝanĝo inter grupoj.
Kampo "Mapping Type" ne deviga kaj en ĉi tiu kazo ni ne plenigos ĝin.
En la kampo "Serĉita valoro" starigu la kursoron. Denove, tra la supre priskribita ikono en la formo de triangulo, ni moviĝas al Funkciaj Sorĉisto.
- Ĉi-foje en Funkcia sorĉisto moviĝu al kategorio "Matematika". Elektu nomon "SUMMESLI" kaj alklaku la butonon "Bone".
- La funkcia argumento fenestro komenciĝas. Someroj. La specifita operatoro sumigas la ĉelojn, kiuj plenumas la specifitan kondiĉon. Lia sintakso estas:
= SUMMES (rango; kriterio; range_summing)En la kampo "Montaro" enigi la adreson de la kolumno "Enspezo". Por ĉi tiuj celoj, ni fiksas la kursoron sur la kampon, kaj tiam, kunpreminte la maldekstran musbutonon, elektu ĉiujn ĉelojn de la responda kolumno, ekskludante la valoron. "Tuta". Kiel vi povas vidi, la adreso tuj aperas en la kampo. Krome ni devas fari ĉi tiun ligon absoluta. Por fari tion, elektu ĝin kaj premu la klavon F4. La adreso estas markita per dolaraj signoj.
En la kampo "Kriterio" ni bezonas kondiĉon. Enigu la jenan esprimon:
">"&Tiam tuj post ĝi ni enigas la adreson de la unua ĉelo de la kolumno. "Enspezo". Ni faras la horizontalajn koordinatojn en ĉi tiu adreso absoluta, aldonante dolaran signon de la klavaro antaŭ la letero. La vertikalaj koordinatoj estas relativaj, te estu neniu signo antaŭ la nombro.
Post tio, ne premu la butonon "Bone", kaj alklaku la nomon de la funkcio DIVIDUO en la formala stango.
- Tiam ni revenos al la funkcia argumento fenestro. DIVIDUO. Kiel vi povas vidi, sur la kampo "Serĉita valoro" datumoj aperis donitaj de la operatoro Someroj. Sed tio ne estas ĉio. Iru al ĉi tiu kampo kaj aldonu la signon al la ekzistantaj datumoj. "+" sen citaĵoj. Tiam ni enigas la adreson de la unua ĉelo de la kolumno. "Enspezo". Kaj denove ni faras la horizontalajn koordinatojn de ĉi tiu ligo absolutaj, kaj vertikale ni lasas parencon.
Poste prenu la tutan enhavon de la kampo "Serĉita valoro" en krampoj, tiam metu la dividan signon ("/"). Post tio, denove tra la triangula ikono, iru al la fenestro selektado de funkcioj.
- Kiel lasta tempo en kurado Funkcia sorĉisto serĉas la deziratan operatoron en la kategorio "Matematika". Ĉi-foje, la dezirata funkcio nomiĝas "SUMM". Elektu ĝin kaj alklaku la butonon. "Bone".
- Operatora argumenta fenestro malfermiĝas SUMO. Ia ĉefa celo estas la sumado de datumoj en ĉeloj. La sintakso de ĉi tiu aserto estas sufiĉe simpla:
= SUMO (Nombro1; Nombro2; ...)Por niaj celoj ni nur bezonas kampon. "Numero1". Enigu la koordinatojn de la kolumna intervalo "Enspezo", ekskludante la ĉelon kiu enhavas la totalojn. Ni jam faris similan operacion sur la kampo. "Montaro" funkcioj Someroj. Kiel en tiu tempo, ni faras absolutajn koordinatojn de la teritorio elektante ilin kaj premante la klavon F4.
Post tio alklaku la klavon "Bone" ĉe la fino de la fenestro.
- Kiel vi povas vidi, la komplekso de la eniritaj funkcioj produktis kalkulon kaj donis la rezulton en la unua ĉelo de la kolumno "Grupo". La unua ero ricevis grupon. "A". La plena formulo ni uzis por ĉi tiu kalkulo estas jene:
= ELEKTU (KUNKTO (SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUMO ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")Sed, kompreneble, en ĉiu kazo, la koordinatoj en ĉi tiu formulo estos malsamaj. Do ĝi ne povas esti konsiderata universala. Sed, uzante la manlibron donitan supre, vi povas enmeti la koordinatojn de iu tabelo kaj sukcese apliki ĉi tiun metodon en ajna situacio.
- Tamen ĉi tio ne estas ĉio. Ni kalkulis nur por la unua vico de la tabelo. Plene plenigi la datuman kolumnon "Grupo", vi devas kopii ĉi tiun formulon al la suba rango (ekskluzive de la vica ĉelo "Tuta") uzante la pleniga markilo, kiel ni faris pli ol unufoje. Post la enigo de la datumoj, la analizo de ABC povas esti konsiderata kompleta.
Kiel vi povas vidi, la rezultoj akiritaj uzante la varianton kun la uzo de kompleksa formulo tute ne diferencas de la rezultoj, kiujn ni faris per ordigado. Ĉiuj produktoj havas la samajn kategoriojn, sed la linioj ne ŝanĝis sian komencan pozicion.
Leciono: Magia funkcio Excel
Excel povas ege faciligi analizon ABC por uzanto. Ĉi tio atingiĝas per ilo kiel ordigado. Post tio oni kalkulas la individuan specifan pezon, la akumulitan kotizon kaj fakte la divido en grupoj. En kazoj kie la ŝanĝo en la komenca pozicio de la vicoj en la tabelo ne rajtas, vi povas apliki la metodon per kompleksa formulo.
