Kembali sel kosong dari rumus di Excel

Excel tidak memiliki cara untuk melakukan hal ini.

Hasil dari rumus dalam sebuah sel di Excel harus berupa angka, teks, logika (boolean) atau kesalahan. Tidak ada formula nilai sel jenis "kosong" atau "kosong".

Salah satu praktek yang telah diikuti adalah dengan menggunakan NA() dan ISNA(), tapi itu mungkin atau mungkin tidak benar-benar memecahkan masalah anda karena ada big differrence di jalan NA() diperlakukan dengan fungsi-fungsi lainnya (SUM(NA()) adalah #N/A sementara SUM(A1) adalah 0 jika A1 adalah kosong).

Anda're akan harus menggunakan VBA, maka. Anda'll iterate atas sel-sel dalam jangkauan anda, menguji kondisi, dan menghapus konten jika mereka cocok.

Sesuatu seperti:

For Each cell in SomeRange
  If (cell.value = SomeTest) Then cell.ClearContents
Next

Ya, itu adalah mungkin.

Hal ini dimungkinkan untuk memiliki memusnahkan formula untuk mengevaluasi trueblank jika kondisi terpenuhi. Melewati tes dari ISBLANK formula.

Semua yang anda butuhkan adalah untuk mengatur rentang bernama, mengatakan GetTrueBlank, dan anda akan dapat menggunakan pola berikut hanya seperti dalam pertanyaan anda:

=IF(A1 = "Hello world", GetTrueBlank, A1)

Langkah 1. Letakkan kode ini di Modul VBA.

Function Delete_UDF(rng)
    ThisWorkbook.Application.Volatile
    rng.Value = ""
End Function

Langkah 2. Di Sheet1 di A1 sel tambahkan rentang bernama GetTrueBlank dengan rumus sebagai berikut:

=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")

Yang's ini. Tidak ada langkah-langkah lebih lanjut. Hanya menggunakan self memusnahkan formula. Dimasukkan ke dalam sel, mengatakan B2, rumus berikut:

=IF(A2=0,GetTrueBlank,A2)

Rumus di atas di B2 akan mengevaluasi untuk trueblank, jika anda tipe 0 di A2.

Anda dapat download demonstrasi file disini.

Dalam contoh di atas, mengevaluasi formula untuk trueblank hasil di sel kosong. Memeriksa hasil dengan ISBLANK formula hasil positif dalam KENYATAAN. Ini adalah hara-kiri seperti formula. Rumus menghilang dari sel ketika kondisi terpenuhi. Tujuan tercapai, meskipun anda mungkin mungkin ingin rumus untuk tidak menghilang.

Anda dapat memodifikasi rumus untuk kembali hasilnya dalam sel, sehingga formula tidak akan membunuh dirinya sendiri. Lihat bagaimana untuk mendapatkan UDF mengakibatkan sel.

Saya telah menemukan contoh-contoh yang mendapatkan trueblank sebagai formula hasil diungkapkan oleh FrankensTeam berikut: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

Mungkin ini adalah kecurangan, tetapi bekerja!

Aku juga diperlukan sebuah tabel yang merupakan sumber untuk grafik, dan saya didn't ingin ada yang kosong atau nol sel-sel untuk menghasilkan sebuah titik pada grafik. Memang benar bahwa anda perlu untuk mengatur grafik properti, pilih data, tersembunyi dan sel kosong untuk "menunjukkan sel-sel kosong sebagai Kesenjangan" (klik tombol radio). Yang's langkah pertama.

Kemudian pada sel-sel yang dapat berakhir dengan hasil yang anda don't ingin diplot, masukkan rumus IF pernyataan dengan sebuah NA() hasil =IF($A8>TODAY(),NA(), *formula yang akan diplot*)

Ini tidak memberikan diperlukan grafik dengan tidak ada poin ketika sebuah sel yang tidak valid nilai terjadi. Tentu saja hal ini membuat semua sel-sel dengan nilai yang valid untuk membaca #N/A, dan yang's berantakan.

Untuk membersihkan ini, pilih sel yang berisi nilai yang tidak valid, kemudian pilih conditional formatting - aturan baru. Pilih 'format hanya sel-sel yang mengandung' dan di bawah aturan keterangan pilih 'kesalahan' dari drop down box. Kemudian di bawah format pilih font yang - warna - putih (atau apapun latar belakang anda, warna terjadi). Klik berbagai tombol untuk keluar dan anda harus melihat bahwa sel-sel dengan data yang tidak valid terlihat kosong (mereka benar-benar mengandung #N/A tetapi teks putih pada latar belakang putih tampak kosong.) Kemudian terkait grafik juga tidak menampilkan nilai yang tidak valid poin.

Ini adalah bagaimana saya melakukannya untuk dataset yang saya gunakan. Tampaknya berbelit-belit dan bodoh, tapi itu satu-satunya alternatif untuk belajar bagaimana untuk menggunakan VB solusi yang disebutkan di atas.

1. Saya melakukan "copy" dari semua data, dan disisipkan data sebagai "nilai".
2. Kemudian saya menyoroti disisipkan data dan melakukan "ganti" (Pilih-Jam) sel kosong dengan beberapa surat, saya memilih q karena itu't di mana saja pada lembar data.
3. Akhirnya, saya melakukan yang lain "ganti", dan diganti q dengan apa-apa.

Ini tiga langkah proses berubah semua "kosong" sel-sel menjadi "kosong" sel-sel". Saya mencoba menggabungkan langkah 2 & 3 dengan hanya mengganti sel kosong dengan sel kosong, tapi itu tidak't bekerja-saya harus mengganti sel kosong dengan beberapa jenis teks yang sebenarnya, kemudian ganti teks dengan sel kosong.

Jika tujuannya adalah untuk dapat menampilkan sebuah sel kosong ketika itu sebenarnya memiliki nilai nol, maka alih-alih menggunakan rumus yang mengakibatkan kosong atau sel kosong (karena ada's tidak kosong() fungsi) sebaliknya,

• di mana anda ingin sel kosong, kembali 0, bukan "," dan KEMUDIAN

• mengatur format angka untuk sel-sel seperti, di mana anda akan memiliki untuk datang dengan apa yang anda inginkan untuk bilangan positif dan negatif (dua item pertama dipisahkan oleh semi-kolon). Dalam kasus saya, angka-angka yang saya punya adalah 0, 1, 2... dan aku ingin 0 untuk menunjukkan kosong. (Aku tidak pernah tahu apa yang teks parameter yang digunakan untuk, tapi itu tampaknya diperlukan).

0;0;"";"text"@

Wow, jumlah yang menakjubkan dari orang-orang salah membaca pertanyaan. It's mudah untuk membuat sel terlihat kosong. Masalahnya adalah bahwa jika anda membutuhkan sel kosong, rumus Excel dapat't kembali "nilai" tetapi hanya dapat mengembalikan sebuah nilai. Kembali nol, ruang, atau bahkan "," adalah sebuah nilai.

Jadi, anda harus kembali "sihir nilai" dan kemudian menggantinya dengan tidak ada nilai menggunakan pencarian dan mengganti, atau menggunakan VBA script. Sementara anda bisa menggunakan ruang atau "", saran saya akan menggunakan nilai yang jelas, seperti "NODATE" atau "NONUMBER" atau "XXXXX" sehingga anda dapat dengan mudah melihat di mana hal itu terjadi - itu's cukup sulit untuk menemukan "" dalam spreadsheet.

Mencoba mengevaluasi sel menggunakan LEN. Jika hal ini mengandung formula LEN akan kembali 0. Jika ini berisi teks itu akan kembali lebih besar dari 0.

Begitu banyak jawaban yang mengembalikan nilai yang TERLIHAT kosong tapi tidak benar-benar kosong sebagai sel seperti yang diminta...

Seperti yang diminta, jika anda benar-benar ingin formula yang mengembalikan sel kosong. Hal INI dimungkinkan melalui VBA. Jadi, di sini adalah kode untuk melakukan hal itu. Mulailah dengan menulis rumus untuk mengembalikan #N/A kesalahan di mana pun anda inginkan sel-sel kosong. Kemudian saya solusi otomatis membersihkan semua sel yang berisi bahwa galat #N/A. Tentu saja anda dapat memodifikasi kode untuk secara otomatis menghapus isi sel berdasarkan pada apa pun yang anda suka.

Buka "visual basic viewer" (Alt + F11) Menemukan buku kerja yang menarik dalam proyek explorer dan klik dua kali (atau klik kanan dan pilih view code). Ini akan membuka "lihat kode" jendela. Pilih "buku Kerja" di (Umum) menu dropdown dan "SheetCalculate" di (Deklarasi) menu dropdown.

Paste kode berikut (berdasarkan jawaban oleh J. T. Grimes) dalam Workbook_SheetCalculate fungsi

    For Each cell In Sh.UsedRange.Cells
        If IsError(cell.Value) Then
            If (cell.Value = CVErr(xlErrNA)) Then cell.ClearContents
        End If
    Next

Menyimpan file anda sebagai macro enabled workbook

NB: proses Ini adalah seperti pisau bedah. Ini akan menghapus seluruh isi sel-sel yang mengevaluasi ke galat #N/A jadi sadar. Mereka akan pergi dan anda tidak bisa mendapatkan mereka kembali tanpa pesawat rumus yang mereka gunakan untuk mengandung.

NB2: Jelas anda harus mengaktifkan macro ketika anda membuka file yang lain itu tidak't bekerja dan #N/A kesalahan akan tetap terhapus

Gunakan COUNTBLANK(B1)>0 bukan ISBLANK(B1) dalam JIKA pernyataan.

Tidak seperti ISBLANK(), COUNTBLANK() menganggap "," sebagai kosong dan kembali 1.

Jawaban ini tidak sepenuhnya berurusan dengan OP, tapi ada yang telah beberapa kali saya memiliki masalah yang sama dan mencari jawabannya.

Jika ** anda dapat menciptakan rumus atau data jika diperlukan (dan dari deskripsi anda terlihat seperti jika anda bisa), maka ketika anda siap untuk menjalankan bagian yang memerlukan sel yang kosong akan benar-benar kosong, maka anda dapat memilih wilayah dan jalankan berikut vba macro.

Sub clearBlanks()
    Dim r As Range
    For Each r In Selection.Cells
        If Len(r.Text) = 0 Then
            r.Clear
        End If
    Next r
End Sub

ini akan menghapus dari isi sel yang sedang menampilkan "," atau hanya memiliki formula

Saya dulu kerja berikut ini untuk membuat excel saya terlihat bersih:

Ketika anda membuat perhitungan "," akan memberikan kesalahan sehingga anda ingin memperlakukannya sebagai nomor jadi saya menggunakan nested if pernyataan untuk kembali 0 istead dari "", dan kemudian jika hasilnya 0 persamaan ini akan kembali ""

=IF((IF(A5="",0,A5)+IF(B5="",0,B5)) = 0, "",(IF(A5="",0,A5)+IF(B5="",0,B5)))

Cara ini lembar excel akan terlihat bersih...

Apa yang saya digunakan adalah hack kecil. Saya menggunakan T(1), yang kembali sel kosong. T adalah fungsi pada excel yang mengembalikan argumen jika string dan sel kosong sebaliknya. Jadi, apa yang dapat anda lakukan adalah:

=IF(condition,T(1),value)

Jika anda menggunakan fungsi lookup seperti HLOOKUP dan VLOOKUP untuk membawa data ke lembar kerja anda menempatkan fungsi dalam kurung dan fungsi akan kembali sel kosong bukannya {0}. Misalnya,

Ini akan mengembalikan nilai nol jika lookup sel kosong:

    =HLOOKUP("Lookup Value",Array,ROW,FALSE)

Ini akan mengembalikan sel kosong jika lookup sel kosong:

    =(HLOOKUP("Lookup Value",Array,ROW,FALSE))

Saya don't tahu apakah ini bekerja dengan fungsi-fungsi yang lain...saya belum't mencoba. Saya menggunakan Excel 2007 untuk mencapai hal ini.

Edit

Untuk benar-benar mendapatkan IF(A1="", , ) untuk datang kembali sebagai benar perlu ada dua lookup di sel yang sama dipisahkan oleh suatu &. Mudah cara sekitar ini adalah untuk membuat kedua lookup a cell yang berada di akhir baris dan akan selalu kosong.

Nah sejauh ini adalah yang terbaik yang saya bisa datang dengan.

Ia menggunakan ISBLANK fungsi untuk memeriksa apakah sel adalah benar-benar kosong dalam sebuah pernyataan IF. Jika ada apa-apa di dalam sel, A1 dalam contoh ini, bahkan karakter RUANG, ` maka sel tidakKOSONG` dan perhitungan akan menghasilkan. Ini akan menjaga kesalahan perhitungan ditampilkan sampai anda memiliki nomor untuk bekerja dengan.

Jika sel KOSONG maka perhitungan sel tidak akan menampilkan kesalahan dari perhitungan.Jika sel TIDAK KOSONG maka hasil perhitungan akan ditampilkan. Ini akan melemparkan kesalahan jika data anda yang buruk, ditakuti #DIV/0!

=IF(ISBLANK(A1)," ",STDEV(B5:B14))

Mengubah referensi sel dan formula yang anda butuhkan untuk.

Jawabannya adalah positif - anda tidak dapat menggunakan =IF() fungsi dan meninggalkan sel kosong. "Tampak kosong" tidak sama dengan kosong. Ini adalah rasa malu dua tanda kutip kembali ke belakang tidak menghasilkan sel kosong tanpa menghapus rumus.

Google membawa saya ke sini dengan masalah yang sama, saya akhirnya menemukan solusi yang sesuai dengan kebutuhan saya, itu mungkin bisa membantu orang lain juga...

Saya menggunakan rumus ini:

=IFERROR(MID(Q2, FIND("{",Q2), FIND("}",Q2) - FIND("{",Q2) + 1), "")