jump to navigation

MENCARI NAMA WORKSHEET EXCEL DENGAN FUNGSI “CELL” 7 Mei 2012

Posted by excellerates in Excel.
Tags: , , , , , , , , , , , , ,
trackback

artikel kali ini berawal dari pertanyaan dari seorang pengunjung yang intinya sebuah Problema de Excellente tentang bagaimana formula untuk mencari nama sheet Excel … ada ndak yah fungsinya  :?:  … saya sendiri belOm menemukan fungsi Excel yang bisa langsung menghasilkan nama sheet dari sebuah cell Excel yang kita refferensikan … kalO ada yang tahu boleh dong share via komen disini … yang saya pernah lakukan untuk mencari nama sheet adalah dengan kombinasi beberapa fungsi Excel dengan Fungsi CELL sebagai fungsi utamanya

yaaagghh … fungsi ini nampaknya memang kurang populer bagi pengguna excel dibanding fungsi VLOOKUP, SUM, MIN, MAX dan fungsi2 “seleb” Excel laEnnya … tapi kalO sOdara2 berminat mempelajarinya saya sarankan cari dokumentasinya di Help Excel sOdara atau dari Website resmi Microsoft excel … dijamin penjelasan yang sOdara terima akan sangat valid dan dapat dipertanggungjawabkan … kan mereka yang bikin excel😉 … makanya untuk penjelasannya daripada capek ngetik ulanga  saya CoPas kan aja langsung dari pabriknya seperti berikOt :

Description

The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:

=IF(CELL(“type”, A1) = “v”, A1 * 2, 0)

This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank.

Syntax

CELL(info_type, [reference])

The CELL function syntax has the following arguments:

  • info_type    Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the info_type argument and the corresponding results.
info_type Returns
“address” Reference of the first cell in reference, as text.
“col” Column number of the cell in reference.
“color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
“contents” Value of the upper-left cell in reference; not a formula.
“filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
“format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.
“parentheses” The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
“prefix” Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else.
“protect” The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
“row” Row number of the cell in reference.
“type” Text value corresponding to the type of data in the cell. Returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else.
“width” Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.
  • reference    Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

CELL format codes

The following list describes the text values that the CELL function returns when the info_type argument is “format” and the reference argument is a cell that is formatted with a built-in number format.

If the Excel format is The CELL function returns
General “G”
0 “F0”
#,##0 “,0”
0.00 “F2”
#,##0.00 “,2”
$#,##0_);($#,##0) “C0”
$#,##0_);[Red]($#,##0) “C0-“
$#,##0.00_);($#,##0.00) “C2”
$#,##0.00_);[Red]($#,##0.00) “C2-“
0% “P0”
0.00% “P2”
0.00E+00 “S2”
# ?/? or # ??/?? “G”
m/d/yy or m/d/yy h:mm or mm/dd/yy “D4”
d-mmm-yy or dd-mmm-yy “D1”
d-mmm or dd-mmm “D2”
mmm-yy “D3”
mm/dd “D5”
h:mm AM/PM “D7”
h:mm:ss AM/PM “D6”
h:mm “D9”
h:mm:ss “D8”

 Note   If the info_type argument in the CELL function is “format” and you later apply a different format to the referenced cell, you must recalculate the worksheet to update the results of the CELL function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1
2
3
4
5
6
7
8
A B C
Data
5-Mar
TOTAL
Formula Description Result
=CELL(“row”, A20) The row number of cell A20 20
=CELL(“format”, A2) The format code of cell A2 D2 (d-mmm)
=CELL(“contents”, A3) The content of cell A3 TOTAL
=CELL(“type”, A2) The data type of cell A2 v (value)

seperti yang telah saya sampaikan pada pembukaan artikel ini salah satu aplikasi Fungsi ini adalah untuk mencari nama sheet dari cell yang direferensikan … contoh penggunaanya bisa kita pakaE untuk membuat penjumlahan yang berlanjut antar sheet secara otomatis … misalkan kita mempunyai data penjualan tiap bulan yang disimpan pada 1 sheet berbeda tiap bulannya … seperti penampakan tabel berikut

FungsiCell1

Tabel diatas berisi data penjualan 3 macam produk oleh 4 orang penjual pada bulan Januari 2012 … nama sheetnya adalah 1  … formula yang di pakai dalam sheet ini adalah sbb :

