Excel의 수식에서 빈 셀 반환

Excel의 수식에서 빈 셀 반환

---

Excel 수식에서 빈 셀을 반환해야하지만 Excel은 빈 문자열이나 빈 셀에 대한 참조를 실제 빈 셀과 다르게 처리합니다. 본질적으로 나는 다음과 같은 것이 필요합니다.

=IF(some_condition,EMPTY(),some_value)

나는 다음과 같은 일을하려고했습니다.

=IF(some_condition,"",some_value)

과

=IF(some_condition,,some_value)

B1이 빈 셀이라고 가정

=IF(some_condition,B1,some_value)

그러나 이들 중 어느 것도 진정한 빈 셀 인 것처럼 보이지 않습니다. 공식의 결과이기 때문에 추측하고 있습니다. 어떤 조건이 충족되는 경우에만 셀을 채우고 셀을 실제로 비워 둘 수있는 방법이 있습니까?

편집 : 권장대로 NA ()를 반환하려고했지만 내 목적으로도 작동하지 않았습니다. VB로 이것을 수행하는 방법이 있습니까?

편집 : 데이터를 데이터베이스로 가져 오는 응용 프로그램의 요구 사항에 맞게 서식이 지정된 다른 워크 시트에서 데이터를 가져 오는 워크 시트를 작성 중입니다. 이 응용 프로그램의 구현을 변경할 수있는 액세스 권한이 없으며 값이 실제로 비어 있지 않고 ""인 경우 실패합니다.

---

VBA그런 다음 을 사용해야 합니다. 범위의 셀을 반복하고 조건을 테스트하고 일치하는 경우 내용을 삭제합니다.

다음과 같은 것 :

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

---

Excel에서는이를 수행 할 방법이 없습니다.

Excel에서 셀의 수식 결과는 숫자, 텍스트, 논리 (부울) 또는 오류 여야합니다. "빈"또는 "공백"의 수식 셀 값 유형이 없습니다.

