Belajar spreadsheet yuuk..! Excel makin asyik..

Test blog posting from Ms Word 2007

2011-04-23T08:48:00.001+07:00

Many spreadsheet users will know that when you need to pick out a value from a list, it's a good idea to use a lookup formula. There are numerous ways to do this, but which is the best?

Each formula or method has its advantages and disadvantages depending on the circumstances. It's hard to cover all of these circumstances, so we'll focus on one specific, yet common, situation.

Let's say you have a unique list of items, say "fruit". Each type of fruit has a numerical value associated with it — perhaps the quantity or price of the item. Somewhere else in your spreadsheet you want to enter the fruit item you're interested in and have a formula return the specific value for this fruit item. Let's further assume that the items are not sorted in any particular order.

In this table, we want to enter the name of the fruit in cell B10, and we want the value in cell C10 to automatically pick out the correct quantity in the source data.

So, now that we've set the scene, let's look at 7 different ways to do this in Excel.

Method 1 – SUMIF

C11: =SUMIF(B4:B7,B11,C4:C7)

SUMIF is a very useful way to do conditional sums. It lets you specify the range of items (i.e. the list of fruit names), the criteria (i.e. the particular fruit you want to find), and the result range (i.e. the quantities associated with the items). It does not require the list to be sorted in any order, but for this to work as a lookup each item in the list needs to be unique. Furthermore, it will only work for numerical values in the result range. If you wanted to return say the colour of the fruit, SUMIF would not work.

Method 2 – INDEX and MATCH

D11: =INDEX(D4:D7,MATCH(B11,B4:B7,FALSE))

INDEX and MATCH is probably the method I use most often to perform exact lookups. For some reason it just makes the most sense to me, and has clear advantages over VLOOKUP and HLOOKUP. If the items in the list are not unique then it returns the associated value for the first one it finds. Although this indicates a problem with your source data, this seems better to me than SUMIF which would effectively aggregate all the items it finds. Furthermore, INDEX and MATCH works with non-numeric data, so would work fine in the situation discussed above regarding picking a colour out of the list. Finally, the ranges do not have to be contiguous, and there is a more advanced version of INDEX and MATCH which can match criteria in two dimensions i.e. rows and columns.

Method 3 – VLOOKUP

E11: =VLOOKUP(B11,B4:E7,4,FALSE)

This is similar to the INDEX and MATCH approach but with one significant disadvantage — it requires you to specify the column number of the data you want returned (in this case the 4th column of the range). It is possible to calculate this column number dynamically, but this is hard, opaque and error-prone. Furthermore, the formula requires that the left-most column of the range contains the items you're searching. This is unnecessarily restrictive and is not required by the INDEX and MATCH approach.

Method 4 – DGET

F11: =DGET(B3:F7,F3,B10:B11)

This is a neat method and probably should get more use. By all accounts it is a computationally efficient method, and when working with arrays it allows for flexible criteria and boolean logic e.g. "apples" OR "bananas" (e.g. DSUM, DCOUNT, etc.). One drawback is that it requires a criteria range to be set up in a particular way (B10:B11 in the formula) and this may not suit your worksheet layout and may break later if you insert rows in the wrong place. In general, I would avoid this approach and still go with INDEX and MATCH.

Method 5 – Array IF

G11: {=SUM(IF(B4:B7=$B$11,G4:G7))}

Arrays are incredibly powerful and intuitive when you really get them. Sometimes thinking about them can cause your brain to leak out of your ear though, so be careful. In this case, using an array IF is no better than SUMIF and has the disadvantage of being more advanced – so less skilled users of your model may be confused. Also arrays can only be entered by holding down Ctrl and Shft at the same time when hitting Enter. Again, this is not something less skilled users will know about — not that they should be messing with your formulas anyway.

Method 6 – Range Names

H11: =INDIRECT(B11)

This assumes that you've created range names for your source data. You can do this easily / automatically by selecting your source data (B4:I7) and hitting Ctrl Shft F3, then choose the Left column option and hit Enter. This will create range names out of all item names. The INDIRECT formula is the way to get Excel to use the input value you type in B11 as if you'd written =Bananas in cell H11. This method is bad design! The main problem is that you now need to maintain and ensure the integrity of a whole lot of range names. If you later change the item Bananas in your source list to be Kiwi Fruit, you'll need to delete your Bananas range name and add one for Kiwi Fruit. There are so many things that can/will go wrong with this method, and we generally recommend not using range names at all in your models.

Method 7 – PivotTables

I11: =GETPIVOTDATA("PivotTable",B13,"FRUIT",B11)

PivotTables are very useful for analysing data set up in a database format. They can certainly be used to lookup data as we're doing here, but they require you to create a PivotTable and ensure that it is being refreshed when your source data changes. This does not happen automatically like normal spreadsheet functions. It also requires you to use and understand the GETPIVOTDATA formula, which can be a bit fiddly. In this case, using a PivotTable approach is a bit like using a sledge-hammer to kill an ant, so it's not our preferred approach.

Conclusion

So, while none of these methods are foolproof, the winner is clearly method 2 – INDEX and MATCH. To further improve your exact lookups, you should 1) check your source items are unique, 2) use data validation to ensure the result item is from this list, 3) deal with potential errors with IFERROR or ISERROR. Finally, although not done in the above examples, remember to get your $ signs in the right places in case you need to copy and paste this formula to other cells.

Sources: http://www.sumwise.com/blog/excel-lookup-formulas/

Google Search Advanced Operators

2011-01-13T06:07:00.003+07:00

Berikut ada panduan singkat tentang bagaimana menggunakan mesin pencari dari Google. Klo kita menguasainya maka kita akan bisa mencari informasi yang kita butuhkan dari internet lebih akurat dan cepat. Langsung aj silakan dipelajari ...

