Santosh Kumar: Office Suites

Santosh 的个人资料Santosh Kumar照片日志列表更多

工具

帮助

日志

摘要

排列方式：

<< 第一页 < 上一页下一页 > 最后一页 >>

Excel, Shortcuts for working with formula

Opening paste function window: At any point of time SHIFT+F3 can be pressed to get the paste function window. And a desired function can be selected to work with.

Opening formula window: Press CTRL+A, after typing ‘=’ symbol and then the function name, to get the parameter window. For example, press CTRL+A after typing ‘=pmt’

Getting syntax inline: Press CTRL+SHIFT+A, after typing ‘=’ symbol and then the function name, to get the syntax. For example, press CTRL+SHIFT+A after typing =pmt, you will get ‘=pmt(rate,nper,pv,fv,type)’ typed automatically on the cell.

»»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com

2008/9/10 17:45 | 添加评论 | 固定链接 | 写入日志 | Office Suites

Excel, Finding exact date differences

The following example demonstrates how to find the date difference in terms of years, months and days between two dates. Let us think that you already have first three columns and you want the fourth one. Simply put the formula ‘=DATEDIF(B2,C2,"y") & "y " & DATEDIF(B2,C2,"ym") & "m " & DATEDIF(B2,C2,"md") & "d"’ at cell D2 and copy the same up to D44

EMP_NAME        HIRE_DATE      DATE_BY   EXP_ASON
Paolo         27-Aug-1992   9-Sep-2008   16y 0m 13d
Pedro         24-Dec-1990   9-Sep-2008   17y 8m 16d
Victoria      13-Sep-1990   9-Sep-2008   17y 11m 27d
Helen         21-Sep-1989   9-Sep-2008   18y 11m 19d
Lesley        13-Feb-1991   9-Sep-2008   17y 6m 27d
Roland         5-Sep-1991   9-Sep-2008   17y 0m 4d
Rita           9-Oct-1993   9-Sep-2008   14y 11m 0d
Helvetius     19-Mar-1993   9-Sep-2008   15y 5m 21d
Timothy       19-Jun-1988   9-Sep-2008   20y 2m 21d
Sven           5-Apr-1991   9-Sep-2008   17y 5m 4d
Miguel         7-Dec-1992   9-Sep-2008   15y 9m 2d
Paula         19-Jan-1994   9-Sep-2008   14y 7m 21d
Manuel         9-Jan-1989   9-Sep-2008   19y 8m 0d
Howard        19-Nov-1988   9-Sep-2008   19y 9m 21d
Martin        13-Apr-1990   9-Sep-2008   18y 4m 27d
Gary           9-Aug-1988   9-Sep-2008   20y 1m 0d
Daniel         1-Jan-1990   9-Sep-2008   18y 8m 8d

»»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com

2008/9/9 17:43 | 添加评论 | 固定链接 | 写入日志 | Office Suites

Excel, Automating a Trial Balance

There are innumerous possibilities of automation with Microsoft Excel. In the following example, you will find how to automate a Trial Balance out of day-to-day cash transactions. Cell B3 to B9 is fixed for Account heads. And the transaction begins at row number 13. So it will look like this:

.............[ Trial Balance ].............
S# Particulars    Balance   Dr. Amt Cr. Amt
1 Bank             10000     14000    4000
2 Stationary        -550         0     550
3 Ramkumar         -1000       500    1500
4 Interest          -500         0     500
5 Rent             -1000         0    1000
6 Electricity       -250         0     250
7 Water             -200         0     200

.............[ Cash Account ]..............
S# Particulars       Date   Dr. Amt Cr. Amt
1 Bank        01-04-2008      8000
2 Stationary 02-04-2008               200
3 Ramkumar    03-04-2008       500
4 Stationary 04-04-2008               150
5 Bank        05-04-2008      3000
6 Bank        06-04-2008      2000
7 Ramkumar    07-04-2008              1500
8 Bank        08-04-2008              4000
9 Stationary 09-04-2008               200
10 Interest    10-04-2008               500
11 Bank        11-04-2008      1000
12 Rent        12-04-2008              1000
13 Electricity 13-04-2008               250
14 Water       14-04-2008               200

How to make it happen

Step 1: Put a drop down box at B13
Select cell B13. Go to Data -> Validation menu. Under the Setting tab, select List at box ‘Allow’ and under ‘source box’ type ‘=$B$3:$B$9’. Now copy cell B13 up to last row.

Step 2: Put a formula at cell D3 and E3
Now at cell D3 put a formula ‘=SUMIF($B$13:$D$65536,B3,$D$13:$D$65536)’ and copy it till D9. And at E3 put ‘=SUMIF($B$13:$D$65536,B3,$E$13:$E$65536)’ and copy it till E9

Step 3: Formulate for Balance
At cell C3 a simple formula for deduction can be placed. And that is ‘=D3-E3’. This also requires to copy up to C9

Step 4: Record transactions
Right from row 13 you record your transaction. Step 1 will validate for a valid account head but for D and E columns you have to validate for numeric data.

»»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com

2008/9/9 13:44 | 添加评论 | 固定链接 | 写入日志 | Office Suites

Excel, Searching tips

Microsoft Office, other office suits may also be, is bounded with a bunch of exciting features; that can make almost of official work happen within itself.

Imagine a requirement where you are supposed to find those records, which contains a particular word anywhere in any field. These requirement may exit where address like of field are concerned e.g. M.G. word can be a road, street, area, district or anything else. Think of the following table, oracle gives it by default as SCOTT.EMP; I have modified a bit.

S# EMP# NAME   JOB       HIREDATE SAL DEPTNO
1 7730 CLARK MANAGER   09-Jun-81 2450 10
2 7369 SMITH CLERK     17-Dec-80 800 20
3 7499 ALLEN SALESMAN 20-Feb-81 1600 30
4 7521 WARD   SALESMAN 22-Feb-81 1250 30
5 7566 JONES MANAGER   02-Apr-81 2975 20
6 7654 MARTIN SALESMAN 28-Sep-81 1250 30
7 7698 BLAKE MANAGER   01-May-81 2850 30
8 7788 SCOTT ANALYST   19-Apr-87 3000 20
9 7839 KING   PRESIDENT 17-Nov-81 5000 10
10 7844 TURNER SALESMAN 08-Sep-81 1500 30
11 7876 ADAMS CLERK     23-May-87 1100 20
12 7900 JAMES CLERK     03-Dec-81 950 30
13 7902 FORD   ANALYST   03-Dec-81 3000 20
14 7934 MILLER CLERK     23-Jan-82 1300 10

Now, I want to search those records that contain 30 in it, anywhere. And they are rec#1(emp#), rec#3(deptno), rec#4(deptno), rec#6(deptno), rec#7(deptno), rec#8(sal), rec#10(deptno), rec#12(deptno), rec#13(sal), rec#14(sal).

Now add a field FOUND to the right of DEPTNO and in cell H2, put a formula i.e. =IF(ISERROR(SEARCH("30",C3&D3&E3&F3&G3&H3)),"N","Y"). Using autofill feature copy the formula to rest of the cells in range H3 to H15. It now look like this.

S# EMP# NAME   JOB       HIREDATE SAL DEPTNO FOUND
1 7730 CLARK MANAGER   09-Jun-81 2450 10     Y
2 7369 SMITH CLERK     17-Dec-80 800 20     N
3 7499 ALLEN SALESMAN 20-Feb-81 1600 30     Y
4 7521 WARD   SALESMAN 22-Feb-81 1250 30     Y
5 7566 JONES MANAGER   02-Apr-81 2975 20     N
6 7654 MARTIN SALESMAN 28-Sep-81 1250 30     Y
7 7698 BLAKE MANAGER   01-May-81 2850 30     Y
8 7788 SCOTT ANALYST   19-Apr-87 3000 20     Y
9 7839 KING   PRESIDENT 17-Nov-81 5000 10     N
10 7844 TURNER SALESMAN 08-Sep-81 1500 30     Y
11 7876 ADAMS CLERK     23-May-87 1100 20     N
12 7900 JAMES CLERK     03-Dec-81 950 30     Y
13 7902 FORD   ANALYST   03-Dec-81 3000 20     Y
14 7934 MILLER CLERK     23-Jan-82 1300 10     Y

Now it is up to you. If want to use autofilter, go ahead! By using the autofilter option you can filter for FOUND? field where it is Y. That’s all.

»»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com

2008/9/7 11:06 | 添加评论 | 固定链接 | 写入日志 | Office Suites

Excel, The best keyboard shortcuts

Select the current column	CTRL+SPACEBAR
Select the current row	SHIFT+SPACEBAR
Paste a function into a formula	SHIFT+F3
When you enter a formula, display the Formula Palette after you type a function name	CTRL+A
Alternate between displaying cell values and displaying cell formulas	CTRL+` (left quote)
Enter the date	CTRL+; (semicolon)
Enter the time	CTRL+: (colon)
Fill the selected cell range with the current entry	CTRL+ENTER
Display the Go To dialog box	F5
Display the Format Cells dialog box	CTRL+1
This key combination selects the current range around the active cell, the area defined as the largest rectangle of data surrounded by white space	CTRL+SHIFT+* (asterisk)
Insert the AutoSum formula	ALT+= (equal sign)

»»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com

2007/10/22 14:13 | 添加评论 | 固定链接 | 写入日志 | Office Suites