Excel life hacks para sa mga kasangkot sa pag-uulat at pagproseso ng data

2024 May -akda: Malcolm Clapton | [email protected]. Huling binago: 2024-01-13 03:13

Sa post na ito, si Renat Shagabutdinov, Assistant General Director ng Mann, Ivanov at Ferber Publishing House, ay nagbabahagi ng ilang cool na Excel life hacks. Ang mga tip na ito ay magiging kapaki-pakinabang para sa sinumang kasangkot sa iba't ibang pag-uulat, pagproseso ng data at paggawa ng mga presentasyon.

Naglalaman ang artikulong ito ng mga simpleng pamamaraan para gawing simple ang iyong trabaho sa Excel. Ang mga ito ay partikular na kapaki-pakinabang para sa mga nakikibahagi sa pag-uulat ng pamamahala, naghahanda ng iba't ibang analytical na ulat batay sa mga pag-download mula sa 1C at iba pang mga ulat, mga presentasyon ng form at mga diagram mula sa kanila para sa pamamahala. Hindi ako nagpapanggap na ganap na bagong bagay - sa isang anyo o iba pa, ang mga pamamaraan na ito ay malamang na tinalakay sa mga forum o nabanggit sa mga artikulo.

Mga simpleng alternatibo sa VLOOKUP at HLOOKUP, kung ang mga nais na halaga ay wala sa unang column ng talahanayan: LOOKUP, INDEX + SEARCH

Gumagana lang ang mga function ng VLOOKUP at HLOOKUP kung ang mga gustong value ay nasa unang column o row ng table kung saan mo planong kumuha ng data.

Kung hindi, mayroong dalawang pagpipilian:

1. Gamitin ang LOOKUP function.

   Mayroon itong sumusunod na syntax: LOOKUP (lookup_value; lookup_vector; result_vector). Ngunit para gumana ito ng tama, ang mga halaga ng saklaw ng view_vector ay dapat pagbukud-bukurin sa pataas na pagkakasunud-sunod:

   

2. Gumamit ng kumbinasyon ng MATCH at INDEX function.

   Ibinabalik ng MATCH function ang ordinal number ng isang elemento sa array (sa tulong nito makikita mo kung saang row ng table ang hinahanap na elemento), at ang INDEX function ay nagbabalik ng array element na may ibinigay na numero (na malalaman natin. gamit ang MATCH function).

   

   Syntax ng function:

   • SEARCH (search_value; search_array; match_type) - para sa aming kaso, kailangan namin ng uri ng pagtutugma na "eksaktong tugma", tumutugma ito sa numerong 0.

   • INDEX (array; line_number; [column_number]). Sa kasong ito, hindi mo kailangang tukuyin ang numero ng column, dahil ang array ay binubuo ng isang row.

Paano mabilis na punan ang mga walang laman na cell sa isang listahan

Ang gawain ay punan ang mga cell sa hanay ng mga halaga sa itaas (upang ang paksa ay nasa bawat hilera ng talahanayan, at hindi lamang sa unang hilera ng bloke ng mga libro sa paksa):

Piliin ang column na "Subject", mag-click sa ribbon sa grupong "Home", ang "Find and Select" button → "Pumili ng grupo ng mga cell" → "Blank cells" at simulan ang pagpasok ng formula (iyon ay, maglagay ng katumbas sign) at sumangguni sa cell sa itaas, sa pamamagitan lamang ng pag-click pataas na arrow sa iyong keyboard. Pagkatapos nito, pindutin ang Ctrl + Enter. Pagkatapos nito, maaari mong i-save ang natanggap na data bilang mga halaga, dahil hindi na kailangan ang mga formula:

Paano makahanap ng mga error sa isang formula

Pagkalkula ng isang hiwalay na bahagi ng isang formula

Upang maunawaan ang isang kumplikadong pormula (kung saan ang iba pang mga pag-andar ay ginagamit bilang mga argumento ng pag-andar, iyon ay, ang ilang mga pag-andar ay naka-nest sa iba) o upang mahanap ang pinagmulan ng mga error dito, madalas na kailangan mong kalkulahin ang bahagi nito. Mayroong dalawang madaling paraan:

1. Upang kalkulahin ang bahagi ng isang formula sa mismong formula bar, piliin ang bahaging iyon at pindutin ang F9:

   

   Sa halimbawang ito, nagkaroon ng problema sa SEARCH function - ang mga argumento ay pinagpalit dito. Mahalagang tandaan na kung hindi mo kanselahin ang pagkalkula ng bahagi ng function at pindutin ang Enter, kung gayon ang kinakalkula na bahagi ay mananatiling isang numero.

