Excel k‡ãi AvwfavwbK A_© nj †kªôZi nIqv| ¸Y, K…wZZ¡ cÖf„wZ‡Z †kªôZi ev DrK…óZi nIqv| Excel n‡jv Windows wfwËK GKwU Application program hv Microsoft Corporation KZ©„K evRviRvZK…Z Bnv Worksheet Analysis Program. GKB mv‡_ A‡bK mgm¨vi mgvav‡b Ab¨vb¨ A‡bK ‡cÖvMªvg †_‡K †kªôZi| G †cÖvMÖvgwUi mvnv‡h¨ RwUj MvwYwZK cwiMYbv, Z_¨ e¨e¯’vcbv Ges Z_¨‡K AvKl©Yxq K‡i Dc¯’vcbvq wbLuyZfv‡e PvU© ev MÖvd ˆZix Kiv BZ¨vw` QvovI AviI A‡bK RwUj KvR‡K AwZ mn‡R mgvcb Kiv hvq|
spread sheet.
Spread A_© Qov‡bv Ges Sheet A_© cvZv Spread Sheet A_© Qov‡bv cvZv| †Lvc †Lvc N‡ii b¨vq A‡bK Ni m¤^wjZ eo kxU‡K †¯cÖWkxU ejv nq|
Start Microsoft Excel.
* Start > Programs > Microsoft Excel (MS-Excel)
Row:
Row ‡K mvwi ejv nq| A_©vr evg cv‡ki 1,2,3,4 BZ¨vw`‡K Row ejv nq
Column:
Column ‡K mvaviYZ Dc‡ii A,B,C,D BZ¨vw` AvKv‡i †`Lv hvq|
Cell:
Excel IqvK©kxUwU mvwi I Kjvg wfwËK| Dc‡ii A,B,C,D nj wewfbœ Kjv‡gi bvg Ges cv‡ki 1,2,3,4 nj mvwi msL¨v| mvwi Ges Kjv‡gi ci¯úi †Q‡` ˆZix †QvU †QvU AvqZvKvi Ni‡K †mj ejv nq|
File menu (Alt+F) (dvBj †gbyi KvRt)
New: File > New > Ok
Open: File > Open > (e· †_‡K dvBj wbe©vPb K‡i) Open.
Close: File > Close
Save: File > Save ð File Name (dvB‡ji bvg wjL‡Z n‡e †hgb: Muaj) > Save (dvBjwU Muaj bv‡g †mf n‡q hv‡e|
Password: File > Save As > Options > Password to Open (e‡· cvmIqvW© wjL‡Z n‡e- S03K) > Reenter Password to Open (e‡· H GKB cvmIqvW© wjL‡Z n‡e S03K) > Ok > Save.
Open Password File: File > Open (†h dvB‡j cvmIqvW© Kiv
n‡q‡Q †mB dvBj wbe©vPb Ki‡Z n‡e) Ok.
Cancel Password File: Open Password File > File >
Save As > Options (Password to Open (e‡·i cvmIqvW© gy‡Q †dj‡Z n‡e) > Ok > Save.
Page Setup: (Paper Size) File > Page Setup > Page > Select Portrait or Landscape > Paper size G wK¬K K‡i wb‡Ri gvcgZ KvMR wVK K‡i wb‡Z n‡e|
Margin- Margin > Top Margin (Type New Number) > Bottom Margin (Type New Number) > Left Margin (Type New Number) > Right Margin (Type New Number)
Print Preview- File > Print preview (Print Kivi Av‡M ‡cRwUi wcÖ›U †Kgb n‡e Zv †`L‡Z PvB‡j Print preview ‡Z wK¬K Ki“b| > Close.
rint (Ctrl+P): File > Print > Print range Gi N‡i All G Click Ki‡j Document Gi me¸‡jv †iKW© wcÖ›U n‡e| > Pages (s) From to N‡i b¤^i wj‡L w`‡j ïay †m me cÖôv wcÖ›U n‡e| > Ok .
Edit Menu (Alt+E) (BwWU †gbyi KvR) t
# Undo (Ctrl+Z) ‡jLv cybti“×vi Kiv|
# Redo (Ctrl+Y) Kgv‡Ûi Kvh©KvwiZv evwZj Kivi Rb¨ e¨envi Kiv nq \
Copy & Past (Ctrl+C) / (Ctrl+V) > ‡jLv †k± K‡i > Edit
# Copy ‡hLv‡b Kwc Ki‡Z Pvb †mLv‡b Kvm©i ‡i‡L
# Edit ð Past.
Ab¨vb¨ KvR¸‡jv cÖvq gvB‡µvmdU IqvW© Gi b¨vq| weavq G¸‡jv Avi
we¯—vwiZ †j‡L mgq bó bv Kivq DËg|
How to Create a Chart
# wb‡gœ GKwU †Uwej ˆZix Ki“b Ges Zv m¤ú~Y© wm‡j± Ki“b|
A | B | C | D | E | |
1 | 4-YRS REPORT | ||||
2 | 1996 | 1997 | 1998 | 1999 | |
3 | BOGRA | 31.7 | 47.2 | 42.3 | 52.5 |
4 | DHAKA | 13.5 | 16.3 | 11.5 | 18.5 |
5 | KHULNA | 17.2 | 19.5 | 21.7 | 12.3 |
# Insert 8 Chart (Chart Wizard ‡gbywU Avm‡e|
Chart Type: ‡_‡K †hgb- Column, Bar, Line BZ¨vw` †h †Kvb GKwU wm‡j± K‡i Next Ki“b|
# Data range: Gi Series in ‡_‡K Rows/Column †h †Kvb GKwU wm‡j±
K‡i Next Ki“b|
# Chart Titles: ‡hgb UvB‡Uj wnmv‡e 4-Yrs Report Pvb Zvn‡j Chart Title e‡·
wj‡L w`b| Ges Category (X) Axis G hw` Year cÖ`k©b Ki‡Z Pvb Zvn‡j wj‡L w`b| Avevi Value (Y) axis G hw` Metric ton cÖ`k©b Ki‡Z Pvb Z‡e Zv wj‡L w`b|
# Gridlines: Category (x) axis `vM PvB‡j wK¬K Ki“b Ges Value (Y) axis `vM
PvB‡j wK¬K Ki“b|
# Legend: ‡hLv‡b Legend ‡bIqv cÖ‡qvRb †mLv‡b wK¬K Ki“b| Right,
Bottom, Top BZ¨vw`|
# Data Labels: hw` Show Value Ki‡Z Pvb Zvn‡j Show Value G wK¬K
Ki“b| Abyiƒcfv‡e Show Label Ki‡Z PvB‡j Show Label G wK¬K Ki“b| # Data Table: MÖv‡di mv‡_ Data Table Show Ki‡Z PvB‡j Data Table
Show G wK¬K Ki“b|
# Next 8 Finish G wK¬K Kivi ci MÖvdwU cvZvq cÖ`k©b Ki‡e|
How to use Functions
# (Equal Function)= dvskb hv MvwYwZK dg©~jv MVb K‡i ¯^qswµqfv‡e we‡kl cwiMYbv Calculation K‡i| cÖ‡qvRb Abymv‡i †h †Kvb †m‡j G ai‡Yi KvR Kivi mgq cÖ_‡gB Kx †evW© n‡Z = (Equal Sign) wPýwU UvBc K‡i wb‡Z nq e‡j mKj dvskb‡K = dvskb (Equal Function) ejv nq| wKQz = dvskb i‡q‡Q hv LyeB mnR †hgbt =SUM, hv †Kvb †iÄ Gi msL¨vi †hvMdj †ei K‡i|
# †hgb: =Sum (B1:B7), G dvskbwU (B1:B7) ‡iÄ Gi msL¨vi †hvMdj †ei K‡i hv UvBcK…Z B1+B2+B3+B4+B5+B6+B7 dg©~jv n‡Z mnRZi| Avevi wKQz dvskb mg~n RwUj dg~©jv cÖwZ¯’vb K‡i †hgb: =PMT, hv wbw`©ó gybvdvi nv‡i, wbw`©ó mg‡qi Rb¨, †Kvb wbw`©ó cwigvY UvKv wb‡j gvwmK ev evrmwiK wKw¯—‡Z KZ UvKv K‡i Rgv w`‡Z n‡e Zv wbY©q K‡i|
: = dvskb wb‡RB GKwU dg~©jv wnmv‡e A_ev, Ab¨ dvskb ev dg©~jvi mv‡_ hy³ n‡q e¨eüZ nq|
4GKwU dvsk‡bi MVb KvVv‡gv wbgœiƒct
=Function A_ev
=Function (Argument1, Argument2,......... . Arguments)
=Function n‡”Q dvsk‡bi bvg| A‡bK dvskb mg~n GK ev GKvwaK Argument Aš—f©~³ K‡i| G¸‡jv ‡cÖvMÖvg‡K Z_¨ Supply K‡i =dvskb m¤úbœ Kivi Rb¨| cÖ‡Z¨K dvsk‡bi Rb¨ wbR¯^ wmb‡U· (Syntax) i‡q‡Q|
4GKwU dvskb Gw›Uª Kivi mgq wb‡gœi welqvejx j¶Yxqt
1| cÖ‡Z¨K dvskb =(Equal Sign) w`‡q ïi“ K‡Z n‡e|
2| dvskb Ges Av¸©‡g‡›Ui gv‡S †Kvb †¯úm ivLv hv‡e bv|
3| Av¸©‡g›U, eªv‡KU Øviv Ave× Ki‡Z n‡e|
4| GKvwaK Av¸©‡g›U _vK‡j Zv Kgv (,) ev †mwg‡Kvjb (;) w`‡q c„_K K‡i
†`Lv‡Z n‡e|
5| Av¸©‡g›U¸‡jv Value (msL¨v) Ges String (A¶i ev kã) n‡Z cv‡i
†Kvb dvsk‡b Av¸©‡g›U f¨vjy n‡Z Zv mivmwi wj‡L A_ev †mj G‡Wªm
wj‡L A_ev †iÄ Gi bvg wj‡L A_ev Ab¨ dvsk‡bi gva¨‡g wjLv hvq|
6| Av¸©‡g›U Syntax n‡j Dnv‡K Wvej †Kv‡Ukb Øviv Ave× K‡i w`‡Z n‡e|
7| wKQz dvskb i‡q‡Q †h¸‡jv‡Z †Kvb Av¸©‡g›U jv‡M bv| G¸‡jv Gfv‡e †jLv nq| †hgbt =PC(),=NOW(),=RAND() BZ¨vw`|
How to use Functions
mivmwi Kx-‡evW© †_‡K ¯Œx‡Y UvBc K‡i A_ev Insert ‡gby †_‡K dvskb KgvÛ cÖ‡qvM Kiv hvq| Insert †g‡by †_‡K Function w`‡j dvskb DBRvW© e¨envi K‡i †h †Kvb dvskb KgvÛ cÖ‡qvM Kiv hvq| ‡hgbt B2†_‡K B9 ch©š— AvUwU †mj G †jLv msL¨vi †hvMdj †ei Kivi Rb¨ =Sum dvskbwU DBRvW© e¨envi K‡i cÖ‡qvM Kivi Rb¨t
1| †h N‡i djvdj Avbv `iKvi †m N‡i (B10G) †mj c‡q›Uvi ivL‡Z n‡e|
2| Insert ‡gby‡Z wK¬K K‡i Function... G wK¬K Ki‡Z n‡e|3| c`©vq GKwU Paste Function WvqvjM e· Avm‡e
Select a function category in the Function category box below. The function names grouped by category will then appear in the Function name box to the right. If you can't find a specific function, select All in the Function category box, and all available functions will appear in the Function name box.
Click a function name in the Function name box to see the function arguments and a description of the function. Double-click a function name to display the function and its arguments in the Formula Palette. As you create a formula, the Formula Palette will assist you.
4| Function Category Gi Math & Trig G wK¬K Ki“b|
5| Wvb w`‡Ki Function name e‡·i SUM G wK¬K K‡i OK evU‡b wK¬K Ki“b|
6| Avi GKwU SUM WvqvjM e· Avm‡e| †mLv‡b Number 1 Gi N‡i dvskb wj‡L OK Ki“b|
7| OK †Z wK¬K Ki‡j B10 N‡i b nB‡Z b9 ‡mj¸‡jv‡Z wjLv msL¨vi †hvMdj Avm‡e|
Avgiv dvskb Gi gva¨‡g wewfbœ ai‡Yi m~Î cÖ‡qvM K‡i _vwK| wb‡gœ cÖ‡qvRbxq wKQz m~‡Îi cÖ‡qvM †`Lv‡bv nj|
1| †hvM Kivi myÎt =A2+B2+C2 wj‡L G›Uvi|
=SUM(A2:C2) wj‡L G›Uvi|
2| we‡qvM Kivi m~Ît =A2-B2 wj‡L G›Uvi|
3| Mo †ei Kivi m~Ît =AVERAGE(A2:D2) G›Uvi|
4| me‡P‡q eo msL¨v †ei Kivi m~Î =MAX(A2:D2) wj‡L G›Uvi|
5| me‡P‡q †QvU msL¨v †ei Kivi m~Î =MIN(A2:D2) wj‡L G›Uvi|
6| msL¨v MYbv Kivi m~Î: =COUNT (A2:D2) wj‡L G›Uvi|
How to create comment:
hvnvi gš—e¨ Kiv cÖ‡qvRb †mj c‡q›Uvi D³ †m‡j ivLyb|
# Insert >Comment
A | B | C | D | E | |
1 | Name | Age | Sex | Position | Income |
2 | Muaj | 48 | M | Officer | 5000 |
3 | Nayem | 36 | M | Teacher | 8000 |
4 | Maleque | 27 | M | Engineer | 8000 |
5 | Al-Amin | 32 | M | Officer | 2000 |
6 | Fahmida | 36 | F | House wife | 8000 |
7 | Sifat | 38 | M | Officer | 9000 |
*K‡g›U GwWwUs DB‡Ûv Avm‡e| BDRv‡ii bvg (hv wWdë Kiv Av‡Q) Gi wb‡P Kvm©i Av‡Q| GLv‡b hv UvBc Kivi Zv UvBc K‡i w`b|
* gvDm c‡q›Uvi dvuKv ¯’v‡b wK¬K Ki“b|
* †h †mjwU‡Z K‡g›U mwbœ‡ewkZ Kiv n‡q‡Q †m †mj Gi Dc‡ii Wvb †KvYvq GKwU wPý Avm‡e|
* cieZ©x‡Z D³ †mj G gvDm c‡q›Uvi wb‡j K‡g›U wn‡m‡e hv †jLv n‡q‡Q Zv †`Lv hv‡e|
How to delete Comment
# ‡h †mj Gi K‡g›U ev gš—e¨ gyQ‡Z Pvb †m †mj G gvDm wb‡q Wvb
†evZvg Pvcyb| c`©vq wb‡gœi †gbywU Avm‡e- Format Menu........
Format> Cells bv‡g GKwU e· Avm‡e|
‡h †mj¸‡jv Pvwi cv‡k¦© eW©vi w`‡Z Pvb †m¸‡jv wm‡j± Ki“b|
Format> Cells > Border:
Outline/Inside > Ok.Pattern: ‡h †mj¸‡jvi †kW ev c¨vUvY© w`‡Z Pvb †m¸‡jv wm‡j± Ki“b|
Format > Cells > Pattern c`©vq wb‡gœi WvqvjM e· Avm‡e
# GLvb †_‡K Abvqv‡m Kvjvi ev c¨vUvb© ‚ †bIqv hv‡e|Formatting Row
* Format (Alt+O)
* Row ‡Z wK¬K Ki‡j 4wU Ackb mn GKwU mve †gby I‡cb n‡e|
‡hgbt Height, Auto Fit, Hide, Unhide.
Height:
GLv‡b †iv Gi D”PZv evov‡bv ev Kgv‡bv hvq| * Format > Row * Height ‡iv‡Z D”PZv hZ Pvb ZZ
msL¨v Row Height e‡· wj‡L w`b- Ok
Auto Fit: wb‡`©k w`‡q †h †iv‡Z Gi D”PZv cwieZ©b Kiv n‡q‡Q †m †iv
ev †iv¸‡jv wm‡j± Ki“b|
* Format > Row > Auto Fit wbe©vwPZ †iv¸wj
A‡Uv‡gwUK wdU n‡q hv‡e|
Hide: †h mvwi jyKv‡Z Pvb †m mvwii †h †Kvb †m‡j Kvm©i ivLyb|
(GKvwaK mvwi GKev‡i jyKv‡Z PvB‡j mvwi¸‡jv wm‡j± Ki“b|
* Format > Row > Hide wbe©vwPZ mvwi ev mvwi¸‡jv
jywK‡q hv‡e|
Unhide: Hide Kiv †iv Unhide Kivi Rb¨ A_©vr jyKv‡bv mvwi¸‡jv
wd‡i Avbvi Rb¨
* Format > Row > Unhide jyKv‡bv mvwi ev mvwi mg~n wd‡i Avm‡e|
Formatting Column:
Dc‡i DwjwLZ †ivÕi b¨vq|
Sheet: GLv‡b wmU Gi bvg cwieZ©b, wmU jyKv‡bv I e¨vK MÖvDÛ †`Iqv hvq|
Auto Format: ‡h dvBjwU A‡Uv di‡gU Ki‡Z Pvb †m dvBjwU
Open Ki“b| dvB‡ji †h Ask A‡Uv di‡gU Ki‡Z Pvb †m Ask wm‡j± Ki“b|
* Format >Auto Format c`©vq WvqvjM e· Avm‡e|
Avcbvi B”QvgZ †h ai‡Yi †Uwej dig¨vU `iKvi †m bvg wm‡j±
Ki“b| Ok.
How to use Goal seek.
Goal Seek k‡ãi A_© j¶¨ wba©viY| Goal A_© j¶¨ Avi Seek A_© wba©viY| GB AckbwU e¨envi K‡i †Kvb wbw`©ó cwigvY jvf Ki‡Z n‡j wK cwigvY wewµ evov‡Z n‡e ev wbw`©ó kZ©mv‡c‡¶ wKw¯—‡Z KZ UvKv Rgv w`‡q m‡e©v”P KZ UvKv FY †c‡Z cv‡ib BZ¨vw` cÖ‡kœi DËi Rvbv hvq|
: D`vniYt Avcwb evox †Kbvi Rb¨ e¨vsK †_‡K FY †b‡eb| e¨vs‡Ki kZ© nj F‡Yi Dci kZKiv 9 UvKv K‡i my` w`‡Z n‡e Ges 30 erm‡i cÖwZgv‡m wKw¯—‡Z UvKv cwi‡kva Ki‡Z n‡e| wKw¯—‡Z cÖwZgv‡m KZ UvKv K‡i w`‡Z cvi‡eb Zvi Dci wfwË K‡i F‡Yi cwigvY wba©vwiZ n‡e| hw` Avcwb cÖwZgv‡m 5000 UvKv K‡i Rgv w`‡Z cv‡ib Zvn‡j KZ UvKv FY cv‡eb Zv Rvbv cÖ‡qvRb| Goal Seek wb‡`©kwU w`‡q Zv Rvbv hvq| GLv‡b-* my‡`i nvi (Rate)= 9%
* mgq Kvj (Period)=30 ermi
* wKw¯—i cwigvY (cÖwZgv‡m) =5000 UvKv
* UvKvi cwigvb=?
G dvskbwUi Rb¨ wb‡gœi Av¸©‡g›U cÖ‡qvRb|
# =PMT (interest, Periods, Present Value)
D`vni‡Y Avgv‡`i †`qv Av‡Q- Interest= 9%
Periods= 30 (wKš‘ F‡Yi cwigvY (eZ©gvb g~j¨) †`qv †bB|
aivhvK G UvKvi cwigvY AvbygvwbK 1000000 UvKv| (mwVK cwigvY Avgv‡`i †ei Ki‡Z n‡e)| G¸‡jv wb‡q wbgœiƒc IqvK©kxU ˆZix Ki“b|
A | B | C | |
1 | Amount | 1000000 | |
2 | Interest | 9% | |
3 | Periods | 30 | |
4 | |||
5 | Payment | ||
6 |
# ‡mj c‡q›Uvi B4 †m‡j ivLyb-
# =PMT(B2/12,B3*12,B1) UvBc Ki“b|
GLv‡b Interest=B2=9% wKš‘ cªwZgvm wn‡m‡e ZvB Interest=9+12=B2/12 Period=B3=30 ermi wKš‘ wKw¯— cÖwZgv‡m w`‡Z n‡e e‡j Period=30´12=B3*12 |
Enter= G›Uvi Kx Pvcyb
# B5 †m‡j 8046.23 Avm‡e| A_©vr wKw¯—‡Z UvKv Rgv †`qvi cwigvY 8046.23 UvKv| wKš‘ Avcwb w`‡Z cvi‡eb 5000 UvKv| ZvB 5000 UvKvi Rb¨ Loan (Amount) KZ UvKv cvIqv hv‡e Zv Goal Seek dg©~jvi †mj‡K Adjust K‡i Target Value ‡ei Ki‡e|
Goal Seek wb‡`©k
# Tools Æ Goal Seek...
# To Value N‡i 5000 UvBc Ki“b|
# By changing cell: N‡i B1 UvBc Ki“b|
# Ok.
# Goal Seek Status DB‡Ûv Avm‡e-----
A | B | C | |
1 | Amount | 1000000 | |
2 | Interest | 9% | |
3 | Periods | 30 | |
4 | |||
5 | Payment | 5000.00 | |
6 |
# Ok .
> B5 N‡i Payment 8046.23 UvKvi cwie‡Z© 5000 UvKv n‡e Gi Rb¨ (gv‡m 5000 UvKv Rgv †`qvi Rb¨) F‡Yi cwigvY Amount 621409.33 UvKv (B1) Avm‡e| A_©vr Avcwb (9% nvi my‡` 30 ermi †gqv‡`i Rb¨) wKw¯—‡Z cÖwZ gv‡m 5000 UvKv Rgv w`‡Z cvi‡j e¨vsK Avcbv‡K evoxi Rb¨ 8046.23 UvKv FY †`‡e|
Gfv‡e Avcwb Goal Seek wb‡`©kwU e¨envi K‡i m~‡Îi ‡Kvb AvB‡Ug‡K Avcbvi AvB‡Ug Øviv Adjust K‡i Target Value ‡ei Ki‡Z cv‡ib|
How to use Scenario.
wfbœ wfbœ UvKv wfbœ wfbœ my‡`i nv‡i wfbœ wfbœ mg‡qi Rb¨ wKw¯—i cwigvY wK wK n‡e Zv Goal Seek wb‡`©k w`‡q Rvbv hvq bv| Scenario wb‡`©k w`‡q Giƒc wfbœ wfbœ AvB‡U‡gi Rb¨ GKB dg©~jvi wfbœ djvd‡ji Scenario ev `„k¨ weeiYx ‰Zix Kiv hvq|
G dvskbwUi Rb¨ wb‡gœi Av¸©‡g›U cÖ‡qvRb|
# =PMT (interest, Periods, Present Value)
D`vniYt-
aiv hvK, Avcwb e¨vsK †_‡K gvwmK wKw¯—‡Z cwi‡kva Ki‡eb k‡Z© FY wb‡eb| e¨vsK 4wU k‡Z© FY †`q| Avcwb †h †Kvb k‡Z© FY wb‡Z cv‡ib|
kZ© 1: 50000 UvKv cv‡eb 7% my‡`i nv‡i, 6 erm‡ii g‡a¨ †kl Ki‡Z n‡e|
kZ© 2: 90000 UvKv cv‡eb 9% my‡`i nv‡i, 8 erm‡ii g‡a¨ †kl Ki‡Z n‡e|
kZ© 3: 100000 UvKv cv‡eb 5% my‡`i nv‡i, 2 erm‡ii g‡a¨ †kl Ki‡Z n‡e|
kZ© 4: 100000 UvKv cv‡eb 10% my‡`i nv‡i, 10 erm‡ii g‡a¨ †kl Ki‡Z n‡e|
A | B | C | |
1 | Inrerest | 7% | |
2 | Periods | 6 | |
3 | Loan | 50000 | |
4 | |||
5 | Payment |
Scenario ‰Zix Kivt
>> Tols
>> Scenario.
>> Add
8 Scenario name: e‡· Cod1
8 Changing Cells: e‡· B1:B3 wjLyb
8 Ok
e‡· 7%
e‡· 6 wjLyb
e‡· 50000 wjLyb >> Ok
>> Add G wK¬K K‡i µgvš^‡q Gfv‡e cÖ‡Z¨KwU Amount Gi Rb¨ Avjv`vfv‡e Scenario K‡i wb‡Z n‡e|
>> ‡h Amount Gi Scenario Show Ki‡Z Pvb Kvm©i Zvi Dci †i‡L Show †Z wK¬K Ki‡j Zvi Purchase Price Tk. 4,387.14 †`Lv hv‡e| How to Delete a Scenario:
* Tools
* Scenario wjó †_‡K †hwU gyQ‡Z Pvb †mwU wm‡j± Ki“b|
* Delete
How to Audit:
* ‡Kvb †Kvb †mj dg~©jvi mv‡Z m¤úK©hy³, †Kvb †Kvb ‡mj dg©~jvi mv‡_ Dependent dg©~jvq †Kvb Z‡_¨i Rb¨ fyj cÖ`wk©Z n‡”Q BZ¨vw` wbix¶v K‡i wPwýZ K‡i †`Lv‡bv hvq|
* A1 ‡m‡j 200, A3 †m‡j 250, B4 †m‡j 450 wjLyb| * C8 ‡m‡j Kvm©i ivLyb-
* dg©~jv ev‡i =A1+A3+B4 wj‡L G›Uvi Pvcyb|
* C8 N‡i A1,A3 Ges B4 †mj Gi msL¨vi †hvMdj Avm‡e-
* Tools
* Auditing
* Trace Precedents
* bxj i‡½i †Uªmvi Øviv wPwýZ n‡q cÖ`wk©Z n‡e|
How to Remove Auditing:
* C8 ‡m‡j Kvm©i ivLyb|
* Tools
* Auditing
* Remove All Arrows.
Using Data Filter:
GK&‡m‡ji Data Filter wb‡`©k w`‡q †WUv‡eR n‡Z †Kvb wbw`©ó kZ©mv‡c‡¶ †Kvb wbw`©ó †kªYxi †WUv Lyu‡R †ei K‡i Avjv`vfv‡e cÖ`k©b Kiv hvq| GK‡m‡j `yÕfv‡e †WUv Zjvkx ev †WUv wdëvi Kiv hvq:
1| Auto Filter wb‡`©k w`‡q ¯^qswµqfv‡e|
2| Advanced Filter wb‡`©k w`‡q AwaK kZ©v‡ivc K‡i| Auto Filter
ïay cyi“l‡`i †WUv wdëvi Kivi Rb¨ wb‡gœi c`‡¶c wbbt
WvUv‡e‡Ri †h †Kvb †mj G Kvm©i ivLyb|
2. Top 10 ..) m‡e©v”P 10 R‡bi WvUv †`Lv‡e| ...
3. Custom..) kZ©v‡ivc K‡i WvUv †`Lv hvq.... 
> Income wd‡ìi G¨v‡iv 6 evU‡b wK¬K Ki“b|
> Data
> Filter
> Auto Filter
c`©vq cÖ‡Z¨K wdì †b‡gi Wvb cv‡k¦©
Auto Filter Avm‡e-
> Sex wd‡ìi WvDb G¨v‡iv evUb6G wK¬K Ki“b|
> c`©vq GKwU Wªc-WvDb K‡›Uªvj e· I‡cb n‡e-1. (All) me WvUv GK m‡½ †`Lv‡e ...........
2. Top 10 ..) m‡e©v”P 10 R‡bi WvUv †`Lv‡e| ...3. Custom..) kZ©v‡ivc K‡i WvUv †`Lv hvq....4. F gwnjv‡`i WvUv †`Lv‡e.........
5. M cyi“l‡`i WvUv †`Lv‡e........
6. M G wK¬K Ki“b| ïay cyi“l‡`i WvUv cÖ`wk©Z n‡e|
* Custom Auto Filter
hv‡`i BbKvg 40 nvRv‡ii wb‡P Zv‡`i †WUv †`Lvi Rb¨ wb‡gœi c`‡¶c wbb|
> Income wd‡ìi G¨v‡iv 6 evU‡b wK¬K Ki“b| > (Custom) G wK¬K Ki“b|
> c`©vq Avm‡e----- > Kv÷g A‡Uv wdëvi WvqvjM e‡·i cÖ_g Wªc-WvDb G¨v‡iv evUb (Equals Gi cv‡k¦©i 6 evUb) G wK¬K Ki“b|
> Wªc WvDb e‡·i Is less than G wK¬K Ki“b|
> Gi cv‡k¦©i Wªc-WvDb e‡· wK¬K K‡i 40000 †Z wK¬K Ki“b)
> Ok
> c`©vq ïaygvÎ cyi“l Awdmvi hv‡`i †eZb 40000 Kg Zv‡`i †WUv cÖ`wk©Z n‡e|
we`y¨Z wej ˆZixKiY
we`y¨Z weZiY KZ©„c¶ we`y¨r wej avh© Kivi Rb¨ mvaviYZt Zv‡`i wba©vwiZ ixwZ cÖ‡qvM K‡ib| mvaviYZ Zv‡`i cÖewZ©Z bxwZ nj we`y¨r LiP hw` 1 †_‡K 200 BDwbU ch©š— 1.75UvKv| 201 †_‡K 400 BDwbU ch©š— 2.50UvKv| 401 †_‡K 500 BDwbU ch©š— 3.75UvKv Ges Zvi Dc‡i n‡j cÖwZ BDwbU 4.50UvKv K‡i avh© K‡i we`y¨r wej wba©vwiZ Kiv nq| wbgœiƒc IqvK©kxU ˆZix Ki“b|
=IF(C3<=200,C3*1.75,IF(C3<=400,C3*2.5,IF(C3<=500,C3*3.75,C3*4.5))) wj‡L G›Uvi w`b| > D3 †Z 120 BDwb‡Ui we`y¨r LiP Avm‡e|
> D3 Gi Fill handle WªvM K‡i Ab¨vb¨ †mj (D3-D10..)
G wb‡q Ab¨vb¨ e¨enviKvix‡`i we`y¨r wej †ei Kiv hvq|
we`y¨r wej Gi KvR †kl n‡j Dc‡ii ‡UwejwUi gZ n‡e| fvj fv‡e ey‡S, Ki‡Z n‡e|
wet`ªt digyjv ev‡i wjLvi mgq †Kvb cÖKvi †¯úm n‡e bv| dig~jv fyj n‡e †iRvë Avm‡e bv|
gRyix wbY©q
Ifvi UvBg †ei Kivi m~Ît
3| weµq 30000UvKvi Dci Ges 50000UvKvi g‡a¨ n‡j Kwgkb 7%
†R,Gg cvewj‡KkÝ cÖwZ N›Uv wn‡m‡e Kg©Pvix‡`i gRyix cÖ`vb K‡i| cÖwZw`b 8N›Uv ev Zvi †P‡q Kg mg‡qi Rb¨ gyRix 15 UvKv/N›Uv| A_©vr 8 N›Uv KvR Ki‡j cv‡e 15´8=120 UvKv| 8 N›Uvi Kg 6 N›Uv KvR Ki‡j cv‡e 15´6=90 UvKv| 8 N›Uvi AwZwi³ cÖwZ N›Uvi AwZwi³ cÖwZ N›Uvi Rb¨ gyRix 20 UvKv N›Uv| A_©vr †Kn hw` 12 N›Uv KvR K‡i Zvn‡j †m cv‡e 15´8=120, 20´8=80, 12 N›Uv = 120+80=200UvKv| cÖwZôv‡bi Rb¨ GKwU Wage Sheet ‰Zix Ki‡Z n‡e †hLv‡b ïaygvÎ Kg© N›Uv Gw›Uª †`qv gvÎ AfviUvBg I †gvU gRyix †ei n‡e| wbgœiƒc IqvK©kxU ˆZix Ki“b|
Ifvi UvBg †ei Kivi m~Ît> †mj c‡q›Uvi D3 ‡m‡j G‡b wb‡gœi m~ÎwU UvBc K‡i G›Uvi w`b| =IF(C3>8,C3-8,0)
gRyix †ei Kivi m~Ît
>‡mj c‡q›Uvi E3 ‡m‡j G‡b wb‡gœi m~ÎwU UvBc K‡i G›Uvi w`b| =IF(D3>0,D3*20+8*15)
Kwgkb wbY©q
†R Gg cvewj‡KkÝ Zv‡`i weµq cÖwZwbwa‡`i weµ‡qi Dci wfwË K‡i Kwgkb †`‡e| kZ© nj hw`-
1| weµq 10000 UvKvi Kg n‡e Zvn‡j Kwgkb 2.5%
2| weµq 10000UvKv †_‡K 30000UvKv n‡j Kwgkb 5%
3| weµq 30000UvKvi Dci Ges 50000UvKvi g‡a¨ n‡j Kwgkb 7%4| weµq 50000 UvKvi D‡Ø© n‡j Kwgkb 10%
‡mjmg¨vb KZ K‡i Kwgkb cv‡e Zv †ei Kivi Rb¨ wb‡gœi IqvK©kxUwU ˆZix Ki“b|
>>> ‡mj c‡q›Uvi C3 †m‡j G‡b wb‡gœi m~ÎwU UvBc K‡i G›Uvi Pvcyb| =IF(B3<10000,B3*2.5%,IF(AND(B3>=10000,B3<=30000),B3*5%,IF(AND(B3>30000,B3<=50000),B3*7%,IF(B3>50000,B3*10%))))
†eZ‡bi ZvwjKv
>>> wb‡gœi IqvK©kxUwU ˆZix Ki“b- 
*** House Rent †ei Kivi wbqgt †ewmK hw` 6000 UvKvi Dc‡i nq Z‡e 30% nv‡i cv‡e| Avevi hw` †ewmK 4500 UvKvi Dc‡i nq Z‡e 40% nv‡i cv‡e| GBfv‡e µgvš^‡q 45%, 50% nv‡i g~j †eZ‡bi Dci hv‡`i hZUzKz cÖvc¨ †m ZZUzKz evmv fvov cv‡e|
>> †mj c‡q›Uvi D3 †m‡j G‡b wb‡gœi m~ÎwU UvBc K‡i G›Uvi w`b|
=IF(C3>=6000,30%,IF(C3>=4500,40%,IF(C3>=3000,45%,IF(C3>=900,50%))))*C3
** Medical ‡ei Kivi Rb¨ Medical Allowance Basic Gi
10% †mj c‡q›Uvi D3 †m‡j G‡b wb‡gœi m~ÎwU UvBc Ki“b|
>> =C3*10% Press Enter.
>> Provident Fund ‡ei Kivi Rb¨ Provident Fund Basic
Gi 10%
>> †mj c‡q›Uvi F3 †m‡j G‡b wb‡gœi m~ÎwU UvBc Ki“b|
>> =C3*10% Press Enter.
*** Tax †ei Kivi Rb¨t-
* Income Tax- Basic 900 UvKvi bx‡P n‡j 0
* Income Tax- Basic 900 UvKvi D‡×© n‡j 2%
* Income Tax- Basic 3000 UvKvi D‡×© n‡j 4%
* Income Tax- Basic 4500 UvKvi D‡×© n‡j 6%
* Income Tax- Basic 6000 UvKvi D‡×© n‡j 8%
>> †mj c‡q›Uvi G3 †m‡j G‡b wb‡gœi m~ÎwU UvBc Ki“b|
=IF(C3>=6000,8%,IF(C3>=4500,6%,IF(C3>=3000,4%,IF(C3>=900,2%))))*C3 G›Uvi w`b|
* Total †ei Kivi m~Ît * †mj c‡q›Uvi H3 †m‡j G‡b wb‡gœi m~ÎwU UvBc Ki“b|
=C3+D3+E3-(F3+G3)
me¸‡jv n‡q‡M‡j wb‡gœi AvK…wZ aviY Ki‡e|
| Salary-Sheet | |||||||
| Code | Name | Basic | H-rent | Medical | P-fund | Tax | Total |
| 1 | Muaj | 5200 | 2080 | 520 | 520 | 312 | 6968 |
| 2 | Usama | 4500 | 1800 | 450 | 450 | 270 | 6030 |
| 3 | Nayem | 1700 | 850 | 170 | 170 | 34 | 516 |
| 4 | Moriam | 6200 | 1860 | 620 | 620 | 496 | 7564 |
| 5 | Nasir | 1500 | 750 | 150 | 150 | 30 | 2220 |
