Как заменить ячейки, содержащие отдельные буквы, на другое значение?

Question

В Excel, как мне написать формулу, которая просматривает диапазон ячеек (скажем, от A3 до A16), проверяет, являются ли какие-либо из них отдельными буквами, и, если они есть, заменяет эти значения на "0"?

Замена и замена, кажется, работают, только если он смотрит на другие ячейки (если это имеет смысл).

Answer 1

Замена и замена, кажется, работают, только если он смотрит на другие ячейки (если это имеет смысл).

Правильно.  В зависимости от семантики (то есть от того, как вы на это смотрите), формулы (и функции) ничего не меняют .  Они вычисляют значение для ячейки, в которой они находятся.  Это может зависеть от значения (й) в некоторых других ячейках (например, A3:A16), но ячейка не может иметь значение и формулу для манипулирования этим значением.

Что вы можете сделать, это установить B3 в

=IF(AND(LEN(A3)=1, NOT(EXACT(UPPER(A3),LOWER(A3)))), 0, A3)

а затем перетащите / заполните его до B16 .

Объяснение:

• Проверьте длину (LEN) значения, которое мы смотрим (ячейка A3).  Если это один (1), продолжите этот тест, в противном случае, «провал» (подробнее об этом позже).
• Преобразуйте значение в A3 в верхний и нижний регистр.  Если A3 содержит не алфавитный символ, такой как 7 , & или ♥ , преобразование регистра не будет иметь никакого эффекта.  Если это заглавная буква (например, Q), LOWER(A3) вернет свой строчный эквивалент (q), и наоборот.  Поэтому, возможно, если UPPER(A3) ≠ LOWER(A3) , A3 содержит букву.
• К сожалению, для нас в этом случае Excel обычно не учитывает регистр; если вы сравните Q с q , Excel скажет, что они равны.  Поэтому мы используем функцию EXACT() чтобы увидеть, идентичны ли они точно.
• Объединяя вышесказанное, если A3 - это один символ, а этот символ - буква, это то, что (я считаю) вы ищете.  Итак, мы оцениваем B3 до следующего аргумента, а именно 0 .  Если он пустой или содержит более одного символа или не является буквой (т. Е. Тест не пройден), мы отображаем его значение (т. Е. Значение A3).

Выше есть сбой: если A3 пусто, длина равна нулю, и мы получаем последнюю часть формулы - часть A3 - но она будет отображаться как 0 .  Чтобы исправить это, сделайте

=IF(ISBLANK(A3), "", IF(AND(LEN(A3)=1, NOT(EXACT(UPPER(A3),LOWER(A3)))), 0, A3))

в котором говорится, что если A3 пусто, установите B3 пустым; в противном случае перейдите к формуле, как описано выше.

Если вы хотите изменить значения в A3:A16 навсегда, просто скопируйте B3:B16 и вставьте значения в A3:A16 .