biking Italy	the words biking and Italy
recycle steel OR iron	information on recycling steel or recycling iron
"I have a dream"	the exact phrase I have a dream
salsa –dance	the word salsa but NOT the word dance
Louis +I France	information about Louis the First (I), weeding out other kings of France
castle ~glossary	glossaries about castles, as well as dictonaries, lists of terms, terminology, etc.
fortune-telling	all forms of the term, whether spelled as a single word, a phrase, or hyphenated
define:imbroglio	definitions of the word imbroglio from the Web

Calculator Meaning Type Into Search Box

+ – * / basic arithmetic 12 + 34 - 56 * 7 / 8

% of percentage of 45% of 39

^ or ** raise to a power 2^5 or 2**5

old units in new units --> convert units 300 Euros in USD, 130 lbs in kg, or 31 in hex

selanjutnya klik di sini

Kalkulasi Bunga Majemuk (Compound Interesr) di Ms Excel

2010-08-07T16:15:00.003+07:00

	A	B	C
1	Perhitungan Bunga Majemuk (Compound Interest)
2	*Single formula general solution*
3			Rumus di kolom B
4	Nilai Investasi	100,000,000	-
5	Tingkat bunga per tahun	14.00%	-
6	Compounding periods/tahun	365	-
7	Termin (dalam tahun)	3	-
8
9	Periodic interest rate:	0.04%	=B5*(1/B6)
10	Nilai investasi pada akhir termin	152,183,900	=FV(B9,B6*B7,,-B4)
11	Total bunga yang diperoleh	52,183,900	=B10-B4
12
13	Annual yield:	17.39%	=(B11/B4)/B7
14

	A	B	C
1	Continuous Compounding
2
3
4	Nilai Investasi	100,000,000
5	Annual interest rate:	14.00%
6	Compounding	Continuous	--> terus menerus
7	Term (years)	3
8			Rumus
9	Nilai investasi pada akhir termin	152,196,156	=B4EXP(B5B7)
10
11
12	Jumlah Tahun untuk membuat investasi jadi double
13
14	Interest Rate	Rule of 72	Actual
15	1%	72.00	69.66
16	2%	36.00	35.00
17	3%	24.00	23.45
18	4%	18.00	17.67
19	5%	14.40	14.21
20	6%	12.00	11.90
21	7%	10.29	10.24
22	8%	9.00	9.01
23	9%	8.00	8.04
24	10%	7.20	7.27
25	15%	4.80	4.96
26	20%	3.60	3.80
27	25%	2.88	3.11
28	30%	2.40	2.64
29

Tabel Loan Payment Satu Variabel

2010-08-07T09:38:00.011+07:00

1-way

	A	B	C	D	E	F	G	H	I
1	Jumlah Pinjaman	100,000,000
2	Bunga per tahun	12.00%
3	Periode Pmbyrn (bulan)	1
4	Jumlah periode	60	---> 5 tahun
5
6			Rumus
7	Pmbyrn per periode	2,224,445	=PMT(B2*(B3/12),B4,-B1)
8	Jml Pembayaran	133,466,686	=B6*B4
9	Jml Bunga	33,466,686	=B7-B1
10
11	Bunga per tahun		12.00%	12.25%	12.50%	12.75%	13.00%	13.25%	13.50%
12	Pmbyrn per periode	2,224,445	2,224,445	2,237,099	2,249,794	2,262,530	2,275,307	2,288,126	2,300,985
13	Jml Pembayaran	133,466,686	133,466,686	134,225,922	134,987,629	135,751,803	136,518,438	137,287,531	138,059,076
14	Jml Bunga	33,466,686	33,466,686	34,225,922	34,987,629	35,751,803	36,518,438	37,287,531	38,059,076
15
16	Cara membuat tabel di atas :
17
18	1. Buat referensi perhitungan seperti terlihat pada Range A1:D9
19	2. Buat tabel dan isikan angka-angka di cell yang berwarna biru
20	3. Kemudian Pilih Range B11:I14 lalu pilih Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table lalu excel akan menampilkan Data Table seperti gambar berikut :
21
22	4. Isi Row Input Cell : $B$2 dan Column input cell biarkan kosong
23	5. Klik OK dan angka-angka dalam range C12:I14 akan terisi secara otomatis.

Multiple Lookup Contoh

2010-08-06T21:34:00.011+07:00

	A	B	C	D	E	F	G	H
1	Tabel Referensi
2	Warna-1	Warna-2	Warna-3	Output
3	merah	hijau	biru	putih
4	merah	hijau	kosong	kuning
5	merah	kosong	biru	ungu
6	kosong	hijau	biru	cyan
7	kosong	hijau	kosong	hijau
8	kosong	kosong	biru	biru
9	kosong	kosong	kosong	hitam
10
11	Tabel yang akan di-look up (diintip)
12	merah	hijau	kosong	kuning
13	kosong	hijau	kosong	hijau
14	merah	hijau	kosong	kuning
15	merah	hijau	biru	putih
16	kosong	kosong	kosong	hitam
17	merah	hijau	putih	#N/A
18
19	Rumus di D12 adalah sebagai berikut:
20	={INDEX($D$3:$D$9,MATCH(A12&B12&C12,$A$3:$A$9&$B$3:$B$9&$C$3:$C$9,0))} copy rumus/formula tersebut ke bawah seperti ini :
21	={INDEX($D$3:$D$9,MATCH(A13&B13&C13,$A$3:$A$9&$B$3:$B$9&$C$3:$C$9,0))}
22	={INDEX($D$3:$D$9,MATCH(A14&B14&C14,$A$3:$A$9&$B$3:$B$9&$C$3:$C$9,0))}
23	={INDEX($D$3:$D$9,MATCH(A15&B15&C15,$A$3:$A$9&$B$3:$B$9&$C$3:$C$9,0))}
24	dst

