Jifunze jinsi ya kutumia RANK() kwenye Excel kupata nafasi ya number kulingana na condition au criteria fulani. Tazama tutorials zaidi, pakua mifumo ya Excel bure, na pata msaada wa WhatsApp.”

1. RANK() Function ya Msingi

Kama tulivyoshahazungumza, RANK() inarudisha position ya number kwenye list:

RANK(number, ref, [order])


number = number unayotaka kupata rank yake

ref = range ya numbers ili kuhesabu ranking

order (optional) = 0 au blank kwa descending (largest = rank 1), 1 kwa ascending (smallest = rank 1)

2. Conditional Ranking

Conditional Ranking inamaanisha: unapopata rank ya number kati ya subset ya data kulingana na criteria fulani.

Mfano:

A B
Item Category
Pen Office
Book School
Pencil Office
Eraser School

Tunataka rank ya items kulingana na category ya “Office” tu.

Formula:

=RANK(A2, IF(B$2:B$5="Office", A$2:A$5), 0)


IF(B$2:B$5="Office", A$2:A$5) → inachuja numbers tu za "Office"

RANK() inapata position kwa numbers zilizochaguliwa

Formula hii ni array formula kwenye Excel 2019 na chini (enter kwa Ctrl+Shift+Enter), kwenye Excel 365 inafanya automatically.

3. Conditional Ranking na FILTER() (Excel 365/2021)

Kwa Excel 365, unaweza kutumia FILTER() badala ya IF():

=RANK(A2, FILTER(A$2:A$5, B$2:B$5="Office"), 0)


FILTER() inachuja data kulingana na criteria

RANK() inarudisha position ya number katika filtered list

4. Conditional Ranking kwa Ascending Order
=RANK(A2, FILTER(A$2:A$5, B$2:B$5="Office"), 1)


order = 1 inapata rank kulingana na smallest number = 1

Useful kwa bottom analysis au cost-based ranking

5. Conditional Ranking kwa Multiple Criteria

Unaweza kuunda ranking kulingana na criteria nyingi:

Mfano: Items kulingana na category na status

A B C
Item Category Status
Pen Office Available
Book School Sold
Pencil Office Available
Eraser School Available

Formula:

=RANK(A2, FILTER(A$2:A$5, (B$2:B$5="Office")*(C$2:C$5="Available")), 0)


(B$2:B$5="Office")*(C$2:C$5="Available") → AND condition

FILTER() inachuja data yenye criteria zote

RANK() inapata position ya number katika filtered subset

6. Tips Muhimu

Conditional ranking ni muhimu kwa dynamic dashboards, sales performance, student grading, au reporting kulingana na category.

Kutumia FILTER() + RANK() inarahisisha array-based conditional analysis.

Kwenye Excel 2019 na chini, tumia IF() + Ctrl+Shift+Enter ili kuunda array formulas.

Links Muhimu

Tazama tutorials zaidi za Excel: https://www.faulink.com

Download mifumo ya Excel bure: https://www.faulink.com/excel_mifumo.php

Msaada wa WhatsApp: https://wa.me/255693118509