Cell C1 berisi Nama File + path … dengan formula

=CELL(“Filename”;A1)

hasilnya

F:\me&me\koleksiku\2012\[FungsiCell.xls]1

Cell C2 berisi formula

=MID(C1;FIND(“]”;C1;1)+1;LEN(C1)-FIND(“]”;C1;1))

hasilnya

1

Kemudian pada Cell B5 akan kita isi formula untuk menampilkan bulan dan tahun berdasarkan nama sheetnya … formulanya sbb

=TEXT(DATE(2012;C2;1);”mmmm yyy”)

hasilnya

Januari 2012

untuk Jumlah bulan lalu pada baris 14 dan pada kolom H ( yang berwarna kuning) karena sheet ini untuk bulan januari maka langsung diisi 0

untuk membuat bulan Februari bisa kita Copy dari sheet 1 … hasilnya sheet 1(2) … trus kita rename menjadi 2 seperti penampakan berikot

FungsiCell2

dalam sheet 2 Cell2 C1,C2 dan B5 telah menyesuaikan dengan nama sheet yang baru … sekarang kita bikin formula pada Cell yang warna kuning

pada Cell H8 sampai H11 isikan formula berikut

=OFFSET(INDIRECT(“‘”&$C$2-1&”‘!$A$1”);ROW()-1;COLUMN())

dan pada Cell C14 sampai F 14 isikan formula berikut

=OFFSET(INDIRECT(“‘”&$C$2-1&”‘!$A$1”);ROW()+1;COLUMN()-1)

dan hasilnya bisa langsung di dapet jumlah2 dari bulan sebelOmnya (Januari 2012)

untuk membuat sheet 3, 4, 5 dst tinggal di kloning aja sheet 2 ini dan di rename dengan nomor bulannya … tapi ingat ndak boleh loncat yahh … harus urut biar formulanya ndak error

silahkan diexplore Fungsi ini siapa tahu bisa menyelesaikan problema de excellente sOdara ….. semoga manpaat dan MDLMDL

Komentar»

1. Muhammad Chandra - 10 Juli 2012

mas misal di A1 saya pnya data begini :

A1
—-
A
A
A
A
A
B
B
B
B
C
C
C
C
D
D
E
E
E
E
F
F
F

saya pengen menampilkan di B2 begini :
A
B
C
D
E
F

rumusnya gimana ???

motorbreath - 11 Juli 2012

apakah datanya menempati 1 cell (A1) ? atau bersambung ke cell2 dibawahnya A2, A3 dst ?

2. ERNA MARIYANA - 27 September 2012

Mas tolong dong carikan data nama orang yg sy cari lengkapnya muhamad syahlevy allinsky

motorbreath - 28 September 2012

waduhhhh … saya ndak kenal jugak😕

fera - 24 Agustus 2015

Maaf mbak, kenapa dgn orang tersebut?

3. intan - 19 Oktober 2012

mas yang baik.. saya kan punya banyak shett.. misal ada 10 shett , saya mau carii cepat shett 5 ada cara mudah gak?? mohon bantuannya ya…🙂

motorbreath - 20 Oktober 2012
ratih - 10 Oktober 2013

saya sudah coba cara di atas tp setiap file saya tutup setelah saya buka lagi, sudah g bisa keluar daftar isi. cara munculinnya lagi gmn? mohon bantuannya

motorbreath - 28 Oktober 2013

kodenya sudah dimasukkan seperti dalam artikel tersebut ????
kalau sudah save as dengan pilihan macro enabled ( .xlam)

4. jondidi - 14 November 2012

selamat pagi pak, mohon kunci SSP3.2400. TRIM

motorbreath - 14 November 2012

SSP3.2400 KONCINYA : 0T8AIIVWY0

5. Rini Suhetiek - 23 Januari 2013

klo bikin data untuk stock barang pke excel it gmn y…????

motorbreath - 23 Januari 2013

ada kok silahkan di ubek2

6. VLOOKUP GAMBAR | Muhammad Syukron belajar Excel - 21 Maret 2013

[…] 1 untuk mencari nama sheet … untuk mencari nama sheet artikelnya disini …. […]

