Karatasi ya Maelezo ya Excel

Zana zote
Bila Malipo

Rejeleo linalotafutika na linaloweza kuchapishwa la Excel — fomula muhimu, vitendakazi vya kutafuta, vitendakazi vya maandishi na tarehe, njia za mkato na jedwali za pivot. Bila malipo.

Essential formulas

10
=SUM(A1:A10)
Add a range of numbers
=AVERAGE(A1:A10)
Mean of a range
=COUNT(A1:A10)
Count cells containing numbers
=COUNTA(A1:A10)
Count non-empty cells
=MIN(A1:A10) =MAX(A1:A10)
Smallest / largest value
=ROUND(A1, 2)
Round to 2 decimal places
=SUMIF(B:B,">100")
Sum values matching a condition
=SUMIFS(C:C,A:A,"x",B:B,">5")
Sum with multiple conditions
=COUNTIF(A:A,"yes")
Count cells matching a condition
=AVERAGEIF(A:A,">0")
Average of cells matching a condition

Lookup & reference

9
=VLOOKUP(key,A:C,3,FALSE)
Look up a key, return col 3 (exact match)
=HLOOKUP(key,1:3,2,FALSE)
Horizontal lookup across rows
=XLOOKUP(key,A:A,C:C)
Modern lookup (any direction)
=XLOOKUP(key,A:A,C:C,"N/A")
XLOOKUP with a not-found default
=INDEX(C:C,MATCH(key,A:A,0))
Flexible lookup left or right
=MATCH(key,A:A,0)
Position of a value in a range
=INDEX(A1:C10,2,3)
Value at row 2, column 3 of a range
=OFFSET(A1,2,1)
Reference offset from a cell
=INDIRECT("A"&row)
Build a reference from text

Text functions

11
=LEFT(A1,3) =RIGHT(A1,3)
First / last 3 characters
=MID(A1,2,4)
4 chars starting at position 2
=LEN(A1)
Number of characters
=TRIM(A1)
Remove extra spaces
=UPPER / LOWER / PROPER(A1)
Change case
=CONCAT(A1,B1)
Join text values
=TEXTJOIN(", ",TRUE,A1:A5)
Join a range with a delimiter
=SUBSTITUTE(A1,"-","")
Replace text occurrences
=FIND("x",A1)
Position of text (case-sensitive)
=TEXT(A1,"0.00%")
Format a number as text
=TEXTSPLIT(A1,",")
Split text into columns by a delimiter

Date & time

9
=TODAY() =NOW()
Current date / date-time
=YEAR(A1) =MONTH(A1) =DAY(A1)
Extract date parts
=DATE(2026,6,26)
Build a date from parts
=DATEDIF(start,end,"d")
Days/months/years between dates
=EDATE(A1,3)
Date 3 months after A1
=EOMONTH(A1,0)
Last day of the month
=NETWORKDAYS(start,end)
Working days between dates
=WEEKDAY(A1,2)
Day of week (Mon=1)
=A1-B1
Difference in days between dates

Logical & error handling

9
=IF(A1>10,"hi","lo")
Branch on a condition
=IF(A1>10,IF(A1>20,"x","y"),"z")
Nested IF
=IFS(A1>20,"x",A1>10,"y",TRUE,"z")
Cleaner multi-branch logic
=AND(A1>0,B1<10)
True if all conditions hold
=OR(A1>0,B1<10)
True if any condition holds
=NOT(A1)
Invert a boolean
=IFERROR(A1/B1,0)
Return 0 on error
=IFNA(VLOOKUP(...),"")
Handle only #N/A errors
=SWITCH(A1,1,"a",2,"b","?")
Match a value to a result

References & names

9
A1
Relative reference (shifts on copy)
$A$1
Absolute reference (locked)
A$1 / $A1
Lock row only / column only
F4
Cycle reference lock styles while editing
Sheet2!A1
Reference a cell on another sheet
A1:A10
A range of cells
A:A / 1:1
Whole column / whole row
Name Box: TaxRate
Define a named range for clarity
=Table1[Amount]
Structured reference to a table column

Keyboard shortcuts

11
Ctrl + C / V / X
Copy / paste / cut
Ctrl + Z / Y
Undo / redo
Ctrl + Shift + V
Paste special
Ctrl + Arrow
Jump to edge of data region
Ctrl + Shift + Arrow
Select to edge of data region
Ctrl + ;
Insert today's date
Alt + =
AutoSum the column/row
Ctrl + T
Convert range to a Table
F2
Edit the active cell
Ctrl + Shift + L
Toggle filters
Ctrl + 1
Open Format Cells dialog

Pivot tables & analysis

9
Insert > PivotTable
Summarize data interactively
Rows / Columns / Values
Drag fields to build the layout
Value Field Settings
Switch Sum / Count / Average etc.
Show Values As > % of Total
Display values as a share
Right-click > Group
Group dates or numbers into bins
Refresh (Alt + F5)
Reload pivot from source data
Insert > Slicer
Add visual filter buttons
=GETPIVOTDATA(...)
Reference a pivot cell in a formula
Insert > PivotChart
Chart driven by a pivot table

Hakuna kipengele kinacholingana na “:q”.

Kuhusu Karatasi ya Maelezo ya Excel

Karatasi hii ya kumbukumbu ya Excel inakusanya fomula na njia za mkato ambazo kazi ya lahajedwali hutegemea: fomula za msingi, functions za kutafuta na kurejelea, functions za maandishi, tarehe na muda, mantiki na ushughulikiaji wa makosa, marejeleo na majina, njia za mkato za kibodi, na jedwali za pivot pamoja na zana za uchambuzi.

Inajibu maswali yanayovuruga kazi halisi — mpangilio wa hoja za function ya kutafuta, jinsi ya kujenga IF yenye ushughulikiaji wa makosa, function ipi hutoa sehemu ya seli ya maandishi, na tofauti kati ya marejeleo ya jamaa (relative) na kamili (absolute) — kila moja ikiwa fomula unayoweza kunakili na kubadilisha.

Karatasi ni bure na inapakia kikamilifu kwenye kivinjari chako. Utafutaji huchuja kila fomula papo hapo, orodha ya yaliyomo inayoning'inia huruka kati ya sehemu, mbofyo mmoja hunakili fomula yoyote kwenye clipboard yako na kitufe cha kuchapisha hutengeneza kumbukumbu ya karatasi kwa ajili ya ofisi.

Jinsi ya kutumia Karatasi ya Maelezo ya Excel

  1. Pitia sehemu, kuanzia Fomula za msingi na Kutafuta na kurejelea hadi Jedwali za pivot na uchambuzi.
  2. Tafuta jina la function kama VLOOKUP, SUMIF au TEXT ili kuchuja karatasi papo hapo.
  3. Ruka hadi Njia za mkato za kibodi au Marejeleo na majina kupitia upau wa pembeni unaoning'inia.
  4. Bofya fomula au ikoni yake ya kunakili ili kuinakili, kisha ibandike kwenye seli na urekebishe fungu (ranges).
  5. Chapisha karatasi kwa ajili ya kumbukumbu ya fomula unayoweza kuweka pembeni ya kifuatiliaji chako.

Maswali yanayoulizwa mara kwa mara

Sehemu nane: fomula za msingi, kutafuta na kurejelea, functions za maandishi, tarehe na muda, mantiki na ushughulikiaji wa makosa, marejeleo na majina, njia za mkato za kibodi, na jedwali za pivot pamoja na vipengele vya uchambuzi.

Ndiyo. Sehemu ya kutafuta na kurejelea inaonyesha fomula za kawaida za kutafuta pamoja na mpangilio wa hoja zake na matumizi ya kawaida — mada inayotafutwa zaidi kwenye Excel.

Ndiyo. Sehemu maalum inaorodhesha njia za mkato za uhariri na uelekezaji zinazoharakisha kazi ya lahajedwali, pamoja na sehemu za fomula.

Ndiyo. Bofya fomula au ikoni yake ya kunakili nayo huwekwa kwenye clipboard yako; ibandike kwenye Excel na ubadilishe fungu la mfano na lako mwenyewe.

Ndiyo, ni bure, inayoweza kutafutwa, kuchapishwa na haihitaji kujisajili.
Shiriki hii
Zana Zinazohusiana
Karatasi ya Maelezo ya MySQL Rejeleo linalotafutika na linaloweza kuchapishwa la MySQL — hoja, joins, fahirisi, vitendakazi, aina za data na amri za usimamizi. Bila malipo. Bila Malipo
Karatasi ya Maelezo ya VS Code Rejeleo linalotafutika na linaloweza kuchapishwa la Visual Studio Code — njia za mkato za kibodi za kuhariri, usogezaji, multi-cursor, kutafuta na terminali. Bila malipo. Bila Malipo
Karatasi ya Maelezo ya Python Rejeleo linalotafutika na linaloweza kuchapishwa la Python 3 — sintaksia, miundo ya data, comprehensions, vitendakazi, klasi, mafaili na maktaba ya kawaida. Bila malipo. Bila Malipo
Karatasi ya Maelezo ya JavaScript Rejeleo linalotafutika na linaloweza kuchapishwa la JavaScript ya kisasa — sintaksia, safu, mifuatano, vitu, async/await, DOM na vipengele vya ES2022+. Bila malipo. Bila Malipo
Karatasi ya Maelezo ya PHP Rejeleo linalotafutika na linaloweza kuchapishwa la PHP 8.3+ — sintaksia, mifuatano, safu, vitendakazi, klasi, enums, sifa na vipengele vya kisasa. Bila malipo. Bila Malipo
Karatasi ya Maelezo ya Shell Rejeleo linalotafutika na linaloweza kuchapishwa la Bash / shell — usogezaji, mafaili, pipes, viwakilishi, loops, masharti na one-liners za manufaa. Bila malipo. Bila Malipo
Utafutaji maarufu
excel cheat sheet excel formulas list vlookup vs xlookup excel text functions excel date functions excel keyboard shortcuts excel if and iferror excel pivot table basics
Unahitaji msaada?
Umepata tatizo na zana hii? Tujulishe timu yetu.
Ripoti tatizo

Ongeza zana hii ya bure kwenye tovuti yako mwenyewe — nakili na ubandike msimbo ulio hapa chini.