Zwróć pustą komórkę z formuły w programie Excel

Muszę zwrócić pustą komórkę z formuły Excela, ale wygląda na to, że Excel traktuje pusty ciąg lub odwołanie do pustej komórki inaczej niż prawdziwa pusta komórka. Zasadniczo potrzebuję czegoś takiego

=IF(some_condition,EMPTY(),some_value)

Próbowałem robić takie rzeczy jak

=IF(some_condition,"",some_value)

i

=IF(some_condition,,some_value)

i zakładając, że B1 jest pustą komórką

=IF(some_condition,B1,some_value)

ale żadna z tych nie wydaje się być prawdziwymi pustymi komórkami, zgaduję, ponieważ są one wynikiem formuły. Czy jest jakiś sposób na zapełnienie komórki tylko wtedy, gdy spełniony jest jakiś warunek, a poza tym utrzymywać komórkę naprawdę pustą?

EDYCJA: zgodnie z zaleceniami próbowałem zwrócić NA (), ale dla moich celów to też nie zadziałało. Czy można to zrobić za pomocą VB?

EDYCJA: Buduję arkusz roboczy, który pobiera dane z innych arkuszy, który jest sformatowany zgodnie z bardzo specyficznymi wymaganiami aplikacji, która importuje dane do bazy danych. Nie mam dostępu do zmiany implementacji tej aplikacji i kończy się niepowodzeniem, jeśli wartość jest „” zamiast faktycznie pusta.

Musisz więc użyć VBA. Będziesz iterować po komórkach w twoim zakresie, przetestować warunek i usunąć zawartość, jeśli są one zgodne.

Coś jak:

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

Excel nie ma na to żadnego sposobu.

Wynikiem formuły w komórce w programie Excel musi być liczba, tekst, wartość logiczna (boolowska) lub błąd. Nie ma typu wartości komórki formuły „pusta” lub „pusta”.

Jedną z praktyk, które obserwowałem, jest używanie NA () i ISNA (), ale to może, ale nie musi rozwiązać problemu, ponieważ istnieje duża różnica w traktowaniu NA () przez inne funkcje (SUM (NA ( )) jest nr Nie dotyczy, natomiast SUMA (A1) wynosi 0, jeśli A1 jest pusta).

Tak to mozliwe.

Możliwe jest, że formuła anihilująca będzie przetwarzana na trueblank, jeśli zostanie spełniony warunek. Przechodzi test ISBLANKformuły.

Wystarczy, powiedzmy GetTrueBlank, ustawić nazwany zakres, a będziesz mógł użyć następującego wzorca, tak jak w pytaniu:

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

Krok 1. Umieść ten kod w module VBA.

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

Krok 2. W Sheet1w A1komórce dodać nazwany zakres GetTrueBlanko następującym wzorze:

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

Otóż ​​to. Nie ma żadnych dalszych kroków. Wystarczy użyć formuły samounicestwiającej. Umieść w komórce, powiedzmy B2, następującą formułę:

=IF(A2=0,GetTrueBlank,A2)

Powyższa formuła w B2zwróci wartość trueblank, jeśli wpiszesz 0 w A2.

Możesz pobrać plik demonstracyjny tutaj .

W powyższym przykładzie ocena formuły na trueblank powoduje, że komórka jest pusta. Sprawdzanie wyników z ISBLANKwynikami formuły pozytywnie w PRAWDZIE. To formuła podobna do hara-kiri. Formuła znika z komórki po spełnieniu warunku. Cel został osiągnięty, chociaż prawdopodobnie możesz chcieć, aby formuła nie zniknęła.

Możesz zmodyfikować formułę, aby zwracała wynik w sąsiedniej komórce, aby formuła się nie zabiła. Zobacz, jak uzyskać wynik UDF w sąsiedniej komórce .

Oto przykłady uzyskania trueblank jako wyniku formuły ujawnionego przez The FrankensTeam tutaj: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another- komórka

Może to oszustwo, ale działa!

Potrzebowałem również tabeli, która jest źródłem wykresu, i nie chciałem, aby żadne puste lub zerowe komórki tworzyły punkt na wykresie. Prawdą jest, że musisz ustawić właściwość wykresu, wybrać dane, ukryte i puste komórki, aby „pokazać puste komórki jako luki” (kliknij przycisk opcji). To pierwszy krok.

Następnie w komórkach, które mogą zakończyć się wynikiem, którego nie chcesz drukować, umieść formułę w instrukcji IF z NA()wynikami takimi jak=IF($A8>TODAY(),NA(), *formula to be plotted*)

Daje to wymagany wykres bez punktów w przypadku wystąpienia nieprawidłowej wartości komórki. Oczywiście pozostawia to wszystkie komórki z tą nieprawidłową wartością do odczytania #N/A, i to jest bałagan.

Aby to wyczyścić, wybierz komórkę, która może zawierać niepoprawną wartość, a następnie wybierz formatowanie warunkowe - nowa reguła. Wybierz „sformatuj tylko komórki zawierające” i pod opisem reguły wybierz „błędy” z rozwijanego menu. Następnie w formacie wybierz czcionkę - kolor - biały (lub inny kolor tła). Kliknij różne przyciski, aby wyjść, a powinieneś zobaczyć, że komórki z nieprawidłowymi danymi wyglądają na puste (faktycznie zawierają, #N/Aale biały tekst na białym tle wygląda na pusty). Następnie połączony wykres również nie wyświetla punktów nieprawidłowej wartości.

Tak zrobiłem dla zestawu danych, którego używałem. Wydaje się skomplikowany i głupi, ale była to jedyna alternatywa dla nauki korzystania z wyżej wspomnianego rozwiązania VB.

1. Zrobiłem „kopię” wszystkich danych i wkleiłem dane jako „wartości”.
2. Następnie zaznaczyłem wklejone dane i wykonałem „zamianę” ( Ctrl- H) pustych komórek jakąś literą, wybrałem, qponieważ nie było go nigdzie w moim arkuszu danych.
3. Wreszcie zrobiłem kolejne „zastąpienie” i zastąpiłem qniczym.

Ten trzyetapowy proces przekształcił wszystkie „puste” komórki w „puste” komórki. Próbowałem połączyć kroki 2 i 3, po prostu zastępując pustą komórkę pustą komórką, ale to nie zadziałało - musiałem zastąpić pusta komórka z jakimś faktycznym tekstem, a następnie zastąp ten tekst pustą komórką.

Jeśli celem jest wyświetlenie komórki jako pustej, gdy w rzeczywistości ma ona wartość zero, zamiast używać formuły, która powoduje, że komórka jest pusta lub pusta (ponieważ nie ma empty()funkcji),

• tam, gdzie chcesz pustą komórkę, zwróć 0zamiast ""i TO

• ustaw format liczb dla komórek w ten sposób, w którym będziesz musiał wymyślić, co chcesz dla liczb dodatnich i ujemnych (pierwsze dwa elementy oddzielone średnikami). W moim przypadku miałam 0, 1, 2 ... i chciałem, żeby 0 było puste. (Nigdy nie dowiedziałem się, do czego służy parametr tekstowy, ale wydawało się to konieczne).

  0;0;"";"text"@
  

Spróbuj ocenić komórkę za pomocą LEN. Jeśli zawiera formułę LEN, zwróci 0. Jeśli zawiera tekst, zwróci więcej niż 0.

Wow, niesamowita liczba osób źle odczytała pytanie. Łatwo jest sprawić, że komórka wygląda na pustą. Problem polega na tym, że jeśli chcesz, aby komórka była pusta, formuły programu Excel nie mogą zwracać „bez wartości”, ale mogą tylko zwracać wartość. Zwracanie zera, spacji lub nawet „” jest wartością.

Musisz więc zwrócić „magiczną wartość”, a następnie zastąpić ją bez wartości za pomocą funkcji wyszukiwania i zamiany lub skryptu VBA. Chociaż możesz użyć spacji lub „”, radzę użyć oczywistej wartości, takiej jak „NODATE” lub „NONUMBER” lub „XXXXX”, abyś mógł łatwo zobaczyć, gdzie to się dzieje - bardzo trudno jest znaleźć „” w arkuszu kalkulacyjnym.

Użyj COUNTBLANK(B1)>0zamiast ISBLANK(B1)w swoim IFwyciągu.

W przeciwieństwie ISBLANK(), COUNTBLANK()uważa ""tak puste i zwraca 1.

Tak wiele odpowiedzi, które zwracają wartość, która WYGLĄDA pusta, ale tak naprawdę nie jest pustą komórką zgodnie z żądaniem ...

Zgodnie z życzeniem, jeśli faktycznie chcesz formuły, która zwraca pustą komórkę. Jest to możliwe dzięki VBA. Oto kod do zrobienia dokładnie tego. Zacznij od napisania formuły zwracającej błąd # N / A wszędzie tam, gdzie chcesz, aby komórki były puste. Następnie moje rozwiązanie automatycznie usuwa wszystkie komórki, które zawierają błąd # N / A. Oczywiście możesz zmodyfikować kod, aby automatycznie usuwać zawartość komórek w oparciu o cokolwiek lubisz.

Otwórz „Visual Basic Viewer” (Alt + F11) Znajdź skoroszyt zainteresowania w Eksploratorze projektu i kliknij go dwukrotnie (lub kliknij prawym przyciskiem myszy i wybierz kod widoku). Otworzy się okno „wyświetl kod”. Wybierz „Skoroszyt” z menu (Ogólne) i „Arkusz kalkulacyjny” z menu (Deklaracje).

Wklej następujący kod (na podstawie odpowiedzi JT Grimes) wewnątrz funkcji Workbook_SheetCalculate

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

Zapisz plik jako skoroszyt z obsługą makr

NB: Ten proces jest jak skalpel. Spowoduje to usunięcie całej zawartości wszystkich komórek, których wynikiem jest błąd # N / A, więc bądź świadomy. Pójdą i nie możesz ich odzyskać bez ponownego wprowadzania formuły, którą kiedyś zawierały.

NB2: Oczywiście musisz włączyć makra podczas otwierania pliku, inaczej plik nie będzie działał, a błędy # N / A pozostaną nieodkryte

Ta odpowiedź nie dotyczy w pełni PO, ale kilka razy miałem podobny problem i szukałem odpowiedzi.

Jeśli możesz odtworzyć formułę lub dane w razie potrzeby (a z opisu wygląda to tak, jakbyś mógł), to kiedy będziesz gotowy do uruchomienia części wymagającej pustych komórek, aby były faktycznie puste , możesz wybrać region i uruchom następujące makro vba.

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

spowoduje to usunięcie zawartości dowolnej komórki, która jest obecnie wyświetlana ""lub ma tylko formułę

Użyłem małego hacka. Użyłem T (1), który zwrócił pustą komórkę. T jest funkcją w programie Excel, która zwraca argument, jeśli w przeciwnym razie jest łańcuchem i pustą komórką. Co możesz zrobić, to:

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

Wykorzystałem następujące rozwiązania, aby mój program Excel wyglądał na czystszy:

Kiedy wykonujesz jakiekolwiek obliczenia, błąd „” da ci błąd, więc chcesz potraktować go jako liczbę, więc użyłem zagnieżdżonej instrukcji if, aby zwrócić wartość 0, a następnie, jeśli wynikiem jest 0, równanie zwróci „”

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

W ten sposób arkusz programu Excel będzie wyglądał czysto ...

Jeśli używasz funkcji wyszukiwania, takich jak WYSZUKAJ.PIONOWO i WYSZUKAJ.PIONOWO, aby wprowadzić dane do arkusza roboczego, umieść funkcję w nawiasach, a funkcja zwróci pustą komórkę zamiast {0}. Na przykład,

Zwróci wartość zero, jeśli komórka odnośnika jest pusta:

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

Zwróci pustą komórkę, jeśli komórka odnośnika jest pusta:

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

Nie wiem, czy to działa z innymi funkcjami ... Nie próbowałem. Korzystam z programu Excel 2007, aby to osiągnąć.

Edytować

Aby faktycznie otrzymać JEŻELI (A1 = "",,), aby powrócił jako prawdziwy, muszą być dwa wyszukiwania w tej samej komórce oddzielonej znakiem &. Najprostszym sposobem na to jest uczynienie drugiego wyszukiwania komórką, która znajduje się na końcu wiersza i zawsze będzie pusta.

Jak dotąd jest to najlepsze, co mogłem wymyślić.

Używa ISBLANKfunkcji do sprawdzenia, czy komórka jest naprawdę pusta w IFinstrukcji. Jeśli w komórce jest coś, A1w tym przykładzie nawet znak SPACJA, to komórka nie jest EMPTYi obliczenie zostanie wykonane. Dzięki temu błędy obliczeniowe nie będą wyświetlane, dopóki nie będziesz mieć liczb do pracy.

Jeśli komórka jest, EMPTYwówczas komórka obliczeniowa nie wyświetli błędów z obliczeń. Jeśli komórka jest, NOT EMPTYwówczas zostaną wyświetlone wyniki obliczeń. To spowoduje błąd, jeśli twoje dane są złe, przerażające #DIV/0!

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

Zmień odwołania do komórek i formułę zgodnie z potrzebami.

Odpowiedź jest pozytywna - nie można użyć funkcji = IF () i pozostawić komórkę pustą. „Wygląda na pustą” to nie to samo co puste. Szkoda, że ​​dwa cudzysłowy nie dają pustej komórki bez wymazania formuły.

Google przywiózł mnie tutaj z bardzo podobnym problemem, w końcu wymyśliłem rozwiązanie, które odpowiada moim potrzebom, może też pomóc komuś innemu ...

Użyłem tej formuły:

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