15 burvju formulas programmā Excel

Excel ir stingra tante. No vienas puses, tas ir neaizstājams rīks, lai izveidotu pārskatus, sarakstus un analīzes. No otras puses, vēlamo informāciju no izklājlapas varat iegūt tikai tad, ja apgūstat tipisko Excel valodu. Šādas Excel formulas saista visa veida attiecības ar šūnām, lai atgrieztu mērķtiecīgu informāciju. Šeit ir 15 funkcijas, kas var ietaupīt jūsu laiku.

Manuāls vai formulas vednis?

Mēs pieņemam, ka līdz šim jūs esat apguvis pamatformulas, lai piemērotu galvenās operācijas. Neiekļūstot speciālistu fokusā, mēs parādām, cik noderīgas formulas ir saliktas. Varat tos ievadīt manuāli, bet varat arī izmantot fxpoga formulu joslā: formulas vednis. Viņš paņems tevi aiz rokas, lai soli pa solim izveidotu formulu.

01 Pašreizējais laiks

Vai esat kāds, kurš regulāri aizmirst pareizi datēt savu darbu? Formula ŠODIEN automātiski aizpilda dienu, mēnesi un gadu, kamēr funkcija TAGAD pat pievienojot laiku minūtei. Pēc tam jūs rakstāt = ŠODIEN () vai =TAGAD (). Šīs funkcijas ir noderīgas arī darblapā, kurā vēlaties aprēķināt vērtību, pamatojoties uz pašreizējo dienu un laiku. Ar labo klikšķi un izvēli Šūnu īpašības pēc tam varat pielāgot datuma un laika attēlojumu. Lai atjauninātu šo laika informāciju aktīvajā darblapā, nospiediet Shift + F9; izmantojiet F9, lai atjauninātu visu darbgrāmatu.

02 Saskaitiet piepildītās šūnas

Ja jums ir šūnu grupa gan ar tekstu, gan cipariem un vēlaties uzzināt, cik daudz skaitļu ir atlasē, izmantojiet funkciju NUMBER. Pēc tam formulas struktūra izskatās šādi: = COUNT (meklēšanas apgabals). Starp iekavām parādās apgabals, kurā programmai Excel jāmeklē. Tās var būt šūnas zem vai blakus viena otrai, bet tā var būt arī taisnstūrveida šūnu atlase. Ja atlasē ir vārdi, tie būs kopā ar funkciju NUMBER neskaita. Ja vēlaties tikai saskaitīt visas šūnas, kurās kaut kas ir rakstīts, izmantojiet funkciju = COUNTA (bez punkta).

03 Cik bieži?

Izmantojiet funkciju, lai mērķtiecīgi uzskaitītu konkrētus datus COUNTIF. Pieņemsim, ka esat sastādījis grafiku, kurā parādās četri cilvēki, tad varat izmantot =COUNTIF (meklēšanas apgabals; “Hermanis”) redzēt, cik bieži parādās vārds Hermans. Ievadiet meklēšanas diapazonu starp iekavām un pievienojiet meklēšanas kritēriju pēdiņās.

04 Selektīvs papildinājums

Funkcija SUM šūnas tiek plaši izmantots. Gudrāks variants ir SUMIF (). Vispirms norādiet apgabalu, kurā programmai Excel jāmeklē starp iekavām. Meklēšanas diapazonam jābūt blakus esošo šūnu virknei. Pēc semikola jūs nosakāt, kas jāpievieno. Tie var būt skaitļi vai atsauce. Ja tas ir vienādojums, tas jāpievieno divkāršās pēdiņās. Piemēram =SUMIF (B20: B40; ”> 50”) padara visu šajā diapazonā esošo šūnu summu, kas ir lielāka par 50.

05 Papildinājums ar nosacījumu

Pievienošanas nosacījumu var paplašināt, izmantojot informāciju citā kolonnā. Piemērs to skaidri parāda. Pieņemsim, ka jums ir skaitļi, kas attiecas uz trim pilsētām: Amsterdamu, Roterdamu un Eindhovenu. Tad Amsterdamas numurus var pievienot tikai ar =SUMIF (diapazons; “Amsterdama”; pievienošanas diapazons). Šajā gadījumā formula kļūst par =SUMIF (C48: C54; “Amsterdam”; B48: B54). Vienkāršā valodā: Ja vārds Amsterdama ir diapazonā no C48 līdz C54, Excel jāapkopo atbilstošā šūnas vērtība blakus tai diapazonā no B48 līdz B54.

06 Apvienot

Ar funkciju LIKT TEKSTU KOPĀ jūs sapludināt datus no dažādām šūnām. Piemēram, šūnas ar vārdiem un uzvārdiem ar kaut ko līdzīgu =KATENĒT TEKSTS (E34, ““; F34). Divkāršās pēdiņas ar atstarpi nodrošina, ka starp vārdu un uzvārdu ir atstarpe. Tādā pašā veidā ir iespējams sapludināt tekstu ar valūtu. Piemēram, lai pievienotu valūtu eiro, tā jāievada kā funkcija, piemēram, =KATENĒT TEKSTS (A1, ““; B1, ”“ EURO (C1)). Jūs to lasāt kā “apvienot šūnas A1, B1 un C1 ar atstarpēm starp tām un novietot eiro zīmi apvienošanās trešā elementa priekšā”.

07 Aptiniet

Excel ir vairākas iespējas noapaļošanai. Standarta noapaļošana izskatās =ROUND (skaitlis, cipari aiz komata). Formula =KĀRTS (12.5624,1) tādējādi atgriežas 12,6. Galu galā jūs lūdzat noapaļot līdz vienam skaitlim aiz komata. Arī ar funkciju APSTIPRINĀTS UZ AUGŠU un Apaļai uz leju Excel noapaļos līdz jūsu norādītajam ciparam aiz komata. =APSVĒRTS UZ AUGŠU (12.5624,2) tādējādi atgriežas 12,57 un =Apaļš (12.5624,2) rezultātā 12,56. Funkcija INTEGRĒTS faktiski ir arī noapaļošanas funkcija, bet ar šo Excel noapaļo līdz tuvākajam veselajam skaitlim.

08 Lielie - mazie burti

Lai pārliecinātos, ka kolonnā viss parādās ar lielajiem burtiem, izmantojiet funkciju LIELIE BURTI. Formula LOWERCASE rīkojas pretēji. Un, ja vēlaties, lai katrs vārds sāktos ar lielo burtu, kam seko mazie burti, izmantojat funkciju Sākotnējās vēstules. Formula =MAZAS VĒSTULES (B4) parāda šūnas B4 saturu, bet ar mazajiem burtiem.

09 Zem nosacījuma

Ja aprēķins ir atkarīgs no noteiktiem apstākļiem, izmantojiet ALS-funkcija. Šīs funkcijas princips ir: =IF (nosacījums, aprēķins, ja nosacījums ir izpildīts, citi gadījumi). Lai formulētu nosacījumu, izmantojiet zīmes: = vienāds ar, nav vienāds ar > vairāk par, < mazāk nekā, >= vairāk vai vienāds ar= mazāks vai vienāds ar. Pieņemsim, ka organizācijā visi saņem prēmiju, kurš ir pārdevis cenu par 25 000 eiro vai vairāk. Ja saņemat prēmiju, blakus viņu vārdam automātiski parādīsies vārds “Hurray”, ja nē, parādīsies vārds “Diemžēl”. Tam nepieciešamā formula ir =JA (B2> = 2500; "Urā"; "Diemžēl").

10 lielākais - mazākais

Lai ātri atrastu augstāko un zemāko vērtību, ir funkcija MAX un MIN. Ar =MAX (B2: B37) jūs lūdzat augstāko vērtību no šīm šūnām un ar =MIN (B2: B37) jūs saņemat zemāko vērtību diapazonā. Funkcijas LIELĀKAIS un MAZĀKĀS ir smalkāki: jūs varat arī pieprasīt, piemēram, trešo lielāko vai otro mazāko. Lielāko var atrast ar =LIELS (B2: B37,1); skaitlis 1 norāda lielāko. Ar =LIELS (B2: B37,2) jūs saņemat otro lielāko un tā tālāk. Tādā veidā jūs varat viegli apkopot top 3 vai top 10.

11 Meklēt vertikāli

Pieņemsim, ka jums ir divas darblapas ar atšķirīgu informāciju par tiem pašiem cilvēkiem. Ar VLOOKUP iegūstiet informāciju no 1. darblapas 2. darblapas. Lai to atvieglotu, mēs abām cilnēm esam piešķīruši katrai personai unikālu reģistrācijas numuru. 2. cilnē norādiet arī diapazona nosaukumu, no kura vēlaties iegūt informāciju. Šajā piemērā 2. darblapā mēs atlasām kolonnas A un B un nosaukuma lodziņā augšējā kreisajā stūrī ierakstām nosaukumu Adrešu saraksts. 1. darblapas šūnā E2 mēs ievietojam funkciju VLOOKUP. Uzkrāšanās tagad ir =VLOOKUP (A2, direktorijs, 2, FALSE). A2 attiecas uz šūnu ar abonēšanas numuru otrajā darblapā, Adrešu saraksts norāda meklēšanas diapazonu, 2 ir kolonnas numurs 2. darblapā, kur atrodas pieprasītie dati. Pēdējais arguments ir loģiska vērtība, kur jūs FALSE ja vēlaties, lai atrastā vērtība precīzi atbilstu.

12 Dzēst atstarpes

Ar funkciju TRIM dzēst nevajadzīgas atstarpes tekstā. Šī funkcija atstāj dažas atstarpes starp vārdiem, bet noņems atstarpes pirms vai pēc vārda. =TRIM (šūnu diapazons) noderīgs tekstam, kas importēts no citas programmas. Dažās Excel versijās šo funkciju sauc SPOSES DELETE.

13 Apmaiņa

Izmantojot funkciju, kolonnu saturu var pārsūtīt uz rindām vai otrādi TRANSPOSĒT. Vispirms atlasiet šūnas, kurās jāievieto informācija. Noteikti atlasiet tik daudz šūnu, cik sākotnējā sērija. Šeit mēs ierakstījām gadus 8. rindā un ceturkšņus A slejā. Pēc tam ierakstiet funkciju =TRANSPOSĒT un atveriet iekavas. Pēc tam velciet pāri šūnām, kuras vēlaties apmainīt (šeit no šūnām B2 līdz E5). Aizveriet iekavas un tagad nospiediet taustiņu kombināciju Ctrl + Shift + Enter. Tādējādi tiks izveidota masīva formula, kas ir ietverta cirtainās iekavās.

14 Ikmēneša atmaksa

Ja jūs aizņematies pirkumam, cik jums jāmaksā mēnesī? Pieņemsim, ka jums ir 25 000 eiro (B1) aizņemas ar 6% procentu likmi (B2) uz 5 gadiem (B3). Mēs vednī parādām formulu, bet jūs varat arī vienkārši ierakstīt. Bite Interese novieto tevi B2 / 12, jo procenti attiecas uz gadu, un jūs vēlaties uzzināt, cik daudz jūs maksājat katru mēnesi. Bite Terminu skaits jūs vairojat B3 ar 12, jo gadi jāpārvērš mēnešos. Priekšmets Hw nozīmē Šī brīža vērtība, tas ir 25 000 eiro. Tas dod formulu =BET (B2 / 12; B3 * 12; B1) vai =BET (6% / 12,5 * 12,25000).

15 Viltus skaitļi

Eksperimentējot ar formulām, ir noderīgi viltoti dati. Funkcija IZVĒLIES STARP ģenerē nejaušus datus starp noteiktu zemāko un augstāko vērtību. Funkcija =RAND Starp starp (50,150) rada skaitļus no 49 līdz 151.

$config[zx-auto] not found$config[zx-overlay] not found