Atau bisa juga menggunakan macro VBA dengan cara masuk ke VBA Editor dengan menekan Alt+F11 --> Insert Module --> Masukkan kode berikut:

Function sitiVi26A(Tabel As Range, Kriteria As Range) As String
Dim MaxTabelRow As Long, r As Long
MaxTabelRow = Tabel.Rows.Count
For r = 1 To MaxTabelRow
If Kriteria.Cells(1, 1) = Tabel.Cells(r, 1) And _
Kriteria.Cells(1, 2) = Tabel.Cells(r, 2) And _
Kriteria.Cells(1, 3) = Tabel.Cells(r, 3) Then
sitiVi26A = Tabel.Cells(r, 4).Text
Exit For
Else
sitiVi26 = "#n/a"
End If
Next r
End Function

Lalu kembali ke worksheet dan di cell D12 ketikkan rumus
=sitiVi26A($A$3:$D$9,A12:C12)
terus copy rumus tersebut ke cell2 dibawahnya,
Sumber: mailing list xl-mania@yahoogroups.com

Cara Membuat USB Flashdisk Bootable for Windows XP/Vista/7

2010-08-02T23:59:00.004+07:00

Sebenarnya ada cara mudah membuat bootable windows alive in your USB flash / pen / thumb drive. Yaitu dengan cara memakai portable tool bernama NoviCorp WintoFlash yang sangat membantu.

WintoFlash Features
1. transfer windows XP/2003 setup to USB drive
2. transfer WinPE XP/2003 to USB drive (transfer Pre-installed windows XP/2003 environment)
3. erase disk
4. transfer windows Vista/2008/7 setup to USB drive
5. transfer WinPE Vista/2008/7 to USB drive (transfer Pre-installed windows Vista/2008/7 environment)
6. create Windows XP/2003 emergency bootloader USB drive
7. create bootable MS-DOS USB drive

Yang Anda butuhkan:
1. Windows Setup files/CD / DVD
2. satu USB flash / pen /thumb drive yang kosong, anda membutuhkan sedikitnya dengan kapasitas 4GB USB drive jika Anda akan membuat bootable windows 7.

Cara membuat USB bootable Windows 7, Vista and XP?
1. Plug USB drive to USB port
2. Download WintoFlash
3. Extract semua contents on the zip file (you can use 7-zip or other extractor software)
4. Run WinToFlash application
5. Simply click the big button “Windows Setup transfer wizard” and click next
6. Specify Windows file path and USB drive location and then click next
7. Windows license agreement will pop up, click agree and continue.
8. Click ok on format warning (please note that all contents in USB drive will be lost)
9. Tunggu beberapa saat ketika WintoFlash membuat sebuah bootable windows setup in USB drive

Jika Anda ingin membuat sebuah bootable linux pada USB drive Anda dapat gunakan Unetbootin. Semuanya telah dikerjakan. Sekarang Anda telah berhasil membuat sebuah USB bootable windows 7/Vista/XP, dan siap digunakan. Jangan lupa mengubah setting boot sequence / priority pada BIOS.

Download wintoflash di sini.

Sumber
http://wintoflash.com/download/en/
http://indygizmo.com/how-to-create-usb-bootable-windows7-vista-xp/

Contoh Perhitungan Loan Payment di Excel

2010-08-01T20:17:00.012+07:00

Loan Amount:	100,000,000.00	-PV
Annual Interest Rate:	13.0%	rate_per_year
Payment Period (months):	1	B3
Number of Periods:	60	nper

Payment per Period:	$2,275,307.30	=PMT(rate_per_year*B3/12,nper,-PV)


Period	3
Principal Amount	$1,217,939.97	=PPMT(rate_per_year*B3/12,3,nper,-PV)
Interest Amount	$1,057,367.34	=IPMT(rate_per_year*B3/12,3,nper,-PV)

Grafik pembayaran pokok dan bunga sebagai berikut..

Bagaimana membuat "read more.." atau "baca selanjutnya" di Blogger

2010-08-01T12:21:00.012+07:00

Berikut teknik/hack membuat link Baca selengkapnya atau read more... di Blogger atau blogspot yang saya kutip dari http://www.eblogtemplates.com/how-to-add-the-blogger-read-more-expandable-posts-link/ dan telah saya buktikan bisa.

Langkah #1 -Update kode template AndaMasuk ke dashbord blog--> Pilih Setting --> Edit Html --> Klik expandable widget
Tambahkan kode berikut dibawah tag <div class=’post-header-line-1′/> dan <div class=’post-header-line’> jika dua-duanya ada.

<b:if cond='data:blog.pageType == "item"'>
<style>.fullpost{display:inline;}</style>
<p><data:post.body/></p>
<b:else/>
<style>.fullpost{display:none;}</style>

Sekarang tambahkan sedikit kode yang akan menambahkan link "read more" pada posting anda. Kode ini berada di bawah tag <data:post.body/> so copy kode berikut dibawah tag tersebut. Anda bebas mengganti "Read more..." dengan kata2 pilihan Anda. Hati-hati jangan menghapus kode-kode yang lainnya.

<b:if cond='data:blog.pageType != "item"'><br />
<a expr:href='data:post.url'>Read more...</a>
</b:if>
</b:if>

Beres, Andan telah selesai meng-edit template blog Anda. Lalu simpan kode html Anda. Jika tidak ada pesan error maka kode berhasil disimpan, silakan ke Langkah berikutnya.

Langkah #2 – Menanmbahkan Class Tag pada default Post Template

Masuk ke “Settings” => “Formatting” and scroll kebawah sampai ke pilihan terakhir “Post Template”. Paste-kan kode berikut:

<span class="fullpost">

</span>

Langkah #3 - Cobalah membuat posting baru

perhatikan gambar berikut...

My first "read more" post

2010-08-01T11:46:00.003+07:00

asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf asdf

klmn klmn klmn
klmn klmn klmn
klmn klmn klmn
klmn klmn klmn

Peter Noneley Excel Function Dictionary

2010-07-31T22:52:00.007+07:00

Anda ingin belajar fungsi dan formula dalam excel, disertai contoh-contoh penggunaanya yang semauanya diterangkan secara lengkap mengenai :

Help on the following functions.
Age Calculation	Using DATEDIF()
AutoSum shortcut key	Using Alt and =
Brackets in formula	Sample
FileName formula	Using MID() CELL() and FIND()
Instant Charts	Using F11
Ordering Stock	Stock Ordering
Percentages	How to calculate various percentages
Project Dates	Example using date calculation.
Show all formula	Using Ctrl and `
Split ForenameSurname	Using LEFT() RIGHT() FIND() SUBSTITUTE()
Time Calculation	How to calculate time.
TimeSheet For Flexi	Example flexi time sheet.
ABS	Returns the absolute value of a number
AND	Returns TRUE if all its arguments are TRUE
AVERAGE	Returns the average of its arguments
BIN2DEC	Converts a binary number to decimal
CEILING	Rounds a number to the nearest integer or to the nearest multiple of significance
CELL	Returns information about the formatting, location, or contents of a cell
CHAR	Returns the character specified by the code number
CHOOSE	Chooses a value from a list of values
CLEAN	Removes all nonprintable characters from text
CODE	Returns a numeric code for the first character in a text string
COMBIN	Returns the number of combinations for a given number of objects
CONCATENATE	Joins several text items into one text item
CONVERT	Converts a number from one measurement system to another
CORREL	Returns the correlation coefficient between two data sets
COUNT	Counts how many numbers are in the list of arguments
COUNTA	Counts how many values are in the list of arguments
COUNTBLANK	Counts the number of blank cells within a range
COUNTIF	Counts the number of nonblank cells within a range that meet the given criteria
DATE	Returns the serial number of a particular date
DATEDIF	Calculates the difference between two dates. Undocumented in v5/7/97
DATEVALUE	Converts a date in the form of text to a serial number
DAVERAGE	Returns the average of selected database entries
DAY	Converts a serial number to a day of the month
DAYS360	Calculates the number of days between two dates based on a 360-day year
DB	Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DCOUNT	Counts the cells that contain numbers in a database
DCOUNTA	Counts nonblank cells in a database
DEC2BIN	Converts a decimal number to binary
DEC2HEX	Converts a decimal number to hexadecimal
DELTA	Tests whether two values are equal
DGET	Extracts from a database a single record that matches the specified criteria
DMAX	Returns the maximum value from selected database entries
DMIN	Returns the minimum value from selected database entries
DOLLAR	Converts a number to text, using currency format
DSUM	Adds the numbers in the field column of records in the database that match the criteria
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
ERROR.TYPE	Returns a number corresponding to an error type
EVEN	Rounds a number up to the nearest even integer
EXACT	Checks to see if two text values are identical
FACT	Returns the factorial of a number
FIND	Finds one text value within another (case-sensitive)
FIXED	Formats a number as text with a fixed number of decimals
FLOOR	Rounds a number down, toward zero
FORECAST	Returns a value along a linear trend
FREQUENCY	Returns a frequency distribution as a vertical array
GCD	Returns the greatest common divisor
GESTEP	Tests whether a number is greater than a threshold value
GROWTH	Returns values along an exponential trend
HEX2DEC	Converts a hexadecimal number to decimal
HLOOKUP	Looks in the top row of an array and returns the value of the indicated cell
HOUR	Converts a serial number to an hour
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
INDIRECT	Returns a reference indicated by a text value
INFO	Returns information about the current operating environment
INT	Rounds a number down to the nearest integer
ISBLANK	Returns TRUE if the value is blank
ISERR	Returns TRUE if the value is any error value except #N/A
ISERROR	Returns TRUE if the value is any error value
ISEVEN	Returns TRUE if the number is even
ISLOGICAL	Returns TRUE if the value is a logical value
ISNA	Returns TRUE if the value is the #N/A error value
ISNONTEXT	Returns TRUE if the value is not text
ISNUMBER	Returns TRUE if the value is a number
ISODD	Returns TRUE if the number is odd
ISREF	Returns TRUE if the value is a reference
ISTEXT	Returns TRUE if the value is text
LARGE	Returns the k-th largest value in a data set
LCM	Returns the least common multiple
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
LOOKUP (vector)	Looks up values in a vector or array
LOWER	Converts text to lowercase
MATCH	Looks up values in a reference or array
MAX	Returns the maximum value in a list of arguments
MEDIAN	Returns the median of the given numbers
MID	Returns a specific number of characters from a text string starting at the position you specify
MIN	Returns the minimum value in a list of arguments
MINUTE	Converts a serial number to a minute
MINVERSE	Returns the matrix inverse of an array
MMULT	Returns the matrix product of two arrays
MOD	Returns the remainder from division
MODE	Returns the most common value in a data set
MONTH	Converts a serial number to a month
MROUND	Returns a number rounded to the desired multiple
N	Returns a value converted to a number
NA	Returns the error value #N/A
NETWORKDAYS	Returns the number of whole workdays between two dates
NOT	Reverses the logic of its argument
NOW	Returns the serial number of the current date and time
ODD	Rounds a number up to the nearest odd integer
OR	Returns TRUE if any argument is TRUE
PERMUT	Returns the number of permutations for a given number of objects
PI	Returns the value of Pi
POWER	Returns the result of a number raised to a power
PRODUCT	Multiplies its arguments
PROPER	Capitalises the first letter in each word of a text value
QUARTILE	Returns the quartile of a data set
QUOTIENT	Returns the integer portion of a division
RAND	Returns a random number between 0 and 1
RANDBETWEEN	Returns a random number between the numbers you specify
RANK	Returns the rank of a number in a list of numbers
REPLACE	Replaces characters within text
REPT	Repeats text a given number of times
RIGHT	Returns the rightmost characters from a text value
ROMAN	Converts an arabic numeral to roman, as text
ROUND	Rounds a number to a specified number of digits
ROUNDDOWN	Rounds a number down, toward zero
ROUNDUP	Rounds a number up, away from zero
SECOND	Converts a serial number to a second
SIGN	Returns the sign of a number
SLN	Returns the straight-line depreciation of an asset for one period
SMALL	Returns the k-th smallest value in a data set
STDEV	Estimates standard deviation based on a sample
STDEVP	Calculates standard deviation based on the entire population
SUBSTITUTE	Substitutes new text for old text in a text string
SUBTOTAL	Returns a subtotal in a list or database
SUM	Adds its arguments
SUM_as_Running_Total	Sample
SUM_using_names	Using SUM(jan)
SUM_with_OFFSET	Sample
SUMIF	Adds the cells specified by a given criteria
SUMPRODUCT	Returns the sum of the products of corresponding array components
SYD	Returns the sum-of-years’ digits depreciation of an asset for a specified period
T	Converts its arguments to text
TEXT	Formats a number and converts it to text
TIME	Returns the serial number of a particular time
-Timesheet	Sample
TIMEVALUE	Converts a time in the form of text to a serial number
TODAY	Returns the serial number of today’s date
TRANSPOSE	Returns the transpose of an array
TREND	Returns values along a linear trend
TRIM	Removes spaces from text
TRUNC	Truncates a number to an integer
TYPE	Returns a number indicating the data type of a value
UPPER	Converts text to uppercase
VALUE	Converts a text argument to a number
VAR	Estimates variance based on a sample
VARP	Calculates variance based on the entire population
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
WEEKDAY	Converts a serial number to a day of the week
WORKDAY	Returns the serial number of the date before or after a specified number of workdays
YEAR	Converts a serial number to a year
YEARFRAC	Returns the year fraction representing the number of whole days between start_date and end_date

Peter Noneley telah menyiapkannya untuk Anda gratis silkan kunjungi websitenya di http://www.xlfdic.com atau download di sini.
Semoga bermanfaat.

My Favorite Shortcut Keys

2010-07-31T22:16:00.003+07:00

Berikut daftar shortcut keys yang sering saya gunakan untuk menghemat waktu, paling enggak menurut saya, he2x

Ctrl+A --> menyeleksi current region dan berikutnya akan menyeleksi seluruh worksheet
Ctrl+C --> copy/salin
Ctrl+V --> paste/ tempel
Ctrl+F --> Find and Replace
Ctrl+G or F5 --> membuka Go To Dialog
Ctrl+P --> Print
Ctrl+S --> Save active file
Ctrl+X --> Cut
Ctrl+Z --> Undo
Ctrl+Y --> Redo
F4 --> mengulangi perintah sebelumnya
F2 --> edit cell
F9 --> calculate now
Ctrl+0 --> Hides selected kolom
Ctrl+9 --> Hides selected rows
Ctrl+1 --> menampilkan format cell dialog box
Ctrl+; --> memasukkan tanggal hari ini
Ctrl+ Enter --> memasukkan text/formula ke suatu range
Alt+Enter --> berpindah baris pada suatu cell
Esc --> menghindar, hehe...

Untuk menggerakkan kursor atau menyeleksi cell/range data coba gunakan kombinasi tombol panah, ctrl, shift, pageUp, pageDn, Home, dan End.
Jika berminat, untuk lebih lengkapnya silakan download di sini.

Membuat Penomoran Otomatis Baris dan Kolom

2010-07-29T22:26:00.006+07:00

Berikut ini saya bagikan cara membuat penomoran otomatis di judul kolom dan nomor baris pada suatu tabel dalam kondisi yang difilter.
Misalnya kita memiliki data seperti tabel berikut:

Mungkin anda akan membutuhkan ini saat akan mencetak tabel tersebut dalam kondisi difilter atau ada kolom yang di-hide. Sebelum mencetak anda harus menomori terlebih dahulu baris dan kolom. Untuk itu anda perlu menggunakan trik excel berikut:

Buat layout tabel seperti gbr di atas, ketik di A2 : =IF(CELL("width",A1)=0,0,1)
Ketik di B2 : =IF(CELL("width",B1)=0,0,MAX($A2:A2)+1)
Copy sel B2 ke kanan, yaitu di sel C2:E2
Ketik di A3 : =SUBTOTAL(3,B$3:B3) terus copy ke bawah.

Maka ketika tabel difilter nomor di sel A3:A11 akan terupdate secara otomatis sedangkan nomor di judul (header) kolom akan terupdate jika kita me-calculate now (atau dengan menekan F9)
===selesai====

Menggunakan Benford's Law dengan excel untuk mengembangkan Business Planning

2010-07-28T20:59:00.003+07:00

Benford's law mengungkap karakteristik data secara mengagumkan. Tidak hanya membantu mengidentifikasi kecurangan, tetapi juga berguna untuk penyusunan anggaran dan perkiraan (forecast).
Akuntan dan auditor kadang2 menggunakan pengetahuan statistikal yang mengagumkan ini untuk mengungkap kecurangan laporan keuangan. Juga bisa berfungsi mencari strategi yang jitu dalam investasti di pasar saham dan meningkatkan akurasi budget dan forecast.
Artikel ini akan menjelaskan Benford's Law, bgmana menghitungnya dengan bantuan excel, dan saran bagaimana menafsirkan hasilnya secara baik.

Apa itu Benford's Law?
Juga disebut "first digit law", Benfors's Law mengatakan bahwa dalam banyak kejadian di alam nyata didapatkan data yang digit angka pertamanya adalah 1 sekitar 30% dan frekuensi munculnya data yang awali angka kecil lebih sering daripada angka besar.
Nama hukum ini muncul dari Dr, Frank Benford, fisikawan yang bekerja di perusahaan General Electric. Pada tahun 1938 dia menyadari halaman2 pertama pada tabel logaritma yang menunjukkan angka kecil lebih kotor dan lusuh dari pada angka besar.
Untuk membaca lanjutannya silakan klik di sini.

Format Angka

2010-07-27T22:06:00.002+07:00

Excel menyediakan beberapa pilihan format untuk angka. Bisa dilihat di tab Home --> Number Pilih di Daftar Pilihan yang Tersedia (default: General) atau juga bisa diakses dgn menekan shortcut Ctrl+1. Pilihan format angka yang tersedia yaitu :

Tips mengetik NPWP menggunakan Custom Number Format

Caranya : Pilih Home – Number – Custom lalu ketik 00”.”000”.”000”.”0”-517.000”

Maka ketika Anda ketik, misalnya, 13649674 maka yang muncul adalah 01.364.967.4-517.000. Cukup menghemat waktu kan…

Lihat gambar berikut.

Fungsi Excel spreadsheet dan Sejarahnya

2010-07-27T21:00:00.006+07:00

Microsoft Office Excel (singkatnya kita sebut excel saja) adalah aplikasi office pengolah angka/data (spreadsheet) paling popular saat ini dan menjadi standar di dunia. Versi terbarunya excel 2010 yang baru saja dirilis, disebut juga versi 14 kelanjutan dari excel 2007 (versi 12).
Kegunaanya:
1. Number Crunching : menyusun anggaran, analisa hasil survey, analisa laporan keuangan.
2. Creating charts (grafik)
3. Organizing lists: Use the row-and-column layout to store lists efficiently.
4. Accessing other data: Import data from a wide variety of sources.
5. Creating graphics and diagrams: Use Shapes and the new SmartArt to create professional-looking diagrams.
6. Automating complex tasks: Perform a tedious task with a single mouse click with Excel’s macro capabilities.

Berikut perbedaan excel 2003 dan 2007, silakan dicermati..

History of Microsoft Excel

Microsoft Excel has been referred to as "the best application ever written for Windows." You may or may not agree with that statement, but you can't deny that Excel is one of the oldest Windows products and has undergone many re- incarnations and face-lifts over the years. Cosmetically, the current version-Excel 2007-barely even resembles the original version. However, many of Excel's key elements have remained intact over the years, with significant enhancements, of course.

This chapter presents a concise overview of the features available in the more recent versions of Excel, with specific emphasis on Excel 2007. It sets the stage for the subsequent chapters and provides an overview for those who may have let their Excel skill get rusty.

Note If you're an old hand at Excel, you may want to read only the section on the Excel user interface and ignore or briefly skim the rest of the chapter.

Spreadsheets comprise a huge business, but most of us tend to take this software for granted. In the pre-spreadsheet days, people relied on clumsy mainframes or calculators and spent hours doing what now takes minutes.

It Started with VisiCalc

Dan Bricklin and Bob Frankston conjured up VisiCalc, the world's first electronic spread- sheet, back in the late 1970s when personal computers were unheard of in the office environment. They wrote VisiCalc for the Apple II computer, an interesting machine that seems like a toy by today's standards. VisiCalc caught on quickly, and many forward-looking companies purchased the Apple II for the sole purpose of developing their budgets with VisiCalc. Consequently, VisiCalc is often credited for much of Apple II's initial success.

Then Came Lotus
When the IBM PC arrived on the scene in 1982, thus legitimizing personal computers, VisiCorp wasted no time porting VisiCalc to this new hardware environment. Envious of VisiCalc's success, a small group of computer enthusiasts at a start-up company in Cambridge, Massachusetts, refined the spreadsheet concept. Headed by Mitch Kapor and Jonathon Sachs, the company designed a new product and launched the software industry's first full-fledged marketing blitz. Released in January 1983, Lotus Development Corporation's 1-2-3 proved an instant success. Despite its $495 price tag (yes, people really paid that much for a single program), it quickly outsold VisiCalc and rocketed to the top of the sales charts, where it remained for many years. Lotus 1-2-3 was, perhaps, the most popular application ever.

Microsoft Enters the Picture

Most people don't realize that Microsoft's experience with spreadsheets extends back to the early 1980s. In 1982, Microsoft released its first spreadsheet-MultiPlan. Designed for computers running the CP/M operating system, the product was subsequently ported to several other platforms, including Apple II, Apple III, XENIX, and MS-DOS. MultiPlan essentially ignored existing software user-interface standards. Difficult to learn and use, it never earned much of a following in the United States. Not surprisingly, Lotus 1-2-3 pretty much left MultiPlan in the dust.

Excel partly evolved from MultiPlan, first surfacing in 1985 on the Macintosh. Like all Mac applications, Excel was a graphics-based program (unlike the character-based MultiPlan). In November 1987, Microsoft released the first version of Excel for Windows (labeled Excel 2 to correspond with the Macintosh version). Excel didn't catch on right away, but as Windows gained popularity, so did Excel. Lotus eventually released a Windows version of 1-2-3, and Excel had additional competition from Quattro Pro-originally a DOS program developed by Borland International, then sold to Novell, and then sold again to Corel (its current owner).

Excel Versions
Excel 2007 is actually Excel 12 in disguise. You may think that this name represents the twelfth version of Excel. Think again. Microsoft may be a successful company, but its version-naming techniques can prove quite confusing. As you'll see, Excel 2007 actually represents the tenth Windows version of Excel. In the following sections, I briefly describe the major Windows versions of Excel.

EXCEL 2
The original version of Excel for Windows, Excel 2 first appeared in late 1987. It was labeled Version 2 to correspond to the Macintosh version (the original Excel). Because Windows wasn't in widespread use at the time, this version included a runtime version of Windows-a special version with just enough features to run Excel and nothing else. This version appears quite crude by today's standards.

EXCEL 3
At the end of 1990, Microsoft released Excel 3 for Windows. This version offered a significant improvement in both appearance and features. It included toolbars, drawing capabilities, worksheet outlining, add-in support, 3-D charts, workgroup editing, and lots more.

EXCEL 4
Excel 4 hit the streets in the spring of 1992. This version made quite an impact on the marketplace as Windows increased in popularity. It boasted lots of new features and usability enhancements that made it easier for beginners to get up to speed quickly.

EXCEL 5
In early 1994, Excel 5 appeared on the scene. This version introduced tons of new features, including multisheet workbooks and the new Visual Basic for Applications (VBA) macro language. Like its predecessor, Excel 5 took top honors in just about every spreadsheet comparison published in the trade magazines.

EXCEL 95
Excel 95 (also known as Excel 7) shipped in the summer of 1995. On the surface, it resembled Excel 5 (this version included only a few major new features). However, Excel 95 proved to be significant because it presented the first version to use more advanced 32-bit code. Excel 95 and Excel 5 use the same file format.

EXCEL 97
Excel 97 (also known as Excel 8) probably offered the most significant upgrade ever. The toolbars and menus took on a great new look, online help moved a dramatic step forward, and the number of rows available in a worksheet quadrupled. And if you're a macro developer, you may have noticed that Excel's programming environment (VBA) moved up several notches on the scale. Excel 97 also introduced a new file format.

EXCEL 2000
Excel 2000 (also known as Excel 9) was released in June of 1999. Excel 2000 offered several minor enhancements, but the most significant advancement was the ability to use HTML as an alternative file format. Excel 2000 still supported the standard binary file format, of course, which is compatible with Excel 97.

EXCEL 2002
Excel 2002 (also known as Excel 10) was released in June of 2001 and is part of Microsoft Office XP. This version offered several new features, most of which are fairly minor and were designed to appeal to novice users. Perhaps the most significant new feature was the capability to save your work when Excel crashes and also recover corrupt workbook files that you may have abandoned long ago. Excel 2002 also added background formula error checking and a new formula-debugging tool.

EXCEL 2003
Excel 2003 (also known as Excel 11) was released in the fall of 2003. This version had very few new features. Perhaps the most significant new feature was the ability to import and export XML files and map the data to specific cells in a worksheet. It also introduced the concept of the List, a specially designated range of cells. Both of these features would prove to be precursors to future enhancements.

EXCEL 2007
Excel 2007 (also known as Excel 12) was released in early 2007. Its official name is Microsoft Office Excel 2007. This latest Excel release represents the most significant change since Excel 97, including a change to Excel's default file format. The new format is XML based although a binary format is still available. Another major change is the Ribbon, a new type of user interface that replaces the Excel menu and toolbar system. In addition to these two major changes, Microsoft has enhanced the List concept introduced in Excel 2003 (a List is now known as a Table), improved the look of charts, significantly increased the number of rows and columns, and added some new worksheet functions. For more, see the sidebar, "What's New in Excel 2007?".

==================== the end ==========================

Sahabat yg Setia

2008-11-20T22:51:00.001+07:00

Ketahuilah wahai jiwa yg bimbang, ketahuilah Dialah yg selalu menemanimu setiap saat, ketika kamu di rumah, bepergian, terjaga, tidur, dan ketika matimu dan hidupmu. Dialah pemeliharamu, Tuanmu, Kekasihmu, dan Penciptamu. Saat kamu mengingatNya, Dia akan hadir di dekatmu. Ketika engkau sedang patah hati gundah gulana atas kecerobohanmu dalam menjaga hak2 Dinmu, Dialah sohibmu yg setia. Karena itu, jikalau kamu bisa memahami dan merasakan ini semua, niscaya kamu senantiasa menjadikannya teman atau sahabat.

Akidah ahlussunnah wal jamaah

2008-11-02T22:30:00.001+07:00

Akidah berasal dari akar kata akad yg berarti mengikat (mis. Unta), bertransaksi (jual beli), menepati (janji dan sumpah). Di dlm agama didefinisikan hukum yg teguh diyakini dlm hati tanpa ragu atau bimbang. Kita, penganut aswaja, meyakini tiada Tuhan yg patut disembah scr haq kecuali Allah, yg esa dan tak bersekutu, yg qadim azalí, langgeng abadi, tak ada mula bagi awalnya, tak ada finish bagi akhirnya. Dan tidak ada yg menyerupainya. Allah yg dibersihkan dari dimensi waktu dan tempat, serta kesamaan dgn alam semesta, arah (kompas) tidak bisa meliputinya, dan bersemayam di arshinya menurut cara dlm firmannya dan dengan arti yg Ia kehendaki. Allah yg maha kuasa dan maha mengetahui.

Tips dalam bahan bakar minyak alias bbm

2008-10-19T22:17:00.001+07:00

Kemarin saya baca artikel mbak ayik di multiply tentang tips dlm mengisi bbm. Saya pikir tips ini penting dan relevan sekali saat harga bbm mahal dan katanya dunia kedepan akan mengalami krisis energi. Pertama, isilah tangki bbm saat pagi hari saat udara masih dingin. Karena dingin, bbm konsentrasinya lebih tinggi drpd saat udara panas. Kedua, isi saat tangki kendaraan kita setengah penuh. Jangan menunggu sampai tersisa sedikit apalagi kosong. Katanya celah kosong lebih besar yg terisi udara, akan menyebabkan pemuaian bbm lebih banyak. Ketiga, jangan isi saat ada truk pembawa bbm mengisi di spbu. Karena saat itu, bbm yg ada tangki spbu akan teraduk sehingga kotoran yg mengendap akan terangkat. Dan, mungkin kotoran itu bisa masuk ke tangki kendaraan kita. Selengkapnya bisa dibaca di ayik_cantik.multiply.com

Al-quran,waktu terpilih membacanya

2008-09-25T18:47:00.001+07:00

Waktu utama untuk membacanya adalah ketika kita shalat. Menurut madzab syafi'i dan selainnya memperlama berdiri itu lebih afdal drpd memperpanjang sujud atau lainnya. Berikutnya, waktu yg lebih utama adl membaca Al-quran malam hari. Separo malam yg akhir itu lebih bagus. Membaca quran antara maghrib dan isya itu mahbubah (disukai). Klo memilih siang hari maka bakda subuh itu lebih utama. Dan tidaklah ada makruh membaca pada suatu karena ada suatu arti khusus ketika kita membacanya. Kalo anda memilih hari favorit untuk membacanya, pilihlah jumat,senin-kamis, hari arofah, sepuluh hari terakhir dari ramadan dan 10 hari yg awal dari dzulhijjah. Dan klo memilih bulan, ramadanlah yg paling utama. (dikutip dari kitab imam nawawi)

Shalat tarawih (2)

2008-09-22T00:22:00.001+07:00

Dalam suatu hadist,rasulullah bersabda: barangsiapa beribadah pada malam ramadan disertai keimanan dan semata2 mendapatkan pahala dari Allah maka dosa2nya yg lalu akan diampuni. Dan di dlm hadist sahih muslim, aisyah berkata bahwa suatu malam (ramadan) rasulullah sholat yg diikuti oleh skmpl sahabat ,besoknya demikian jg, lalu pada malam ketiga atau keempat mereka berkumpul tapi rasulullah tidak keluar. Bsk paginya rasulullah berkata aku melihat apa yg telah kalian kerjakan aku tidak keluar krn takut akan difardukan pada kalian. Diriwayatkan dari abdur rahman bin abdul qari di dlm sahih bukhari berkata pada suatu malam ramadan aku keluar bersama umar bin khattab terlihat manusia2 sholat dgn membentuk kelompok2 sendiri maka melihat fenomena itu umar berkata jikalau mereka berkumpul shalat dgn satu pembaca (imam) niscaya lebih utama dan lebih dekat kpd kebaikan. Kmdn umar berazam lalu mengumpulkan mereka untuk shalat bersama dgn imam ubay bin kaab. Dan pada malam yg lain melihat kekompakan mereka dgn satu imam,umar berkata inilah kenikmatan bidah dan yg lebih afdal. Dari hadis2 di atas dapat disimpulkan yg pertama shalat tarawih adalah rasulullah dan kholifah umar yg telah berinisiatif mengumpulkan para sahabat untuk shalat bersama bin kaab.wa.

Shalat tarawih

2008-09-20T01:38:00.001+07:00

Tarawih itu jamak (plural) dari kata tarwihah yg berarti istirahat sekali. Dinamakan demikian karena orang2 beristirahat sebentar setelah dapet empat rakaat. Sdngkan menurut syara' (ajaran agama) ialah 20 rakaat yg dilakukan setiap malam ramadan, dua rakaat salam, dari setelah mengerjakan shalat isya' sampai terbit fajar. Hukumnya sunah bagi pria dan wanita. Nabi mengerjakannya secara rutin dan mendorongnya,demikian para sahabat dan tabiin. Tarawih mrpk salah satu syiar islam pada Ramadan yg penuh berkah.

Kata2 bijak ibnu atho'ilah

2008-09-20T01:14:00.000+07:00

Ketika berdoa hal yg penting bukanlah terwujudnya apa yg diminta, tetapi hal yg terpenting adalah kita dianugerahi adab (sopan santun) yg baik oleh Allah.maksudnya ketika berdoa itu. Seorang arif sangat memperhatikan adab bergaul dgn Rabb-nya.Dengan berdoa mendeklarasikan bahwa dirinya benar2 seorang hamba yg lemah dan sangat membutuhkan kasih sayang dan anugerah dari Tuhannya. Jd fokus pendorongnya bukanlah terwujudnya apa yg dimintanya, bukannya itu adl hak Allah untuk memberi dalam waktu dekat, lama, ditahan, atau diganti dengan yg lain.waallahu a'lam bisshowab.

salam pembuka di dunia blog

2008-02-20T11:35:00.000+07:00

Hello everyone...
Hari ini, Rabu, 20 Feb 2008, dua hari kerja di PMa Lima sebelum aq mendapat tugas mengikuti DTSD di Pusdiklat Pajak di Slipi. Di Seksi PDI ini, karena banyak waktu luang aq sangat bisa main internet-an dan bikin blog ini. Semoga bermanfaat.