Beranda > Excel > MENCARI NAMA WORKSHEET EXCEL DENGAN FUNGSI “CELL”

MENCARI NAMA WORKSHEET EXCEL DENGAN FUNGSI “CELL”


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 :wink: … 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

About these ads
  1. 10 Juli 2012 pukul 8:19 pm

    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 ???

    • 11 Juli 2012 pukul 4:08 pm

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

  2. ERNA MARIYANA
    27 September 2012 pukul 1:24 pm

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

    • 28 September 2012 pukul 1:48 pm

      waduhhhh … saya ndak kenal jugak :?

  3. intan
    19 Oktober 2012 pukul 9:16 am

    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… :)

  4. 14 November 2012 pukul 10:17 am

    selamat pagi pak, mohon kunci SSP3.2400. TRIM

    • 14 November 2012 pukul 3:42 pm

      SSP3.2400 KONCINYA : 0T8AIIVWY0

  5. Rini Suhetiek
    23 Januari 2013 pukul 1:15 pm

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

  6. Dian adha
    20 Mei 2013 pukul 2:42 pm

    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

  7. Dian adha
    21 Mei 2013 pukul 3:16 pm

    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 pukul 9:39 am

      Tengkiyu mas.
      Jitu bget solusinya

      • Dian adha
        11 Juni 2013 pukul 4:55 pm

        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

  8. 7 Juli 2013 pukul 2:14 pm

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

    Muhammad Syukron : SSP3.6500 KONCINYA : 0FQ8FCW4E0

  9. 6 Februari 2014 pukul 6:01 am

    Ң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.
    ;)

  10. 13 Februari 2014 pukul 10:32 am

    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.

  11. 7 Maret 2014 pukul 12:57 am

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

  12. 10 April 2014 pukul 4:18 am

    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.

  13. 24 April 2014 pukul 7:57 am

    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.

  14. 13 Mei 2014 pukul 8:09 pm

    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))

  15. 1 Juni 2014 pukul 6:55 am

    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.

  16. 3 Juni 2014 pukul 3:32 pm

    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.

  17. 6 Juni 2014 pukul 12:55 pm

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

  18. 9 Juni 2014 pukul 11:05 am

    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.

  19. 3 Juli 2014 pukul 9:20 pm

    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

  20. 5 Juli 2014 pukul 7:19 pm

    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.

  21. 10 Juli 2014 pukul 5:01 pm

    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.

  22. 11 Juli 2014 pukul 8:57 am

    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.

  23. Bakri Irham
    16 Juli 2014 pukul 6:05 pm

    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

  24. 3 Agustus 2014 pukul 3:36 am

    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.

  25. 5 Agustus 2014 pukul 12:00 pm

    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.

  26. 9 Agustus 2014 pukul 2:53 pm

    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.

  27. 11 September 2014 pukul 4:11 am

    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!

  28. 17 September 2014 pukul 11:52 pm

    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.

  29. 20 September 2014 pukul 8:23 pm

    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?

  30. 23 September 2014 pukul 1:30 pm

    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…

  31. 24 September 2014 pukul 6:05 pm

    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.

  32. 5 Oktober 2014 pukul 7:39 am

    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.

  33. 5 Oktober 2014 pukul 9:32 pm

    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?

  34. 8 Oktober 2014 pukul 7:53 am

    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.

  1. 21 Maret 2013 pukul 10:06 am

Berikan Balasan

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Ikuti

Get every new post delivered to your Inbox.

Bergabunglah dengan 430 pengikut lainnya.

%d blogger menyukai ini: