Mwongozo Kamili wa Excel Formulas: Formula Zote Muhimu na Jinsi Zinavyofanya Kazi

Kama unataka kuwa mtaalamu wa Microsoft Excel, basi lazima uelewe formulas. Hizi ndizo “engine” ya Excel — zinakuruhusu kufanya mahesabu, kuchambua data, na ku-automate kazi zako.

Katika blog hii utapata:

Formula zote muhimu (Basic hadi Advanced)
Jinsi zinavyofanya kazi
Mifano halisi
Tips za kitaalamu

👉 Kwa tutorials zaidi na mafunzo ya vitendo tembelea: https://faulink.com

SEHEMU YA 1: Formula ni Nini?

Formula ni hesabu au maelekezo unayoandika kwenye cell ili Excel ifanye calculation.

Mfano:
=A1 + B1

👉 Hii ina maana: chukua value ya A1 na uongeze na ya B1

SEHEMU YA 2: Aina za Formulas katika Excel

Formulas zimegawanyika katika makundi:

Basic (Rahisi)
Logical (Maamuzi)
Lookup (Kutafuta data)
Text (Kushughulikia maandishi)
Date & Time
Advanced / Data Analysis
SEHEMU YA 3: Basic Formulas (Muhimu Sana)
1. SUM (Jumla)
=SUM(A1:A10)

👉 Inaongeza values zote kuanzia A1 hadi A10

2. AVERAGE (Wastani)
=AVERAGE(A1:A10)

👉 Inatoa wastani wa namba

3. COUNT (Kuhesabu Namba)
=COUNT(A1:A10)

👉 Inahesabu cells zenye namba tu

4. COUNTA (Kuhesabu Zote)
=COUNTA(A1:A10)

👉 Inahesabu cells zote zenye data (text + number)

5. MAX & MIN
=MAX(A1:A10)
=MIN(A1:A10)

👉 MAX = kubwa zaidi
👉 MIN = ndogo zaidi

👉 Jifunze zaidi kwa mifano mingi: https://faulink.com

SEHEMU YA 4: Logical Formulas (IF na Zaidi)
1. IF Function
=IF(A1>=50, "PASS", "FAIL")

👉 Kama value ni >= 50 → PASS
👉 Vinginevyo → FAIL

2. IF Nyingi (Nested IF)
=IF(A1>=75,"A",IF(A1>=50,"B","C"))

👉 Inatumika kwa grading system

3. AND Function
=AND(A1>50, B1>50)

👉 Inarudisha TRUE kama masharti yote yametimia

4. OR Function
=OR(A1>50, B1>50)

👉 TRUE kama angalau moja imetimia

SEHEMU YA 5: Lookup Formulas (Kutafuta Data)
1. VLOOKUP
=VLOOKUP(A2, A1:C10, 2, FALSE)

👉 Inatafuta value kwenye column ya kwanza
👉 Inarudisha value kutoka column nyingine

2. HLOOKUP
=HLOOKUP(A2, A1:C10, 2, FALSE)

👉 Inafanya kazi kama VLOOKUP lakini kwa rows

3. XLOOKUP (Modern)
=XLOOKUP(A2, A1:A10, B1:B10)

👉 Bora zaidi kuliko VLOOKUP

4. INDEX + MATCH
=INDEX(B1:B10, MATCH(A2, A1:A10, 0))

👉 Powerful sana kwa data kubwa

👉 Soma zaidi kwa undani: https://faulink.com

SEHEMU YA 6: Text Formulas
1. CONCAT / CONCATENATE
=CONCAT(A1, " ", B1)

👉 Inaunganisha text

2. LEFT
=LEFT(A1, 4)

👉 Inachukua herufi za mwanzo

3. RIGHT
=RIGHT(A1, 3)

👉 Inachukua herufi za mwisho

4. MID
=MID(A1, 2, 4)

👉 Inachukua text katikati

5. LEN
=LEN(A1)

👉 Inahesabu idadi ya characters

SEHEMU YA 7: Date & Time Formulas
1. TODAY
=TODAY()

👉 Inaonyesha tarehe ya leo

2. NOW
=NOW()

👉 Inaonyesha tarehe + muda

3. DATE
=DATE(2026,5,3)

👉 Inatengeneza tarehe

SEHEMU YA 8: Math Formulas
1. ROUND
=ROUND(A1, 2)

👉 Inazungusha decimal

2. MOD
=MOD(A1, 2)

👉 Inatoa remainder

3. POWER
=POWER(A1,2)

👉 A1²

SEHEMU YA 9: Advanced Formulas
1. SUMIF
=SUMIF(A1:A10, ">50")

👉 Inaongeza kulingana na condition

2. COUNTIF
=COUNTIF(A1:A10, ">50")

👉 Inahesabu kulingana na condition

3. SUMIFS (Multiple Conditions)
=SUMIFS(B1:B10, A1:A10, ">50")
4. IFERROR
=IFERROR(A1/B1, "Error")

👉 Inazuia error kuonekana

👉 Mafunzo zaidi: https://faulink.com

SEHEMU YA 10: Dynamic Formulas (Modern Excel)
1. FILTER
=FILTER(A1:B10, A1:A10>50)

👉 Inaonyesha data inayokidhi condition

2. SORT
=SORT(A1:A10)

👉 Inapanga data

3. UNIQUE
=UNIQUE(A1:A10)

👉 Inaondoa duplicate

SEHEMU YA 11: Practical Example (Student System)
Jina Marks
John 80
Total:
=SUM(B1:B10)
Average:
=AVERAGE(B1:B10)
Grade:
=IF(B1>=50,"PASS","FAIL")
SEHEMU YA 12: Tips za Kitaalamu

✔ Tumia Absolute Reference

=$A$1

✔ Epuka errors kwa IFERROR
✔ Tumia named ranges

SEHEMU YA 13: Makosa ya Kuepuka

❌ Kutotumia brackets vizuri
❌ Kutumia range vibaya
❌ Kutokuelewa logic

SEHEMU YA 14: Hitimisho

Ukijua formulas hizi, utaweza:

✔ Kufanya kazi haraka
✔ Kuchambua data kitaalamu
✔ Kutengeneza systems (kama ulizokuwa unafanya PHP + MySQL)

👉 Endelea kujifunza kwa vitendo kupitia:
🔗 https://faulink.com