2. I-click ang pindutan ng Kalkulahin ang Formula sa pangkat ng Mga Formula sa laso:

   

   Sa window na lilitaw, maaari mong kalkulahin ang formula nang sunud-sunod at matukoy kung anong yugto at kung saan ang function ay nangyayari ang isang error (kung mayroon man):

   

Paano matukoy kung ano ang nakasalalay o tinutukoy ng isang formula

Upang matukoy kung aling mga cell ang nakasalalay sa isang formula, sa pangkat ng Mga Formula sa laso, i-click ang button na Nakakaapekto sa Mga Cell:

Lumilitaw ang mga arrow upang ipahiwatig kung saan nakasalalay ang resulta ng pagkalkula.

Kung ang simbolo na naka-highlight sa larawan sa pula ay ipinapakita, ang formula ay depende sa mga cell sa iba pang mga sheet o sa iba pang mga libro:

Sa pamamagitan ng pag-click dito, makikita natin nang eksakto kung saan matatagpuan ang mga nakakaimpluwensyang cell o range:

Sa tabi ng button na "Nakakaimpluwensya sa Mga Cell" ay ang button na "Mga Dependent na Cell", na gumagana sa parehong paraan: nagpapakita ito ng mga arrow mula sa aktibong cell na may formula patungo sa mga cell na umaasa dito.

Ang button na "Alisin ang mga arrow", na matatagpuan sa parehong bloke, ay nagbibigay-daan sa iyong alisin ang mga arrow sa nakakaimpluwensyang mga cell, mga arrow sa mga umaasa na cell, o parehong uri ng mga arrow nang sabay-sabay:

Paano mahahanap ang kabuuan (numero, average) ng mga halaga ng cell mula sa maraming mga sheet

Sabihin nating mayroon kang ilang sheet ng parehong uri na may data na gusto mong idagdag, bilangin, o iproseso sa ibang paraan:

Upang gawin ito, sa cell kung saan mo gustong makita ang resulta, magpasok ng karaniwang formula, halimbawa SUM (SUM), at tukuyin ang pangalan ng una at huling mga sheet mula sa listahan ng mga sheet na kailangan mong iproseso sa ang argumento, na pinaghihiwalay ng tutuldok:

Matatanggap mo ang kabuuan ng mga cell na may address na B3 mula sa mga sheet na "Data1", "Data2", "Data3":

Gumagana ang addressing na ito para sa mga sheet na matatagpuan tuloy-tuloy … Ang syntax ay ang sumusunod: = FUNCTION (first_list: last_list! Range reference).

Paano awtomatikong bumuo ng mga parirala ng template

Gamit ang mga pangunahing prinsipyo ng pagtatrabaho sa text sa Excel at ilang simpleng function, maaari kang maghanda ng mga parirala ng template para sa mga ulat. Maraming mga prinsipyo ng pagtatrabaho sa teksto:

• Pinagsasama-sama namin ang teksto gamit ang & sign (maaari mo itong palitan ng function na CONCATENATE, ngunit hindi iyon makatuwiran).
• Ang teksto ay palaging nakasulat sa mga panipi, ang mga sanggunian sa mga cell na may teksto ay palaging wala.
• Para makuha ang service character na "quotation marks", gamitin ang CHAR function na may argumento 32.

Isang halimbawa ng paglikha ng template na parirala gamit ang mga formula:

Resulta:

Sa kasong ito, bilang karagdagan sa CHAR function (upang magpakita ng mga quote), ang IF function ay ginagamit, na nagbibigay-daan sa iyo upang baguhin ang teksto depende sa kung mayroong isang positibong kalakaran sa pagbebenta, at ang TEXT function, na nagpapahintulot sa iyo na ipakita ang numero sa anumang format. Ang syntax nito ay inilarawan sa ibaba:

TEXT (halaga; format)

Tinukoy ang format sa mga panipi, na parang naglalagay ka ng custom na format sa window ng Format Cells.

Ang mas kumplikadong mga teksto ay maaari ding maging awtomatiko. Sa aking pagsasanay, nagkaroon ng automation ng mahaba, ngunit nakagawiang mga komento sa pag-uulat ng pamamahala sa format na INDICATOR ay bumagsak / tumaas ng XX kaugnay sa plano, pangunahin dahil sa paglago / pagbaba ng FACTOR1 ng XX, paglago / pagbaba ng FACTOR2 ng YY …” na may nagbabagong listahan ng mga salik. Kung madalas kang sumulat ng mga ganoong komento at ang proseso ng pagsulat ng mga ito ay maaaring i-algorithmize, ito ay kapaki-pakinabang sa sandaling nakakalito upang lumikha ng isang formula o isang macro na magliligtas sa iyo ng hindi bababa sa ilan sa mga gawain.

Paano mag-imbak ng data sa bawat cell pagkatapos ng concatenation

Kapag pinagsama mo ang mga cell, isang value lang ang mananatili. Nagbabala ang Excel tungkol dito kapag sinusubukang pagsamahin ang mga cell:

Alinsunod dito, kung mayroon kang formula depende sa bawat cell, hihinto ito sa paggana pagkatapos pagsamahin ang mga ito (# N / Isang error sa mga linya 3-4 ng halimbawa):

Upang pagsamahin ang mga cell at mapanatili pa rin ang data sa bawat isa sa kanila (marahil mayroon kang formula tulad ng abstract na halimbawang ito; marahil gusto mong pagsamahin ang mga cell, ngunit panatilihin ang lahat ng data para sa hinaharap o itago ito nang sinasadya), pagsamahin ang anumang mga cell sa sheet, piliin ang mga ito, at pagkatapos ay gamitin ang command na Format Painter upang ilipat ang pag-format sa mga cell na kailangan mong pagsamahin:

Paano bumuo ng pivot mula sa maraming data source

Kung kailangan mong bumuo ng pivot mula sa ilang pinagmumulan ng data nang sabay-sabay, kakailanganin mong idagdag ang "PivotTable at Chart Wizard" sa ribbon o panel ng mabilisang pag-access, na mayroong ganoong opsyon.

Magagawa mo ito tulad ng sumusunod: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and Chart Wizard" → "Add":

Pagkatapos nito, lilitaw ang isang kaukulang icon sa laso, pag-click sa kung saan tumatawag sa parehong wizard:

Kapag nag-click ka dito, lilitaw ang isang dialog box:

Sa loob nito, kailangan mong piliin ang item na "Sa ilang mga saklaw ng pagsasama-sama" at i-click ang "Susunod". Sa susunod na hakbang, maaari mong piliin ang "Gumawa ng isang field ng pahina" o "Gumawa ng mga field ng pahina." Kung gusto mong independiyenteng makabuo ng isang pangalan para sa bawat isa sa mga pinagmumulan ng data, piliin ang pangalawang item:

Sa susunod na window, idagdag ang lahat ng mga saklaw batay sa kung saan ang pivot ay bubuo, at bigyan sila ng mga pangalan:

Pagkatapos nito, sa huling dialog box, tukuyin kung saan ilalagay ang ulat ng pivot table - sa isang umiiral o bagong sheet:

Handa na ang ulat ng pivot table. Sa filter na "Pahina 1," maaari ka lang pumili ng isa sa mga pinagmumulan ng data, kung kinakailangan:

Paano kalkulahin ang bilang ng mga paglitaw ng teksto A sa teksto B ("MTS SuperMTS taripa" - dalawang paglitaw ng pagdadaglat na MTS)

Sa halimbawang ito, ang column A ay naglalaman ng ilang linya ng teksto, at ang aming gawain ay alamin kung gaano karaming beses ang bawat isa sa kanila ay naglalaman ng teksto ng paghahanap na matatagpuan sa cell E1:

Upang malutas ang problemang ito, maaari kang gumamit ng isang kumplikadong formula na binubuo ng mga sumusunod na function:

1. DLSTR (LEN) - kinakalkula ang haba ng teksto, ang tanging argumento ay ang teksto. Halimbawa: DLSTR ("machine") = 6.
2. SUBSTITUTE - pinapalitan ang isang partikular na text sa isang text string ng isa pa. Syntax: SUBSTITUTE (teksto; old_text; new_text). Halimbawa: SUBSTITUTE (“kotse”; “auto”; “”) = “mobile”.
3. UPPER - pinapalitan ang lahat ng mga character sa isang string ng uppercase. Ang tanging argumento ay teksto. Halimbawa: UPPER (“machine”) = “CAR”. Kailangan namin ang function na ito upang makagawa ng mga case insensitive na paghahanap. Pagkatapos ng lahat, UPPER ("kotse") = UPPER ("Machine")

Upang mahanap ang paglitaw ng isang tiyak na string ng teksto sa isa pa, kailangan mong tanggalin ang lahat ng mga paglitaw nito sa orihinal at ihambing ang haba ng nagresultang string sa orihinal:

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

At pagkatapos ay hatiin ang pagkakaibang ito sa haba ng string na hinahanap namin:

6 / DLSTR (“MTS”) = 2

Eksaktong dalawang beses na ang linyang "MTS" ay kasama sa orihinal.

Nananatili itong isulat ang algorithm na ito sa wika ng mga formula (ipahiwatig natin sa pamamagitan ng "teksto" ang teksto kung saan hinahanap natin ang mga pangyayari, at sa pamamagitan ng "hinanap" - ang isa na ang bilang ng mga pangyayari ay interesado tayo):

= (DLSTR (text) -LSTR (SUBSTITUTE (UPPER (text); UPPER (search), ""))) / DLSTR (search)

Sa aming halimbawa, ang formula ay ganito:

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