내가 본 한 가지 연습은 NA () 및 ISNA ()를 사용하는 것이지만 NA ()가 다른 함수로 처리되는 방식에 큰 차이가 있기 때문에 문제를 해결할 수도 있고 그렇지 않을 수도 있습니다 (SUM (NA ( ))는 # N / A이고 A1이 비어 있으면 SUM (A1)은 0입니다.

---

네 가능합니다.

조건이 충족되면 소멸 공식을 trueblank로 평가할 수 있습니다. ISBLANK공식 테스트를 통과했습니다 .

명명 된 범위를 설정하기 GetTrueBlank만하면됩니다 (예 : 질문에서와 같이 다음 패턴을 사용할 수 있음).

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

1 단계. 이 코드를 VBA의 모듈에 넣습니다.

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

2 단계 에서 Sheet1의 A1셀 범위라는 추가 할 GetTrueBlank다음 식 :

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

That's it. There are no further steps. Just use self annihilating formula. Put in cell, say B2, the following formula:

=IF(A2=0,GetTrueBlank,A2)

The above formula in B2 will evaluate to trueblank, if you type 0 in A2.

You can download a demonstration file here.

In the example above, evaluating formula to trueblank results in an empty cell. Checking result with ISBLANK formula results positively in TRUE. This is hara-kiri like formula. The formula disappears from cell when condition is met. The goal is reached, although you probably might want the formula not to disappear.

You may modify the formula to return result in adjacent cell, so that the formula will not kill itself. See how to get UDF result in adjacent cell.

I have come across the examples of getting a trueblank as a formula result revealed by The FrankensTeam here: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

---

Maybe this is cheating, but it works!

I also needed a table that is the source for a graph, and I didn't want any blank or zero cells to produce a point on the graph. It is true that you need to set the graph property, select data, hidden and empty cells to "show empty cells as Gaps" (click the radio button). That's the first step.

Then in the cells that may end up with a result that you don't want plotted, put the formula in an IF statement with an NA() results such as =IF($A8>TODAY(),NA(), *formula to be plotted*)

This does give the required graph with no points when an invalid cell value occurs. Of course this leaves all cells with that invalid value to read #N/A, and that's messy.

To clean this up, select the cell that may contain the invalid value, then select conditional formatting - new rule. Select 'format only cells that contain' and under the rule description select 'errors' from the drop down box. Then under format select font - colour - white (or whatever your background colour happens to be). Click the various buttons to get out and you should see that cells with invalid data look blank (they actually contain #N/A but white text on a white background looks blank.) Then the linked graph also does not display the invalid value points.

---

This is how I did it for the dataset I was using. It seems convoluted and stupid, but it was the only alternative to learning how to use the VB solution mentioned above.

1. I did a "copy" of all the data, and pasted the data as "values".
2. Then I highlighted the pasted data and did a "replace" (Ctrl-H) the empty cells with some letter, I chose q since it wasn't anywhere on my data sheet.
3. Finally, I did another "replace", and replaced q with nothing.

This three step process turned all of the "empty" cells into "blank" cells". I tried merging steps 2 & 3 by simply replacing the blank cell with a blank cell, but that didn't work--I had to replace the blank cell with some kind of actual text, then replace that text with a blank cell.

---

If the goal is to be able to display a cell as empty when it in fact has the value zero, then instead of using a formula that results in a blank or empty cell (since there's no empty() function) instead,

• where you want a blank cell, return a 0 instead of "" and THEN

• set the number format for the cells like so, where you will have to come up with what you want for positive and negative numbers (the first two items separated by semi-colons). In my case, the numbers I had were 0, 1, 2... and I wanted 0 to show up empty. (I never did figure out what the text parameter was used for, but it seemed to be required).

  0;0;"";"text"@
  

---

Try evaluating the cell using LEN. If it contains a formula LEN will return 0. If it contains text it will return greater than 0.

---

Wow, an amazing number of people misread the question. It's easy to make a cell look empty. The problem is that if you need the cell to be empty, Excel formulas can't return "no value" but can only return a value. Returning a zero, a space, or even "" is a value.

So you have to return a "magic value" and then replace it with no value using search and replace, or using a VBA script. While you could use a space or "", my advice would be to use an obvious value, such as "NODATE" or "NONUMBER" or "XXXXX" so that you can easily see where it occurs - it's pretty hard to find "" in a spreadsheet.

---

So many answers that return a value that LOOKS empty but is not actually an empty as cell as requested...

As requested, if you actually want a formula that returns an empty cell. It IS possible through VBA. So, here is the code to do just exactly that. Start by writing a formula to return the #N/A error wherever you want the cells to be empty. Then my solution automatically clears all the cells which contain that #N/A error. Of course you can modify the code to automatically delete the contents of cells based on anything you like.

Open the "visual basic viewer" (Alt + F11) Find the workbook of interest in the project explorer and double click it (or right click and select view code). This will open the "view code" window. Select "Workbook" in the (General) dropdown menu and "SheetCalculate" in the (Declarations) dropdown menu.

Paste the following code (based on the answer by J.T. Grimes) inside the Workbook_SheetCalculate function

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

Save your file as a macro enabled workbook

NB: This process is like a scalpel. It will remove the entire contents of any cells that evaluate to the #N/A error so be aware. They will go and you cant get them back without reentering the formula they used to contain.

NB2: Obviously you need to enable macros when you open the file else it won't work and #N/A errors will remain undeleted

---

Use COUNTBLANK(B1)>0 instead of ISBLANK(B1) inside your IF statement.

Unlike ISBLANK(), COUNTBLANK() considers "" as empty and returns 1.

---

This answer does not fully deal with the OP, but there are have been several times I have had a similar problem and searched for the answer.

If you can recreate the formula or the data if needed (and from your description it looks as if you can), then when you are ready to run the portion that requires the blank cells to be actually empty, then you can select the region and run the following 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

this will wipe out off of the contents of any cell which is currently showing "" or has only a formula

---

I used the following work around to make my excel looks cleaner:

When you make any calculations the "" will give you error so you want to treat it as a number so I used a nested if statement to return 0 istead of "", and then if the result is 0 this equation will return ""

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

This way the excel sheet will look clean...

---

If you are using lookup functions like HLOOKUP and VLOOKUP to bring the data into your worksheet place the function inside brackets and the function will return an empty cell instead of a {0}. For Example,

This will return a zero value if lookup cell is empty:

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

This will return an empty cell if lookup cell is empty:

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

I don't know if this works with other functions...I haven't tried. I am using Excel 2007 to achieve this.

Edit

To actually get an IF(A1="", , ) to come back as true there needs to be two lookups in the same cell seperated by an &. The easy way around this is to make the second lookup a cell that is at the end of the row and will always be empty.

---

Well so far this is the best I could come up with.

It uses the ISBLANK function to check if the cell is truly empty within an IF statement. If there is anything in the cell, A1 in this example, even a SPACE character, then the cell is not EMPTY and the calculation will result. This will keep the calculation errors from showing until you have numbers to work with.

If the cell is EMPTY then the calculation cell will not display the errors from the calculation.If the cell is NOT EMPTY then the calculation results will be displayed. This will throw an error if your data is bad, the dreaded #DIV/0!

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

Change the cell references and formula as you need to.

---

대답은 긍정적입니다. = IF () 함수를 사용하고 셀을 비워 둘 수 없습니다. "비어있는 것"은 비어있는 것과 다릅니다. 수식을 지우지 않고 빈 셀을 만들지 않고 두 따옴표를 연속으로 사용하는 것은 수치입니다.

---

내가 사용한 것은 작은 핵이었다. 빈 셀을 반환하는 T (1)을 사용했습니다. T는 문자열이고 빈 셀이면 인수를 반환하는 Excel의 함수입니다. 따라서 할 수있는 일은 :

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

---

Google은 매우 비슷한 문제로 나를 여기에 데려 왔습니다. 마침내 내 요구에 맞는 솔루션을 찾았습니다. 다른 사람도 도울 수 있습니다 ...

나는이 공식을 사용했다 :

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

참고 URL : https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel