Loncat ke daftar isi utama

Bagaimana cara mengosongkan sel saat menerapkan rumus sampai data dimasukkan di Excel?

Di Excel, jika Anda menerapkan rumus ke rentang kolom, hasilnya akan ditampilkan sebagai nol sementara sel referensi kosong dalam rumus. Tetapi dalam kasus ini, saya ingin membiarkan sel tetap kosong saat menerapkan rumus sampai sel referensi dimasukkan dengan data, apakah ada trik untuk menanganinya?
doc tetap kosong sampai 1

Biarkan sel kosong sampai data masuk


panah gelembung kanan biru Biarkan sel kosong sampai data masuk

Sebenarnya, ada rumus yang bisa membantu Anda mengosongkan sel rumus hingga data dimasukkan ke dalam sel referensi.

Misalnya, di sini untuk menghitung selisih antara kolom Nilai 1 dan kolom Nilai 2 di Kolom Perbedaan, dan Anda ingin membiarkan sel tetap kosong jika ada beberapa sel kosong di kolom Nilai 1 dan kolom Nilai2.

Pilih sel pertama yang Anda inginkan untuk menempatkan hasil terhitung, ketikkan rumus ini = IF (OR (ISBLANK (A2), ISBLANK (B2)), "", A2-B2), dan seret gagang isian ke bawah untuk menerapkan rumus ini ke sel yang Anda butuhkan.
doc tetap kosong sampai 2

Dalam rumus, A2 dan B2 adalah sel referensi dalam rumus yang ingin Anda terapkan, A2-B2 adalah penghitungan yang ingin Anda gunakan.


Batch menyisipkan baris atau kolom kosong dalam interval tertentu dalam rentang Excel

Jika Anda ingin menyisipkan baris kosong di setiap baris lainnya, Anda mungkin perlu memasukkannya satu per satu, tetapi file Sisipkan Baris & Kolom Kosong of Kutools untuk Excel dapat menyelesaikan pekerjaan ini dalam hitungan detik. Klik untuk uji coba gratis 30 hari!
doc memasukkan kolom baris kosong
Kutools for Excel: dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari.

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (40)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
how can i return both blank cells into a blank result

b2="v"
c2=""
b3=""
c3="v"
b4=""
c4=""

if b2="v","x"
if c2="v","y"
This comment was minimized by the moderator on the site
I am in xcel and need the cell where my formula is to remain blank until the data needed in the formula cells is entered. My formula is =A1+5
This comment was minimized by the moderator on the site
so at the minute i am trying to do totals on timesheets but in the formula which i have done E8-D8-0.50 in column I8 is showing as -0.50 how can i get it blank until data is inputted in those cells
This comment was minimized by the moderator on the site
Hi, Ollie, try this formula:
=IF(OR(ISBLANK(E8),ISBLANK(D8)), "",E8-D8-0.5)
This comment was minimized by the moderator on the site
Hi, Carmen,try this formula: =IF(OR(ISBLANK(B2),ISBLANK(C2)), "",B2/C2), it will keep blank until both of column C and column B are filled data.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-keep-cell-blank.png
This comment was minimized by the moderator on the site
Hi, thank you much appreciated but I am still getting an error.

" There's a problem with the formula

Not trying to type a formula?
When the first character is an equal ("=") or a minus ("-") sign, Excel thinks its a formula:
you type: =1+1 the cell shows 2

To get around this try to type an apostrophe first:
'=1+1 the cell shows =1+1

I copied your code and replaced the column numbers.
This comment was minimized by the moderator on the site
Hi there, I want to create an epidemiologic formula to calculate the incidence proportion, which is the number of new cases during a specified time period / the number of people at risk. So essentially, column B divided by column C. However, I am creating the document for students of mine for their exam, so they can just type in the values and get the answer. I tried the formula you provided above =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2) but I keep getting an error. Can anyone assist?

Thanks.
This comment was minimized by the moderator on the site
Ol谩 pessoal, parab茅ns pelo site, tem me ajudado bastante, segue abaixo um problema que ainda n茫o consegui resolver, s茫o v谩rias situa莽玫es em uma formula, segue descri莽茫o do que preciso e dados para criar o ambiente para que possa validar e quem sabe me ajudar com essa fun莽茫o:

Objetivo: fazer com que o Excel atualize a data de vencimento do contrato, somando a quantidade de meses da vig锚ncia, caso a renova莽茫o autom谩tica esteja "SIM" e se tiver "N脗O" ele n茫o somar谩, assim um contrato que foi assinado em janeiro de 2021 e j谩 teve sua renova莽茫o autom谩tica de 12 meses aplicada deve ter a data de vencimento atualizada para janeiro de 2023, se tivermos visualizando a planilha a partir de fevereiro de 2022, como seria essa formula?
Atualmente essa c茅lula D1 j谩 tem a seguinte formula que deve permanecer:
=SE(OU(脡C脡L.VAZIA(A1);脡C脡L.VAZIA(B1));"";(DATAM(A1;B1)))
Essa formula al茅m de somar a data de assinatura do contrato e a vig锚ncia ela s贸 apresenta o resultado se houver informa莽玫es nas c茅lulas A1 e B1, se n茫o deixa vazia

Celular Formato Conte煤do Descri莽茫o
A1 Data Abreviada 12/11/2020 Dt Assinatura contrato
B1 Geral 12 Vig锚ncia (Meses)
C1 Geral Sim ou N茫o Renova莽茫o Autom谩tica
D1 Data Abreviada Formula Data Termino Contrato
This comment was minimized by the moderator on the site
Hi, Andre Oliveria, if cell A1 contains date and contract name, you need this formula
=MID(A1,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),LEN(A1)+1)),LOOKUP(1,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),LEN(A1)+1)))
to extract the date only, then use below formula
=IF(OR(ISBLANK(B1),ISBLANK(C1)),"",IF(EDATE(B1,C1)<=NOW(),EDATE(B1,C1+1), IF(LOWER(D1)="yes", EDATE(B1,C1*2+1), EDATE(B1,C1+1))))
to get the final date.
B1 is the extracted start date, C1 is the months, D1 is "yes"or"no".
This comment was minimized by the moderator on the site
Ol谩 amigos, quero parabenizar a p谩gina pelas informa莽玫es que me ajudaram e resolver um problema, porem agora preciso que um campo de data seja atualizada quando o campo renova莽茫o estiver como SIM, abaixo segue uma descri莽茫o e dados para ajudar, j谩 tentei criar essa formula mais n茫o consegui:

Celular Formato Conte煤do Descri莽茫o
A1 Data Abreviada 12/11/2020 Dt. Assinatura contrato
B1 Geral 12 Vig锚ncia (Meses)
C1 Geral Sim ou N茫o Renova莽茫o Autom谩tica
D1 Data Abreviada Formula Data Termino Contrato

Objetivo: fazer com que o Excel atualize a data de vencimento do contrato, somando a quantidade de meses da vig锚ncia, caso a renova莽茫o autom谩tica esteja "SIM" e se tiver "N脗O" ele n茫o somar谩, assim um contrato que foi assinado em janeiro de 2021 e j谩 teve sua renova莽茫o autom谩tica de 12 meses aplicada deve ter a data de vencimento atualizada para janeiro de 2023, se tivermos visualizando a planilha a partir de fevereiro de 2022, como seria essa formula?
Atualmente essa c茅lula j谩 tem a seguinte formula que deve permanecer:
=SE(OU(脡C脡L.VAZIA(A1);脡C脡L.VAZIA(B1));"";(DATAM(A1;B1)))
Essa formula al茅m de somar a data de assinatura do contrato e a vig锚ncia ela s贸 apresenta o resultado se houver informa莽玫es nas c茅lulas A1 e B1, se n茫o deixa vazia

Se puderem me ajudar agrade莽o
This comment was minimized by the moderator on the site
I have created simple ss to calculated days from a set date in this case E2 with G2 = E2 +30

How do I hide the result in G2 until a date is entered into E2?

Is there a standard way of doing this in excel for multiple cells at one time?
This comment was minimized by the moderator on the site
Hi, HAMISH, take A1 is the cell used to enter date, C1 is the cell that used to add 30, now in cell C1, type the formula =IF(ISBLANK(A1), "", A1+30), then the result in C1 will be shown date + 30 if a date entered in cell A1, otherwise, it keep blank. Please see the gif attached below.
This comment was minimized by the moderator on the site
Thanks a lot!
It was absolutely helpful!
This comment was minimized by the moderator on the site
I have 2 columns one for due date another for overdue.In the overdue column i have due date cell minus Today(). I then drag that down the column. If i haven't yet put a date in the due date cell I would like to add an additonal formula that says if the due date is blank then its 0
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations