88. Numbering Bar nvwo‡q †M‡j wdwi‡q Avbvi wbqg t
Ø hw` †Kvb Kvi‡b Numbering Bar (1, 2, 3, 4 & A, B, C, D) nvwo‡q hvq Zv wdwi‡q Avb‡Z n‡j cÖ_‡g Tools-G Click Ki‡j Tools Menu-Gi Sub-Menu ‡`Lv hv‡e, Gevi Option-G Click Ki‡j c`©vq Option Dialog Box Avm‡e, GLv‡b View-†Z Click Ki‡j View-Gi Chart ‡`Lv hv‡e Gevi Row & Column heders-Gi mvg‡b wUK (ü) wPý w`‡q Ok-‡Z Click Ki‡j Work Seet-Gi Dc‡i Ges ev‡g †h Numbering Kiv BarwU Av‡Q Zv ‡`Lv hv‡e|
89. Selling Sheet ‰Zix Kivi wbqg t
A | B | C | D | E | F | G | H | I |
Sl. | Item | Jun | Feb | Mar | Total | Avg | Max | Min |
01. | Book | 2000 | 7000 | 8000 | =Sum(C1:E1) & 8 | @Average(C1:E1) & 8 | =Max(C1:E1) & 8 | =Min(C1:E1) & 8 |
02. | Pen | 4000 | 4000 | 5000 | ||||
03. | Disk | 6000 | 6500 | 7000 |
9O. Stock Ledger ‰Zix Kivi wbqg t
A | B | C | D | E | F |
Sl. | Item | Op-Stock | Delivery | Purchase | Total |
01. | Book | 5000 | 4000 | 4000 | + C1 - D1 + E1 & 8 |
02. | Pen | 3500 | 3000 | 3000 | |
03. | Disk | 9000 | 7500 | 7500 |
91. Inventory Sheet ‰Zix Kivi wbqg t
A | B | C | D | E | F | G |
Sl. | Item | Qty | U-Price | Total | Damage | G-Total |
01. | Computer | 250 | 4000 | + C1*D1 & 8 | O5 | =(C1-F1)*D1 & 8 |
02. | Printer | 300 | 15000 | O6 | ||
03. | Disk | 1500 | 500 | O8 |
92. Result Sheet Kivi wbqg t
A | B | C | D | E | F | G | H | J | K | L | M | N |
Sl. | B1 | B2 | E1 | E2 | M1 | Bio1 | Total | Avg | Max | Min | GPA | Gread |
01. | 40 | 42 | 50 | 75 | 80 | 55 | =Sum (B1 : M1) & 8 | @Average (B1 : M1) & 8 | =Max (B1 : M1) & 8 | =Min (B1 : M1) & 8 | | |
02. | 44 | 44 | 55 | 77 | 82 | 54 | ||||||
03. | 46 | 43 | 56 | 78 | 85 | 53 | ||||||
04. | 50 | 47 | 65 | 70 | 86 | 52 |
Formla :
Cells Pointer ‡K Result Gi N‡i ivL‡Z n‡e|
GPA : =If(H1>=75O,”Gpa5”,@If(H1>=6OO,”Gpa4”,@If(H1>=5OO,” Gpa3.5O”,@If(H1>=4OO,”Gpa3”,If(H1>=333,”Gpa2”,”Fail”))))) & 8
Gread : =If(H1>=80,”A+”,@If(H1>=70,”A”, @If(H1>=60,”A-” ,@If(H1>=50,”B”, @If(H1>=40,”C”, @If(H1>=33,”D”,@If(H1<=33,”F”))))) & 8
93. Electric Bill ‰Zix Kivi wbqg t
A | B | C | D |
Meter No. | Name | Unit | Amount |
055356556 | Asraf | 120 | |
96358885 | Rahim | 75 | |
822666 | Mahbub | 210 | |
745826 | Jamal | 406 | |
85555566 | Masum | 550 | |
568998666 | Rubal | 700 | |
Formla :
Cells Pointer ‡K Amount Gi N‡i ivL‡Z n‡e|
Amount : =If(C2<=200,C2*1.75,IF(C2<=400,C2*2.50,IF(C2<=500,C2*3.75,C2*4355))) & 8
94. Salary Sheet ‰Zix Kivi wbqg t
A | B | C | D | E | F | G | H | I | J | K |
Sl. | Name | Post | Basic | H/Rent | M/A | Salary | P/Found | | Nsal | Grade |
01. | Haider | MD | 8000 | 15% | 20% | ? | 10% | | | ? |
02. | Masud | GM | 7000 | 13% | 15% | ? | 9% | | | ? |
03. | Abu | ENG | 5000 | 10% | 12% | ? | 8% | | | ? |
Formla :
H/Rent : @If(C1=”MD”,D1*15%,@If(C1=”GM”,D1*13%,@If(C1=”ENG”,D1*10%))) & 8
M/A : @If(C1=”MD”,D1*20%,@if(C1=”GM”,D1*15%,@If(C1=”ENG”,D1*12%))) & 8
SALARY : =Sum (D2:F2) & 8
P/Found : @If(C1=”MD”,D1*10%,@If(C1=”GM”,D1*9%,@If(C1=”ENG”,D1*8%))) & 8
Tex : =If((D1<2000,0,If(and(D1>=2000,D1<=5000),D1*5%,If(D1>5000.D1*10%)))& 8
Nsal : =F1+G1 & 8 / =D1-(H1+I1) & 8
Grade : @If(G1=7000,”A”,@If(G1=6000”B”,@If(G1=4000,”C”,@If(G1=3000,”D”,”E”))))& 8
95. Garments Factory’s Production Worker’s Salary Sheet ‰Zix Kivi wbqg t
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S |
Sl. | Name | Post | Card No | Ttt Day | W D | Basic | H/ Rent | M / A | Total | Produ Salary | ATT Bonus | Produ Bonus | TotalBasic | A/C Pay | Adv | Stamp | Net Pay | Signature |
01. | Haider | OP | 01 | 31 | 31 | 1038 | | 150 | 1500 | | 250 | | | | | | | |
02. | Masud | OP | 02 | 31 | 31 | 1038 | | 150 | 1500 | | 250 | | | | | | | |
03. | Abu | OP | 03 | 31 | 31 | 1038 | | 150 | 1500 | | 0 | | | | | | | |
Formla :
Basic : = (J1-I1)/1.3
H/Rent : = G1*30%
Att Bonus : =IF(E7=F7,”250”,”0”)
96. Garments Factory’s Fised Worker’s Salary Sheet ‰Zix Kivi wbqg t
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
Sl. | Name | Post | Card No | Ttt Day | W D | Basic | H/ Rent | M / A | Total | ATT Bonus | A/C Pay | Adv | Stamp | Net Pay | Signature |
01. | Haider | OP | 01 | 31 | 31 | 1038 | | 150 | 1500 | 250 | | | | | |
02. | Masud | OP | 02 | 31 | 31 | 1038 | | 150 | 1500 | 250 | | | | | |
03. | Abu | OP | 03 | 31 | 31 | 1038 | | 150 | 1500 | 0 | | | | | |
Formla :
Basic : = (J1-I1)/1.3
H/Rent : = G1*30%
Att Bonus : =IF(E7=F7,”250”,”0”)
97. Overtime mn Salary Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwbi cÖwZ N›Uv wnmv‡ei Kg©Pvwi‡`i gRywi cÖ`vb K‡i| cÖwZw`b 8 N›Uv ev Zvi †P‡q Kg mg‡qi gRywi cÖwZ N›Uvq 60 UvKv| 8 N›Uvi AwZwi³ cÖwZ N›Uvi gRywi 20 UvKv A_©vr †KD hw` 12 N›Uv KvR K‡i Zvn‡j †m cv‡e- 60*8 = 480, 20*4 = 80, GLb 12 N›Uv = 480 + 80 = 560 UvKv|
A | B | C | D | E |
Code No | Name | Working Hour | Overtime | Payment |
01. | Haider | 12 | 4 | |
02. | Masud | 12 | 4 | |
03. | Abu | 10 | 2 | |
04. | Mahabub | 11 | 3 | |
05. | Jamal | 14 | 6 | |
Formla :
Over Time : =C1-8 & 8
Payment : =If(D1>3,D2*20+8*15,C1*15) & 8
98. Commission Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwb Zv‡`i cÖwZwbwa‡`i weµ‡qi Dci wfwË K‡i Kwgkb †`‡e|
weµq 10,000 UvKvi Kg n‡j Kwgkb 2.5%,
weµq 10,000 UvKv †_‡K 30,000 UvKv n‡j Kwgkb 5%,
weµq 30,000 UvKvi Dc‡i Ges 50,000 UvKvi g‡a¨ n‡j Kwgkb 7%,
weµq 50,000 UvKvi Dc‡i n‡j Kwgkb 10% nv‡i †`Iqv n‡e|
A | B | C | D |
Sl. No. | Sales in Person | Sales | Commission |
01. | Rani | 25000 | |
02. | Rukan | 23000 | |
03. | Rubal | 9000 | |
04. | Ruma | 51000 | |
05. | Khakon | 13500 | |
Formla :
Commision : If(C1<10000,C1*2.5%,If(and(C1>=10000,C1<30000),C1*5%,If(and(C1>30000,C1<=50000),C1*7%,If(C1>50000,C1*10%)))) & 8
99. Commission mn Salary Sheet ‰Zix Kivi wbqg t
†Kvb K¤úvwb Zv‡`i cÖwZwbwa‡`i weµ‡qi Dci wfwË K‡i Kwgkb †`‡e|
weµq 10,000 UvKvi Kg n‡j Kwgkb 2.5%,
weµq 10,000 UvKv †_‡K 30,000 UvKv n‡j Kwgkb 5%,
weµq 30,000 UvKvi Dc‡i Ges 50,000 UvKvi g‡a¨ n‡j Kwgkb 7%,
weµq 50,000 UvKvi Dc‡i n‡j Kwgkb 10% nv‡i †`Iqv n‡e|
A | B | C | D | E |
Sl. No. | Sales in Person | Sales | Commission | Total |
01. | Rani | 5000 | 900 | |
02. | Rukan | 4500 | 800 | |
03. | Rubal | 4000 | 700 | |
04. | Ruma | 3700 | 600 | |
Formla :
Commision : = C1+D1*10% & 8
1OO. AvqKi wnmve Kivi wbqg t
†Kvb e¨w³i, gvwmK Avq hw` 10,000 UvKv nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 2%,
gvwmK Avq hw` 15,000 UvKvi g‡a¨ nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 3%,
gvwmK Avq hw` 20,000 UvKv nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 5%,
gvwmK Avq hw` 20,000 UvKvi Dc‡i nq Zvn‡j Zvi BbKvg †U¯‹ n‡e 8%,
A | B | C | D |
Sl. No. | Name | Monthly Incom | Income Tax |
01. | Rukan | 25,000 | |
02. | Rubal | 22,300 | |
03. | Ruma | 20,000 | |
04. | Khakon | 15,000 | |
05. | Bulbul | 13,000 | |
Formla :
Commision : If(C1=10000,C1*2%,If(C1<=15000,C1*3%,If(C1<=20000,C1*5%,If(C1>20000,C1*8%)))) & 8
1O1. my‡`i wnmve †ei Kivi wbqg t
†Kvb e¨w³i, FY hw` 100,000 UvKvi g‡a¨ n‡j 5%,
FY hw` 300,000 UvKvi g‡a¨ n‡j 7%,
FY hw` 500,000 UvKvi g‡a¨ n‡j 9%,
FY hw` 1,000,000 UvKvi g‡a¨ n‡j 10%,
FY hw` 1,000,000 UvKvi g‡a¨ n‡j 15%,
A | B | C | D |
Sl. No. | Name | Loan Amount | Interest |
01. | Rukan | 25,000 | |
02. | Rubal | 22,300 | |
03. | Ruma | 20,000 | |
04. | Khakon | 15,000 | |
05. | Bulbul | 13,000 | |
Formla :
Commision : If(C1<=10000,C1*5%,If(C1<=30000,C1*7%,If(C1<=500000,C1*9%,If(C1<=1000000,C1*10%If(C1>1000000,C1*15%))))) & 8
1O2. Auto Filter Kivi wbqg t
Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Filter-G Click Ki‡j c`©vq Filter-Gi kvLv Menu Avm‡e, Gevi Auto Filter-G Click Ki‡jB Auto Filter-Gi mvg‡b GKwU wUK (ü) wPý em‡e|
1O3. Auto Filter cªZ¨vnvi Kivi wbqg t
Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Filter-G Click Ki‡j c`©vq Filter-Gi kvLv Menu Avm‡e, Gevi Auto Filter-G Click Ki‡jB Auto Filter-Gi mvg‡b GKwU wUK (ü) wPý DwV‡q w`‡Z n‡e|
1O4. Sorting Gi gva¨‡g Ascending/ Descending Kivi wbqg t
Ø Data-G Click Ki‡j Data Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Sort-G Click Ki‡j c`©vq Sort Dialog Box Avm‡e, GLv‡b hw` Ascending Ki‡Z PvB Zvn‡j Ascending-G Click Ki‡Z n‡e Avi hw` Descending Ki‡Z PvB Zvn‡j Descending -G Click K‡i Ok-‡Z Click Ki‡Z n‡e|
1O5. wbwÏ©ó Cells-G Date hy³ Kivi wbqg t
Ø †h Cells-G Date hy³ Kivi `iKvi cª_‡g †m Cells-G Cursor †i‡L =Today ( ) Type K‡i Enter [ 8 ] Pvc‡Z n‡e| hw` †Kvb Box Av‡m Zvn‡j YES-G Click Ki‡j Cursor Gi ¯’v‡b AvR‡Ki ZvwiL hy³ n‡e|
1O6. wbwÏ©ó Cells-G Date & Time hy³ Kivi wbqgt
Ø †h Cells-G Date & Time hy³ Kivi `iKvi cª_‡g †m Cells-G Cursor †i‡L = Now ( ) Type K‡i Enter [ 8 ] Pvc‡Z n‡e| hw` †Kvb Box Av‡m Zvn‡j YES-G Click Ki‡j Cursor Gi ¯’v‡b AvR‡Ki ZvwiL I mgq hy³ n‡e|
1O7. Work Sheet Hide Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Freeze Kivi `iKvi, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Hide-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze n‡e|
1O8. Work Sheet Unhide (cÖZ¨vnvi) Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Unhide-G Click Ki‡j c`©vq Unhide Dialog Box Avm‡e GLv‡b †hUv‡K Unhide Kiv n‡q‡Q ‡mUv‡K Select Kivi ci Ok-†Z Click Ki‡Z n‡e|
1O9. Split (‡gvUv `vM) Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Split Kivi `iKvi, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Split-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Split n‡e|
11O. Remove Split (‡gvUv `vM) cÖZ¨vnvi Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Split Kiv Av‡Q, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Remoce Split-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Split Kiv _vK‡j Zv Open n‡e|
111. Freeze Panes (wPKb `vM) Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Freeze Kivi `iKvi, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Freeze Panes-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze n‡e|
112. Unfreeze Panes (wPKb `vM) cÖZ¨vnvi Kivi wbqg t
Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor †m ¯’v‡b ‡i‡L Window-‡Z Click Ki‡Z n‡e Zvn‡j c`©vq Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi Unfreeze Panes-G Click Ki‡jB Document-Gi wbwÏó ¯’v‡b Freeze Kiv _vK‡j Zv Unfreeze n‡e|
113. New Window Avbvi wbqg t
Ø Document-Gi ‡h ¯’v‡b Freeze Kiv Av‡Q, Cursor cÖ_‡g †m ¯’v‡b ivL‡Z n‡e Zvici Window-G Click Ki‡j Window Menu-Gi Sub Menu ‡`Lv hv‡e, Gevi New Window-G Click Ki‡jB New Window Avm‡e|
ba------XX------ba
G‡·‡ji †gvU Row-Gi msL¨v t 256
†gvU Column-Gi msL¨v t 65536
m~Îvejx :
‡hvM Kivi wbqg (GK mvwii me¸‡jv) : = Sum (C2 : J2) & 8
‡hvM Kivi wbqg (wbÏ©ó mvwi ¸‡jv) : = Sum (C2 + E2 + G2 + I2) & 8
we‡qvM Kivi wbqg : Sum (C2 - D2) & 8
¸b Kivi wbqg : + C2*D2 & 8
fvM Kivi wbqg : = C2 / D2 & 8
% Kivi wbqg (‡ekx) : = C2*10% + C2 & 8 / =C2*0.01% + C2 & 8
% Kivi wbqg (Kg) : = C2*10% - C2 & 8 / =C2*0.01% - C2 & 8
Maximun Kivi wbqg : = Max (C2 : J2) & 8
Minimun Kivi wbqg : = Min (C2 : J2) & 8
Average Kivi wbqg : @ Average (C2:J2) & 8 / =Average (C2/3) & 8
Short Cut Command
1. CTRL + A = All Select
2. CTRL + B / 2 = Bold
3. CTRL + I / 3 = Italic
4. CTRL + U / 4 = Underline
5. CTRL + 5 = Text the Strikethough
6. CTRL + SHIFT + 1 = Only for Nuemaric
7. CTRL + SHIFT + 2 / CTRL + SHIFT + ; = Only for Time
8. CTRL + SHIFT + 3 / CTRL + : = Only for Date
9. CTRL + SHIFT + 4 = Only for Dollar ($) Icon
1O. CTRL + SHIFT + 5 = Only for Persent (%) Icon
11. CTRL + SHIFT + F = Select the Font Box
12. CTRL + SHIFT + P = Select the Font Size Box
13. CTRL + SHIFT + ~ = Normal Format
14. CTRL + SHIFT + ! = Comma Format
15. CTRL + SHIFT + @ = Time Format
16. CTRL + SHIFT + # = Date Format
17. CTRL + SHIFT + $ = Taka Format
18. CTRL + SHIFT + % = Parsent Format
19. CTRL + SHIFT + _ = No Border
2O. CTRL + N = New Document.
21. CTRL + S / SHIFT + F12 = Save Box
22. CTRL + O = Open Box
23. CTRL + K = Insert Hyperlink Box
24. CTRL + X = Cut
25. CTRL + Z = Undo
26. CTRL + C = Copy
27. CTRL + N = New Document / New Book Sheet
28. CTRL + V = Paste
29. CTRL + P = Print Box
3O. CTRL + 1 = Font Box
31. CTRL + 9 = Hide the Row
32. CTRL + O = Hide the Column
33. CTRL + F5 = Small Size the Wark Sheet
34. CTRL + G = Go To Box
35. CTRL + F / SHIFT + F5 = Find Box
36. CTRL + H / SHIFT + F5 = Replace Box
37. CTRL + ALT + B = Change the Key Board.
38. CTRL + W = Book Close.
39. CTRL + + = Sate Up the Any
4O. CTRL + - = Column Delete
41. SHIFT + F3 = Formula Box.
42. F7 = Spelling & Grammar.
43. F10 = Select the Menubar
44. ALT + F4 = Program Close.
45. ALT + = = Formula (+)
46. SHIFT + _ = Delete the Row / Column by Text.
47. SHIFT + + = Increase the Row / Column by Text.
48. SHIFT + F11 = Increase Sheet by Text.
‡gv t Rv‡n`ybœex Ry‡qj
0 comments:
Post a Comment