Excel lub neej hacks rau cov neeg koom nrog hauv kev tshaj tawm thiab ua cov ntaub ntawv

2024 Tus sau: Malcolm Clapton | [email protected]. Kawg hloov kho: 2024-01-13 01:43

Nyob rau hauv no ncej, Renat Shagabutdinov, Assistant General Director ntawm Mann, Ivanov thiab Ferber Publishing House, qhia ib co txias Excel lub neej hacks. Cov lus qhia no yuav muaj txiaj ntsig zoo rau txhua tus neeg koom nrog hauv ntau yam kev tshaj tawm, ua cov ntaub ntawv thiab tsim kev nthuav qhia.

Kab lus no muaj cov tswv yim yooj yim los ua kom yooj yim rau koj txoj haujlwm hauv Excel. Lawv muaj txiaj ntsig tshwj xeeb rau cov neeg uas koom nrog hauv kev tshaj tawm kev tswj hwm, npaj ntau yam kev tshuaj ntsuam xyuas raws li kev rub tawm los ntawm 1C thiab lwm cov ntawv ceeb toom, daim ntawv nthuav qhia thiab cov duab kos los ntawm lawv rau kev tswj hwm. Kuv tsis ua txuj tias yog qhov tshiab tshiab - hauv ib daim ntawv lossis lwm yam, cov tswv yim no tau tham txog ntawm cov rooj sab laj lossis hais hauv cov lus.

Lwm txoj hauv kev yooj yim rau VLOOKUP thiab HLOOKUP, yog tias qhov xav tau qhov tseem ceeb tsis nyob hauv thawj kab ntawm lub rooj: LOOKUP, INDEX + SEARCH

Lub VLOOKUP thiab HLOOKUP ua haujlwm tsuas yog ua haujlwm yog tias qhov xav tau qhov tseem ceeb nyob hauv thawj kab lossis kab ntawm lub rooj uas koj npaj kom tau txais cov ntaub ntawv.

Txwv tsis pub, muaj ob txoj kev xaiv:

1. Siv LOOKUP muaj nuj nqi.

   Nws muaj cov hauv qab no syntax: LOOKUP (lookup_value; lookup_vector; result_vector). Tab sis kom nws ua hauj lwm kom raug, qhov tseem ceeb ntawm view_vector ntau yuav tsum tau muab cais nyob rau hauv ascending kev txiav txim:

   

2. Siv kev sib xyaw ua ke ntawm MATCH thiab INDEX cov haujlwm.

   MATCH muaj nuj nqi xa rov qab tus naj npawb ntawm cov khoom nyob rau hauv array (nrog nws pab koj tuaj yeem nrhiav tau nyob rau hauv kab ntawm lub rooj cov ntsiab lus tshawb nrhiav), thiab INDEX muaj nuj nqi xa rov qab cov ntsiab lus array nrog tus lej muab (uas peb yuav pom. siv MATCH muaj nuj nqi).

   

   Function syntax:

   • SEARCH (search_value; search_array; match_type) - rau peb cov ntaub ntawv, peb xav tau ib hom kev sib txuam "tsim match", nws sib raug rau tus lej 0.

   • INDEX (array; line_number; [column_number]). Hauv qhov no, koj tsis tas yuav qhia tus lej kab, txij li cov array muaj ib kab.

Yuav ua li cas kom ceev cov hlwb khoob hauv ib daim ntawv teev npe

Lub luag haujlwm yog sau cov hlwb hauv kab nrog cov txiaj ntsig ntawm sab saum toj (kom lub ntsiab lus nyob hauv txhua kab ntawm lub rooj, thiab tsis yog hauv thawj kab ntawm cov ntawv thaiv ntawm lub ncauj lus):

Xaiv kab "Subject", nyem rau ntawm kab hauv "Tsev" pab pawg, "Nrhiav thiab Xaiv" khawm → "Xaiv ib pab pawg ntawm cov hlwb" → "Siv hlwb" thiab pib nkag mus rau cov qauv (uas yog, muab qhov sib npaug. kos npe) thiab xa mus rau lub xovtooj ntawm sab saum toj, tsuas yog nyem rau ntawm koj cov keyboard. Tom qab ntawd nias Ctrl + Enter. Tom qab ntawd, koj tuaj yeem khaws cov ntaub ntawv tau txais los ua qhov tseem ceeb, vim tias cov qauv tsis xav tau ntxiv lawm:

Yuav ua li cas thiaj nrhiav tau qhov yuam kev hauv cov qauv

Kev suav ntawm ib feem ntawm ib qho qauv

Txhawm rau nkag siab txog cov qauv tsim nyog (nyob rau hauv uas lwm cov haujlwm tau siv los ua cov lus sib cav, uas yog, qee qhov haujlwm tau ze rau lwm tus) lossis txhawm rau nrhiav qhov yuam kev hauv nws, koj feem ntau yuav tsum suav ib feem ntawm nws. Muaj ob txoj hauv kev yooj yim:

1. Txhawm rau xam ib feem ntawm tus qauv txoj cai nyob rau hauv daim ntawv qhia bar, xaiv qhov ntawd thiab nias F9:

   

   Hauv qhov piv txwv no, muaj teeb meem nrog SEARCH muaj nuj nqi - kev sib cav tau hloov pauv hauv nws. Nws yog ib qho tseem ceeb uas yuav tsum nco ntsoov tias yog tias koj tsis thim qhov kev suav ntawm ib feem ntawm qhov kev ua haujlwm thiab nias Enter, ces qhov kev suav yuav nyob twj ywm tus lej.

2. Nyem rau lub pob "Calculate Formula" nyob rau hauv pawg Formulas ntawm lub ribbon:

   

   Nyob rau hauv lub qhov rais uas tshwm, koj tuaj yeem xam cov qauv ib kauj ruam thiab txiav txim siab nyob rau theem twg thiab nyob rau hauv qhov kev ua haujlwm qhov yuam kev tshwm sim (yog tias muaj):

   

Yuav ua li cas txiav txim siab seb tus qauv yog nyob ntawm lossis hais txog

Txhawm rau txiav txim siab seb cov hlwb twg ib qho qauv nyob ntawm, hauv Formulas pawg ntawm kab, nyem lub pob cuam tshuam rau Cells:

Cov xub tshwm los qhia tias qhov txiaj ntsig ntawm kev suav yog nyob ntawm.

Yog tias lub cim tseem ceeb hauv daim duab liab tshwm, ces tus qauv nyob ntawm lub hlwb ntawm lwm nplooj ntawv lossis hauv lwm phau ntawv:

Los ntawm txhaj rau nws, peb tuaj yeem pom raws nraim qhov chaw cuam tshuam cov hlwb lossis thaj chaw nyob:

Tom ntej no mus rau lub pob "Influencing Cells" yog khawm "Dependent Cells", uas ua haujlwm zoo ib yam: nws qhia cov xub los ntawm lub cev muaj zog nrog cov qauv rau cov hlwb uas nyob ntawm nws.

Lub pob "Tshem cov xub", nyob rau hauv tib lub thaiv, tso cai rau koj tshem tawm cov xub los cuam tshuam cov hlwb, xub mus rau cov hlwb, lossis ob hom xub ib zaug:

Yuav ua li cas thiaj nrhiav tau cov lej (tus lej, qhov nruab nrab) ntawm cov nqi ntawm tes los ntawm ntau daim ntawv

Cia peb hais tias koj muaj ob peb nplooj ntawv tib yam nrog cov ntaub ntawv uas koj xav ntxiv, suav, lossis ua lwm txoj hauv kev:

Txhawm rau ua qhov no, hauv lub xov tooj ntawm tes uas koj xav pom qhov tshwm sim, nkag mus rau tus qauv qauv, piv txwv li SUM (SUM), thiab qhia lub npe ntawm thawj thiab kawg nplooj ntawv los ntawm cov npe ntawm cov nplooj ntawv uas koj yuav tsum tau ua hauv kev sib cav, sib cais los ntawm txoj hnyuv:

Koj yuav tau txais cov lej ntawm cov hlwb nrog qhov chaw nyob B3 los ntawm cov nplooj ntawv "Data1", "Data2", "Data3":

Qhov chaw nyob no ua haujlwm rau cov ntawv nyob tsis tu ncua … Cov syntax yog raws li nram no: = FUNCTION (first_list: last_list! Range reference).

Yuav ua li cas thiaj li tsim cov kab lus template

Siv cov hauv paus ntsiab lus ntawm kev ua haujlwm nrog cov ntawv hauv Excel thiab ob peb lub luag haujlwm yooj yim, koj tuaj yeem npaj cov kab lus rau cov ntawv ceeb toom. Muaj ntau lub hauv paus ntsiab lus ntawm kev ua haujlwm nrog cov ntawv:

• Peb sib txuas cov ntawv siv lub & kos npe (koj tuaj yeem hloov nws nrog CONCATENATE muaj nuj nqi, tab sis qhov ntawd tsis muaj txiaj ntsig ntau).
• Cov ntawv nyeem ib txwm sau nyob rau hauv quotes, references rau cell nrog cov ntawv nyeem yog ib txwm tsis muaj.
• Kom tau txais cov cim kev pabcuam "cov lus hais", siv CHAR muaj nuj nqi nrog kev sib cav 32.

Ib qho piv txwv ntawm kev tsim cov kab lus template siv cov qauv:

Qhov tshwm sim:

Hauv qhov no, ntxiv rau CHAR muaj nuj nqi (los tso saib cov lus qhia), IF muaj nuj nqi yog siv, uas tso cai rau koj hloov cov ntawv nyob ntawm seb puas muaj kev muag khoom zoo, thiab TEXT muaj nuj nqi, uas tso cai rau koj los tso saib cov ntawv nyeem. tooj nyob rau hauv txhua hom ntawv. Nws syntax tau piav qhia hauv qab no:

TEXT (tus nqi; hom)

Cov hom ntawv tau teev nyob rau hauv cov lus hais, ib yam li yog tias koj tau nkag mus rau ib hom kev cai hauv Format Cells qhov rai.

Ntau cov ntawv nyuaj kuj tseem tuaj yeem siv tau. Hauv kuv qhov kev coj ua, muaj qhov automation ntev, tab sis niaj hnub cov lus qhia txog kev tswj hwm hauv hom INDICATOR poob / sawv los ntawm XX piv rau txoj kev npaj, feem ntau yog vim kev loj hlob / poob ntawm FACTOR1 los ntawm XX, kev loj hlob / poob ntawm FACTOR2 los ntawm YY …”nrog rau cov npe hloov pauv ntawm yam. Yog tias koj sau cov lus no ntau zaus thiab cov txheej txheem ntawm kev sau lawv tuaj yeem raug algorithmized, nws yog ib qho tsim nyog ib zaug puzzling los tsim ib qho qauv lossis macro uas yuav cawm koj tsawg kawg qee qhov haujlwm.

Yuav khaws cov ntaub ntawv li cas hauv txhua lub xov tooj tom qab sib txuas

Thaum koj sib koom ua ke ntawm tes, tsuas yog ib tus nqi khaws cia. Excel ceeb toom txog qhov no thaum sim ua ke cov hlwb:

Raws li, yog tias koj muaj ib qho qauv nyob ntawm txhua lub xov tooj, nws yuav tsum tsis ua haujlwm tom qab sib txuas lawv (# N / A yuam kev hauv kab 3-4 ntawm qhov piv txwv):

Txhawm rau sib koom ua ke ntawm tes thiab tseem khaws cov ntaub ntawv hauv txhua tus ntawm lawv (tej zaum koj muaj cov qauv zoo li hauv qhov piv txwv tsis meej no; tej zaum koj xav sib koom ua ke, tab sis khaws tag nrho cov ntaub ntawv rau yav tom ntej lossis zais nws txhob txwm), sib koom ua ke ntawm cov ntawv., xaiv lawv, thiab tom qab ntawd siv Format Painter hais kom hloov cov formatting rau cov hlwb uas koj xav tau los ua ke:

Yuav ua li cas los tsim ib tug pivot los ntawm ntau qhov chaw cov ntaub ntawv

Yog tias koj xav tsim kom muaj qhov pivot los ntawm ntau qhov chaw cov ntaub ntawv ib zaug, koj yuav tsum tau ntxiv "PivotTable thiab Chart Wizard" rau lub ribbon lossis ceev nkag vaj huam sib luag, uas muaj xws li kev xaiv.

Koj tuaj yeem ua qhov no raws li hauv qab no: "File" → "Options" → "Quick Access Toolbar" → "All Commands" → "PivotTable and Chart Wizard" → "Add":

Tom qab ntawd, lub cim sib thooj yuav tshwm sim rau ntawm daim kab xev, nyem rau ntawm uas hu rau tib tus wizard:

Thaum koj nyem rau ntawm nws, lub dialog box tshwm:

Hauv nws, koj yuav tsum xaiv cov khoom "Nyob rau hauv ob peb consolidation ranges" thiab nyem "Tom ntej". Hauv cov kauj ruam tom ntej, koj tuaj yeem xaiv "Tsim ib nplooj ntawv teb" lossis "Tsim nplooj teb". Yog tias koj xav kom nws tus kheej tuaj nrog lub npe rau txhua qhov ntawm cov ntaub ntawv, xaiv cov khoom thib ob:

Nyob rau hauv lub qhov rais tom ntej no, ntxiv tag nrho cov ntau yam nyob rau hauv uas lub pivot yuav tsim, thiab muab lawv lub npe:

Tom qab ntawd, nyob rau hauv lub xeem dialog box, qhia qhov twg lub pivot rooj qhia yuav muab tso rau - nyob rau hauv ib tug uas twb muaj lawm los yog tshiab daim ntawv:

Daim ntawv qhia pivot yog npaj txhij. Hauv "Nplooj 1" lim, koj tuaj yeem xaiv tsuas yog ib qho ntawm cov ntaub ntawv, yog tias tsim nyog:

Yuav ua li cas xam tus naj npawb ntawm qhov tshwm sim ntawm cov ntawv A hauv cov ntawv B ("MTS SuperMTS tariff" - ob qhov tshwm sim ntawm cov ntawv luv MTS)

Hauv qhov piv txwv no, kab A muaj ob peb kab ntawv, thiab peb txoj haujlwm yog txhawm rau nrhiav seb muaj pes tsawg zaus ntawm lawv muaj cov ntawv tshawb fawb nyob hauv cell E1:

Yuav kom daws tau qhov teeb meem no, koj tuaj yeem siv cov qauv complex uas muaj cov haujlwm hauv qab no:

1. DLSTR (LEN) - xam qhov ntev ntawm cov ntawv nyeem, qhov kev sib cav nkaus xwb yog cov ntawv nyeem. Piv txwv li: DLSTR ("machine") = 6.
2. SUBSTITUTE - hloov cov ntawv tshwj xeeb hauv kab ntawv nrog lwm tus. Syntax: SUBSTITUTE (text; old_text; new_text). Piv txwv: SUBSTITUTE ("car"; "auto"; "") = "mobile".
3. UPPER - hloov tag nrho cov cim nyob rau hauv ib txoj hlua nrog uppercase. Qhov kev sib cav nkaus xwb yog cov ntawv. Piv txwv li: UPPER ("machine") = "CAR". Peb xav tau txoj haujlwm no los ua cov ntaub ntawv tshawb nrhiav tsis txaus ntseeg. Tom qab tag nrho, UPPER ("tsheb") = UPPER ("Machine")

Txhawm rau pom qhov tshwm sim ntawm qee cov kab ntawv hauv lwm qhov, koj yuav tsum tshem tawm tag nrho nws qhov tshwm sim hauv qhov qub thiab sib piv qhov ntev ntawm txoj hlua uas tshwm sim nrog tus thawj:

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

Thiab ces faib qhov sib txawv ntawm qhov ntev ntawm txoj hlua peb tab tom nrhiav:

6 / DLSTR (“MTS”) = 2

Nws yog raws nraim ob zaug uas kab "MTS" suav nrog hauv thawj ib qho.

Nws tseem yuav tsum tau sau cov algorithm no hauv cov lus ntawm cov qauv (cia peb qhia los ntawm "ntawv" cov ntawv uas peb tab tom nrhiav rau qhov tshwm sim, thiab los ntawm "nrhiav" - tus naj npawb ntawm qhov tshwm sim peb xav tau):

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

Hauv peb qhov piv txwv, cov qauv zoo li no:

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