7. Dian adha - 20 Mei 2013

Tlong dibantu mas…
Gini critanya. Gimana cara supaya form macro yg kita buat lgsung tampil saat file dibuka, tapi lembar kerja excelnya dibuat tersembunyi

Muhammad Syukron :
bikin kode untuk menampilkan Userformnya pada saat file dibuka pada Module
Sub Auto_Open()
UserForm1.Show
End Sub

untuk menyembunyikan excel pakai kode berikut pada Userform1
Private Sub UserForm_Activate()
Application.Visible = False
End Sub

agar saat userform ditutup excelnya bisa muncul lagi pakai kode ini
Private Sub UserForm_Terminate()
Application.Visible = True
End Sub

8. Dian adha - 21 Mei 2013

Tengkiyu bgt mas atas ilmunya.
Nanya lg ni,
wktu msukin kode (On Error Resume Next With Application) koq diangep kode salah salah y. Gmn ni mas

Muhammad Syukron :
coba ganti baris
On Error Resume Next
With Application

Dian adha - 22 Mei 2013

Tengkiyu mas.
Jitu bget solusinya

Dian adha - 11 Juni 2013

Nanya lg ni mas,
gmn cara byar macro kt lgsung bs aktif, wlaupun macros settingnya msih disable

Muhammad Syukron : mohon maaf saya tidak bisa yang seperti itu

9. karniladian - 7 Juli 2013

MAS TOLONG MINTA KONCI ssp3.6500.hebat banget excelnya.. trims

Muhammad Syukron : SSP3.6500 KONCINYA : 0FQ8FCW4E0

10. Marta - 6 Februari 2014

Ңola!
dira que ess la primera ocasion que Һе
leido este blog y tengo que coentar que me estа gսstando y ϲreo qque me veras mas a menudo por aqui.
😉

11. clash of clans hack no download or survey - 13 Februari 2014

You just have to enter the number of clash of clans hack
gems or coins! In the game, you have to ask excuse me ma’am.

12. slim body dual cleanse - 7 Maret 2014

Get slim cleanse articles requiring technical abilities
are actually a difficult endeavor any time a ‘re a boy.

13. propane Austin - 10 April 2014

If some one wants to be updated with hottest technologies therefore he must be pay a quick
visit this web site and be up to date daily.

14. Clash Of Clans Hack Ipad Download - 24 April 2014

Though I would rather be in bed I will now examine the primary causes
of clash of clans hack tool no survey. The diversion plans
to give a strikingly distinctive undertake the tired, foreseeable cultivating kind.
Extremely clash of clans hack tool no survey is
heralded simply by shopkeepers and investment brokers
alike, leading many to mention that it is impossible to overestimate its impact on modern believed.

15. Fiztha - 13 Mei 2014

klo buat ambil nama filenya secara otomatis bisa koq pake rumus, coba copas rumus dibawah ini :

=LEFT(MID(CELL(“FILENAME”;A1);FIND(“[“;CELL(“FILENAME”;A1))+1;LEN(CELL(“FILENAME”;A1))-FIND(“[“;CELL(“FILENAME”;A1)));LEN(MID(CELL(“FILENAME”;A1);FIND(“[“;CELL(“FILENAME”;A1))+1;LEN(CELL(“FILENAME”;A1))-FIND(“[“;CELL(“FILENAME”;A1))))-(LEN(MID(CELL(“FILENAME”;A1);FIND(“[“;CELL(“FILENAME”;A1))+1;LEN(CELL(“FILENAME”;A1))-FIND(“[“;CELL(“FILENAME”;A1))))-FIND(“]”;MID(CELL(“FILENAME”;A1);FIND(“[“;CELL(“FILENAME”;A1))+1;LEN(CELL(“FILENAME”;A1))-FIND(“[“;CELL(“FILENAME”;A1))))+1))

16. Hervia Voucher Code - 1 Juni 2014

Do you have a spam problem on this site; I also am a blogger,
and I was wondering your situation; we have developed some nice procedures and
we are looking to exchange strategies with others, be sure to
shoot me an email if interested.

17. Proskin Coupon Code - 3 Juni 2014

I got this website from my pal who told me about this web site
and now this time I am browsing this web page and reading very informative content at this
time.

18. Chefs Catalog Coupon Code - 6 Juni 2014

Thanks in favor of sharing such a good thinking, article is pleasant,
thats why i have read it entirely

19. freeze pro shop discount code - 9 Juni 2014

Fantastic goods from you, man. I’ve understand your stuff previous to and you are just extremely excellent.
I actually like what you have acquired here, certainly like what you are
saying and the way in which you say it. You make it enjoyable and you still take care of to keep
it wise. I cant wait to read far more from you.
This is actually a great website.

20. Daily Tips And Tricks. - 3 Juli 2014

I know this if off topic but I’m looking into starting my own blog and was wondering what all is needed
to get setup? I’m assuming having a blog like yours would cost a pretty penny?
I’m not very web smart so I’m not 100% certain. Any suggestions or
advice would be greatly appreciated. Cheers

21. cheap steam game - 5 Juli 2014

I will right away seize your rss feed as I can’t
to find your email subscription hyperlink or e-newsletter
service. Do you have any? Please let me recognize in order
that I could subscribe. Thanks.

22. debt collection - 10 Juli 2014

We’re a group of volunteers and starting a new scheme in our community.
Your website provided us with valuable information to work on. You’ve done a formidable job and our entire community will be grateful
to you.

23. debt collector - 11 Juli 2014

If the credit agency continues to harass you after you have sent a debt
validation letter but before they respond to your request then you may be
able to sue them for violating the Fair Debt Collections Act.
You would like weekly, fortnightly or monthly on-line reports description data and statistics like
the name of somebody and reference, quantity owing, quantity recovered, prices incurred, standing and sum total of debt, recoveries and costs.

A professional debt recovery letter can save you from all of
that.

24. Bakri Irham - 16 Juli 2014

Selamat malam master, saya mau nanya, gimana caranya membuat rumus vlookup jika data source nya berubah rubah,
misalnya, saya punya file excel, ada sheet monitor dan sheet januari sampai december ( ada 13 sheet) yang mana sheet bulan merupakan update data yang akan terus berlanjut hingga bulan berikut nya,
pada sheet monitor misal cell A1 adal cell list ( nanti dengan memilih/atau memasukkan nama bulan pada sheet monitor cell A1 ( misal Juli-13), maka data yang akan ditarik adalah data pada sheet “Juli-13”

mohon pencerahan nya ,,
terimakasih, salam

25. Kristian - 3 Agustus 2014

Items can be simply because it’s stable and speculated market.

This is not finished, it is possible for website visitors to enjoy a full address
and they can hold any gold anyway. This year the two parties in the
major centers of choice? The only reason people are used to make sure that you have plenty of
gold ira investing sellers who prefer to keep living
off of a herringbone when viewed close up.

26. thunderbirdalumcom - 5 Agustus 2014

And you have for some, it can be hired per hour and also the added service.

Car limousine service service but you don t need that these customer-oriented companies provide excellent transportation services are available to take the load off of with the limousine issued by
concerned regulating agencies. The services of wedding
professionals that you can reach to you.

27. pdf books - 9 Agustus 2014

I blog quite often and I seriously thank you for your information.
This article has truly peaked my interest. I’m going to bookmark your website and keep checking for new details about once per week.
I subscribed to your RSS feed as well.

28. Aimee - 11 September 2014

Hey there! Quick question that’s entirely off topic. Do you know how to make
your site mobile friendly? My website looks weird when viewing from my iphone4.
I’m trying to find a theme or plugin that might be able to resolve this
problem. If you have any suggestions, please share. Many thanks!

29. Go here and start hacking facebook profiles - 17 September 2014

Thank you for some other informative web site. Where else may
just I am getting that type of info written in such an ideal approach?
I’ve a mission that I am simply now operating on, and I have been on the glance out
for such info.

30. www.dbvehicleelectrics.com - 20 September 2014

Thank you for the good writeup. It actually was a entertainment account it.

Look complicated to far delivered agreeable from you!
By the way, how could we be in contact?

31. Deni Iskandar - 23 September 2014

Mas, please help Me…
saya Punya data
kode Nama Lokasi
aaa Deni Bandung
aab Deri Kab. bandung
….
….
….
….
…..
abz zaenal Banjar

Saya memiliki data lain yang hampir sama dan volumenya record lebih banyak daripada yang diatas tapi tidak ada data kode, bagaimana ingin mencari kode pada data dibawah ini
Tgl Nama Lokasi Kode
1/01/2014 Deni Bandung ?
2/01/2014 Zaenal Banjar ?
3/01/2014 Deni Bandung ?
….
….
….
….

….
31/05/2014 Deri Kota Bandung ?
Bagaimana membuat rumus excel untuk mencari kode tersebut karena record banyak adakah cara cepat…terima kasih atas atensinya…

32. http://huubing.com/groups/apply-these-eight-secret-techniques-to-improve-new-short-hair-styles-and-colors - 24 September 2014

hi!,I like your writing very a lot! percentage we keep up a correspondence more about
your post on AOL? I require a specialist in this area to solve my problem.
Maybe that’s you! Taking a look forward to look you.

33. juegos de penales de futbol para 2 jugadores - 5 Oktober 2014

Running backs should constantly practice the hand off. Then proper into comprehensive defensive team practicing against
a scout offense. We do not want to run into a challenge as time goes on where
the youngsters simply want to take a seat home
as well as take in junk food as well as sit on right now there grows.
Oftentimes people are after the next greatest thing.

34. Suchmaschinenoptimierung Berlin - 5 Oktober 2014

At this time it appears like Movable Type is the top blogging platform out
there right now. (from what I’ve read) Is that what you are using on your
blog?

35. ProTow Blog - 8 Oktober 2014

Local officials estimate at least hundreds of
members. Opening bid at the auction we attended was
$250 and most every vehicle sold for under $1,000.
“Seaside Heights residents will be able to retrieve their vehicles or remove personal items from their vehicles from inside the APK Auto Repair and Towing impound lots on Monday, November 26, between 9 a.

36. Ingeborg - 23 Oktober 2014

You should be a part of a contest for one of the highest quality
blogs online. I’m going to highly recommend this web site!

37. sugitongadenan - 7 Desember 2014

mas saya pnya data ada 5 sheet, sheet 1 untuk menu, sheet 2 untuk duk, sheet 3 untuk jmlah golongan, sheet 4 untuk penddikan dan sheet 5 untuk jenis kalmin..yg saya mau tnyakan gmna cara menyembunyikan smua sheet..yg kliatan cuma sheet untuk menu tpi sheet yg lain bsa d linkan..gmna cra mmbuatnya

38. Rentin WA Family Attorneys - 3 Januari 2015

Good write-up. I certainly love this site. Stick with it!

39. wahyu dian shalat - 29 Mei 2015

agan saya minta tolong dunk untuk penjelasan rumus vlookup di bawah ini :
=VLOOKUP(B27,$K$5:$O$17,IF(AND(HasSupAdm!AH17>=86,HasSupAdm!AH17=71,HasSupAdm!AH17=50,HasSupAdm!AH17=1,HasSupAdm!AH17<=49),5,IF(HasSupAdm!AH17<1,"NOT YET",))))),FALSE)

kenapa rumus itu hasilnya menjadi #N/A…?

terima kasih sebelumnya agan

40. andisahar - 3 Juli 2015

mohon pencerahan bro…
gimana caranya menyisipkan satu baris pada sheet1 kemudian sheet2,sheet3,dst ikut disisipkan secara otomatis sama dgn sheet1.

41. hasbi - 8 September 2015

mas, minta bantuan untuk aplikasi data siswa mas
lagi belajar, jadi kurang ngerti bahas vb nya
thank

42. the house - 7 Oktober 2015

Piece of writing writing is also a fun, if you know then you
can write otherwise it is difficult to write.

43. dayat - 14 Februari 2016

bos kl minta dibikinin aplikasi bisa g,,,??
minta email nya dong biar bisa sharing.
ny email saya ahmaddayat8441@yahoo.com
thanks

44. Theresa - 31 Maret 2016

You purchase one $2 object together with the
$1 off voucher AND use the Get 1, Acquire 1 Free discount which means you get 2 of the same $2 piece for only $1.


Silahkan berkomentar

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

Ikuti

Kirimkan setiap pos baru ke Kotak Masuk Anda.

Bergabunglah dengan 558 pengikut lainnya

%d blogger menyukai ini: