Uhasibu wa maisha wa Excel kwa wale wanaohusika katika kuripoti na usindikaji wa data

2024 Mwandishi: Malcolm Clapton | [email protected]. Mwisho uliobadilishwa: 2024-01-13 03:06

Katika chapisho hili, Renat Shagabutdinov, Mkurugenzi Mkuu Msaidizi wa Mann, Ivanov na Ferber Publishing House, anashiriki udukuzi mzuri wa maisha wa Excel. Vidokezo hivi vitakuwa muhimu kwa mtu yeyote anayehusika katika kuripoti mbalimbali, usindikaji wa data na kuunda mawasilisho.

Nakala hii ina mbinu rahisi za kurahisisha kazi yako katika Excel. Ni muhimu sana kwa wale ambao wanajishughulisha na kuripoti kwa usimamizi, huandaa ripoti mbalimbali za uchambuzi kulingana na upakuaji kutoka kwa 1C na ripoti zingine, mawasilisho ya fomu na michoro kutoka kwao kwa usimamizi. Sijifanyi kuwa riwaya kabisa - kwa namna moja au nyingine, mbinu hizi labda zilijadiliwa kwenye vikao au zilizotajwa katika makala.

Njia mbadala rahisi za VLOOKUP na HLOOKUP, ikiwa thamani zinazohitajika haziko kwenye safu wima ya kwanza ya jedwali: LOOKUP, INDEX + SEARCH

VLOOKUP na HLOOKUP hufanya kazi tu ikiwa thamani zinazohitajika ziko kwenye safu wima ya kwanza au safu mlalo ya jedwali ambayo unapanga kupata data.

Vinginevyo, kuna chaguzi mbili:

1. Tumia kitendakazi cha LOOKUP.

   Ina syntax ifuatayo: LOOKUP (lokup_value; lookup_vector; result_vector). Lakini ili ifanye kazi kwa usahihi, maadili ya anuwai ya view_vector lazima yapangwa kwa mpangilio wa kupanda:

   

2. Tumia mchanganyiko wa vitendaji vya MATCH na INDEX.

   Kazi ya MATCH inarudisha nambari ya mpangilio ya kitu kwenye safu (kwa msaada wake unaweza kupata ni safu gani ya jedwali kipengee kilichotafutwa), na kazi ya INDEX inarudisha kipengee cha safu na nambari fulani (ambayo tutagundua. kwa kutumia kitendakazi cha MATCH).

   

   Sintaksia ya utendakazi:

   • TAFUTA (search_value; search_array; match_type) - kwa upande wetu, tunahitaji aina inayolingana "inayolingana kabisa", inayolingana na nambari 0.

   • INDEX (safu; nambari_ya_laini; [nambari_ya_safu]). Katika kesi hii, huna haja ya kutaja nambari ya safu, kwani safu ina safu moja.

Jinsi ya kujaza haraka seli tupu kwenye orodha

Kazi ni kujaza seli kwenye safu na maadili hapo juu (ili mada iko katika kila safu ya jedwali, na sio tu kwenye safu ya kwanza ya kizuizi cha vitabu kwenye mada):

Chagua safu ya "Somo", bonyeza kwenye Ribbon kwenye kikundi cha "Nyumbani", kitufe cha "Pata na uchague" → "Chagua kikundi cha seli" → "Seli tupu" na uanze kuingiza formula (ambayo ni, weka sawa. ishara) na urejelee kisanduku kilicho juu, kwa kubofya kishale cha juu kwenye kibodi yako. Baada ya hayo bonyeza Ctrl + Ingiza. Baada ya hayo, unaweza kuhifadhi data iliyopokelewa kama maadili, kwani fomula hazihitajiki tena:

Jinsi ya kupata makosa katika fomula

Uhesabuji wa sehemu tofauti ya fomula

Ili kuelewa fomula changamano (ambamo vipengele vingine vya kukokotoa hutumiwa kama hoja za chaguo za kukokotoa, yaani, baadhi ya chaguo za kukokotoa zimewekwa katika nyingine) au kupata chanzo cha makosa ndani yake, mara nyingi unahitaji kukokotoa sehemu yake. Kuna njia mbili rahisi:

1. Ili kukokotoa sehemu ya fomula moja kwa moja kwenye upau wa fomula, chagua sehemu hiyo na ubonyeze F9:

   

   Katika mfano huu, kulikuwa na tatizo na kipengele cha SEARCH - hoja zilibadilishwa ndani yake. Ni muhimu kukumbuka kwamba ikiwa hutaghairi hesabu ya sehemu ya kazi na bonyeza Ingiza, basi sehemu iliyohesabiwa itabaki nambari.

2. Bofya kitufe cha Kokotoa Mfumo katika kikundi cha Fomula kwenye utepe:

   

   Katika dirisha inayoonekana, unaweza kuhesabu formula hatua kwa hatua na kuamua ni hatua gani na ni kazi gani kosa linatokea (ikiwa ipo):

   

Jinsi ya kuamua ni nini fomula inategemea au inarejelea

Ili kubainisha ni seli zipi zinategemea fomula, katika kikundi cha Mifumo kwenye utepe, bofya kitufe cha Viini vinavyoathiri:

Mishale inaonekana kuonyesha matokeo ya hesabu hutegemea.

Ikiwa ishara iliyoangaziwa kwenye picha katika nyekundu imeonyeshwa, basi fomula inategemea seli kwenye karatasi zingine au katika vitabu vingine:

Kwa kubofya juu yake, tunaweza kuona mahali ambapo seli zinazoathiri au safu ziko:

Karibu na kitufe cha "Seli zinazoathiri" ni kitufe cha "Seli tegemezi", ambayo inafanya kazi kwa njia ile ile: inaonyesha mishale kutoka kwa seli inayofanya kazi na formula kwa seli zinazoitegemea.

Kitufe "Ondoa mishale", kilicho kwenye kizuizi kimoja, hukuruhusu kuondoa mishale kwa seli zinazoathiri, mishale kwa seli tegemezi, au aina zote mbili za mishale mara moja:

Jinsi ya kupata jumla (idadi, wastani) ya thamani za seli kutoka laha nyingi

Wacha tuseme una laha kadhaa za aina moja na data ambayo ungependa kuongeza, kuhesabu, au kuchakata kwa njia nyingine:

Ili kufanya hivyo, kwenye seli ambayo unataka kuona matokeo, ingiza fomula ya kawaida, kwa mfano SUM (SUM), na taja jina la karatasi ya kwanza na ya mwisho kutoka kwenye orodha ya karatasi hizo ambazo unahitaji kusindika. hoja, ikitenganishwa na koloni:

Utapokea jumla ya seli zilizo na anwani B3 kutoka kwa laha "Data1", "Data2", "Data3":

Anwani hii inafanya kazi kwa laha zilizoko mfululizo … Sintaksia ni kama ifuatavyo: = FUNCTION (orodha_ya_kwanza: orodha_ya_mwisho! Rejeleo la masafa).

Jinsi ya kuunda vifungu vya violezo kiotomatiki

Kutumia kanuni za msingi za kufanya kazi na maandishi katika Excel na kazi chache rahisi, unaweza kuandaa misemo ya template kwa ripoti. Kanuni kadhaa za kufanya kazi na maandishi:

• Tunaunganisha maandishi kwa kutumia & ishara (unaweza kuibadilisha na chaguo za kukokotoa CONCATENATE, lakini hiyo haileti maana sana).
• Maandishi huandikwa kila mara kwa nukuu, marejeleo ya seli zilizo na maandishi huwa hayana.
• Ili kupata herufi ya huduma "alama za nukuu", tumia kitendakazi cha CHAR na hoja 32.

Mfano wa kuunda kifungu cha kiolezo kwa kutumia fomula:

Matokeo:

Katika kesi hii, pamoja na kazi ya CHAR (kuonyesha nukuu), kazi ya IF inatumiwa, ambayo inakuwezesha kubadilisha maandishi kulingana na ikiwa kuna mwenendo mzuri wa mauzo, na kazi ya TEXT, ambayo inakuwezesha kuonyesha nambari katika muundo wowote. Sintaksia yake imefafanuliwa hapa chini:

TEXT (thamani; umbizo)

Umbizo limebainishwa katika alama za nukuu, kama vile unaingiza umbizo maalum katika dirisha la Seli za Umbizo.

Maandishi changamano zaidi yanaweza pia kuwa otomatiki. Katika mazoezi yangu, kulikuwa na otomatiki ya maoni marefu, lakini ya kawaida kwa kuripoti kwa usimamizi katika muundo INDICATOR ilianguka / ilipanda XX kulingana na mpango, haswa kwa sababu ya ukuaji / kupungua kwa FACTOR1 na XX, ukuaji / kupungua kwa FACTOR2 na YY …” na orodha inayobadilika ya vipengele. Ikiwa utaandika maoni kama haya mara nyingi na mchakato wa kuyaandika unaweza kusawazishwa, ni muhimu mara moja kutatanisha kuunda fomula au jumla ambayo itakuokoa angalau baadhi ya kazi.

Jinsi ya kuhifadhi data katika kila seli baada ya kuunganishwa

Unapounganisha visanduku, thamani moja pekee ndiyo inayobaki. Excel inaonya juu ya hili wakati wa kujaribu kuunganisha seli:

Ipasavyo, ikiwa ulikuwa na fomula kulingana na kila seli, itaacha kufanya kazi baada ya kuzichanganya (# N / A kosa katika mistari 3-4 ya mfano):

Ili kuunganisha seli na bado kuhifadhi data katika kila moja yao (labda unayo fomula kama katika mfano huu dhahania; labda unataka kuunganisha seli, lakini uhifadhi data yote kwa siku zijazo au uifiche kwa kukusudia), unganisha seli zozote kwenye laha., zichague, na kisha utumie amri ya Mchoraji wa Umbizo kuhamisha umbizo kwenye seli ambazo unahitaji kuchanganya:

Jinsi ya kuunda egemeo kutoka kwa vyanzo vingi vya data

Ikiwa unahitaji kuunda egemeo kutoka kwa vyanzo kadhaa vya data mara moja, itabidi uongeze "PivotTable na Chati Wizard" kwenye utepe au paneli ya ufikiaji wa haraka, ambayo ina chaguo kama hilo.

Unaweza kufanya hivi kama ifuatavyo: "Faili" → "Chaguo" → "Zana ya Ufikiaji Haraka" → "Amri Zote" → "Jedwali la Pivot na Mchawi wa Chati" → "Ongeza":

Baada ya hayo, ikoni inayolingana itaonekana kwenye Ribbon, ikibofya ambayo huita mchawi sawa:

Unapobofya, sanduku la mazungumzo linaonekana:

Ndani yake, unahitaji kuchagua kipengee "Katika safu kadhaa za uimarishaji" na bofya "Next". Katika hatua inayofuata, unaweza kuchagua "Unda shamba moja la ukurasa" au "Unda mashamba ya ukurasa". Ikiwa unataka kupata jina kwa kila moja ya vyanzo vya data, chagua kipengee cha pili:

Katika dirisha linalofuata, ongeza safu zote kwa msingi ambao pivot itajengwa, na uwape majina:

Baada ya hapo, katika kisanduku cha mwisho cha mazungumzo, taja ambapo ripoti ya jedwali la egemeo itawekwa - kwenye laha iliyopo au mpya:

Ripoti ya jedwali la egemeo iko tayari. Katika kichujio cha "Ukurasa wa 1", unaweza kuchagua moja tu ya vyanzo vya data, ikiwa ni lazima:

Jinsi ya kuhesabu idadi ya matukio ya maandishi A katika maandishi B ("ushuru wa MTS SuperMTS" - matukio mawili ya kifupi cha MTS)

Katika mfano huu, safu A ina mistari kadhaa ya maandishi, na kazi yetu ni kujua ni mara ngapi kila moja yao ina maandishi ya utaftaji yaliyo kwenye seli E1:

Ili kutatua tatizo hili, unaweza kutumia fomula tata inayojumuisha kazi zifuatazo:

1. DLSTR (LEN) - huhesabu urefu wa maandishi, hoja pekee ni maandishi. Mfano: DLSTR ("mashine") = 6.
2. SUBSTITUTE - hubadilisha maandishi maalum katika mfuatano wa maandishi na mwingine. Sintaksia: SUBSTITUTE (maandishi; maandishi_ya_zamani; maandishi_mapya). Mfano: SUBSTITUTE (“gari”; “otomatiki”; “”) = “simu ya mkononi”.
3. JUU - hubadilisha herufi zote kwenye mfuatano na herufi kubwa. Hoja pekee ni maandishi. Mfano: JUU ("mashine") = "GARI". Tunahitaji kipengele hiki kufanya utafutaji usiojali. Baada ya yote, JUU ("gari") = JUU ("Mashine")

Ili kupata utokeaji wa mfuatano fulani wa maandishi kwa mwingine, unahitaji kufuta matukio yake yote katika ule wa asili na kulinganisha urefu wa mfuatano unaotokana na ule wa asili:

DLSTR (“Tariff MTS Super MTS”) - DLSTR (“Tariff Super”) = 6

Na kisha ugawanye tofauti hii kwa urefu wa kamba tuliyokuwa tunatafuta:

6 / DLSTR (“MTS”) = 2

Ni mara mbili hasa kwamba mstari "MTS" umejumuishwa katika moja ya awali.

Inabakia kuandika algorithm hii katika lugha ya fomula (wacha tuonyeshe kwa "maandishi" maandishi ambayo tunatafuta matukio, na kwa "inatafutwa" - ile ambayo idadi yake ya matukio tunavutiwa nayo):

= (DLSTR (maandishi) -LSTR (SUBSTITUTE (JUU (maandishi); JUU (tafuta), ""))) / DLSTR (tafuta)

Katika mfano wetu, formula inaonekana kama hii:

= (DLSTR (A2) -LSTR (SUBSTITUTE (JUU (A2), JUU ($ E $ 1), “”))) / DLSTR ($ E $ 1)