Как я могу объединить несколько вложенных функций замены в Excel?

Я пытаюсь настроить функцию для переформатирования строки, которая позже будет объединена. Пример строки может выглядеть так:

  Standard_H2_W1_Launch_123x456_S_40K_AB  

Хотя иногда “ S “не существует, и иногда” 40K “- это” 60K “или нет, а” _AB “также может быть” _CD “или _” EF “. Наконец, необходимо заменить все подчеркивания на дефисы. Конечный продукт должен выглядеть так:

  Standard-H2-W1-Launch-123x456-  

У меня есть четыре функции, которые если запускать один за другим, обо всем этом позаботятся:

  = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, "_ AB", "_"), "_ CD",  "_"), "_ EF", "_") = ЗАМЕНА (ПОДСТАВИТЬ (B2, "_ 40K", ""), "_ 60K", "") = ЗАМЕНЯТЬ (C2, "_ S _", "_") = ПОДСТАВИТЬ  (D2, "_", "-")  

Я пробовал несколько способов объединить их в одну функцию, но я относительно новичок в этом уровне Excel так что я в растерянности. Есть ли способ объединить все это так, чтобы он выполнял одну команду за другой в одной ячейке?

Чтобы просто объединить их, вы можете разместить их все вместе следующим образом:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2, «_ AB», «_»), «_ CD», «_»), «_ EF»  , "_"), "_ 40K", ""), "_ 60K", ""), "_ S _", "_"), "_", "-")  

(обратите внимание, что это может пройти более старое ограничение Excel в 7 вложенных операторов. Я тестирую в Excel 2010

Другой способ сделать это – использовать Функции Left и Right .

Предполагается, что изменяющиеся данные на конце всегда присутствуют и имеют длину 8 символов

  = ЗАМЕНА (LEFT (A2, LEN (A2) -8), "_", "-")  

Это приведет к тому же результирующая строка

Если строка не всегда заканчивается 8 символами, которые вы хотите удалить, вы можете выполнить поиск по “_S” и получить текущее местоположение. Попробуйте следующее:

  = ПОДСТАВИТЬ (LEFT (A2, FIND ("_ S"  , A2,1)), "_", "-")  

2

Спасибо за идею разбить формулу, Вернер!

Использование Alt + Enter позволяет помещать каждый бит сложной формулы замены в отдельные строки: они становятся более понятными и автоматически выравниваются при нажатии Enter.

Просто убедитесь, что у вас достаточно конечных операторов, чтобы соответствовать количеству строк replace ( по обе стороны от ссылки на ячейку.

Как в этом примере:

  = replace (replace (replace (substitute (B11, "(  "," "),") "," ")," ["," "),"] "," ")  

становится:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (B12, "(", ""), ")", ""), "[", ""  ), "]", "")  

который отлично работает как есть, но всегда можно удалить лишние абзацы вручную:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (B12, "(", ""), ")", ""), "[", ""), "]", "")  

Name> substitute ()

[Американское Самоа]> Американское Самоа

отредактировано 27 декабря 2017 г., 17:00

Stevoisiak

15k1616 золотых знаков9191 серебряных знаков163163 бронзовых знака

ответил 25 окт.’16 в 19:06

добавить комментарий |

Спасибо за идею разбить формулу Вернер!

Использование Alt + Enter позволяет поместить каждый бит сложной формулы замены в отдельные строки: они становятся более понятными и автоматически выравниваются при нажатии Enter.

Просто убедитесь, что у вас достаточно конечных операторов, чтобы соответствовать количеству строк replace ( по обе стороны от ссылки на ячейку.

Как в этот пример:

  = substitute (substitute (replace (replace (B11, "(", ""), ")", ""), "[  "," "),"] "," ")  

становится:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (B12, "(", ""), ")", ""), "[", ""), "]", "")  

который отлично работает как есть, но всегда можно удалить лишние абзацы вручную:

  = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (B12, "("  , ""), ")", ""), "[", ""), "]", "")  

Name> replace ( )

[Американское Самоа]> Американское Самоа

2

th>

• вложенность SUBSTITUTE () в строке может быть неприятным, однако его всегда можно расположить:

отредактировано 13 февраля ’19 в 14:33

ответил 11 мая 2018 в 07:47

добавить комментарий |

• вложенность SUBSTITUTE () в строке может быть неприятным, однако его всегда можно расположить:

1

I будет использовать следующий подход:

  = ПОДСТАВИТЬ (LEFT (A2, LEN (A2) -X), "_", "-")  

где X обозначает длину вещей, которые вы не после. А для X я бы использовал

  (ISERROR (FIND ("_ S", A2,1)) * 2) + (  ISERROR (FIND ("_ 40K", A2,1)) * 4) + (ISERROR (FIND ("_ 60K", A2,1)) * 4) + (ISERROR (FIND ("_ AB", A2,1)) *  3) + (ISERROR (FIND ("_ CD", A2,1)) * 3) + (ISERROR (FIND ("_ EF", A2,1)) * 3)  

Приведенный выше ISERROR (FIND ("X",.,.)) * X вернет 0, если X не найден и x (длина X ), если он найден. Таким образом, технически вы обрезаете A2 справа с учетом возможных совпадений.

Преимущество этого подхода над другим упомянутым состоит в том, что более очевидно, какая замена (или удаление) происходит, поскольку “подстановка” не вложена.

ответил 11 марта ’14 в 01:56

добавить комментарий |

Я бы использовал следующий подход:

  = ЗАМЕНА (ЛЕВЫЙ (A2,  LEN (A2) -X), "_", "-")  

где X обозначает длину того, что вам не нужно. . А для X я бы использовал

  (ISERROR (FIND ("_ S", A2,1)) * 2) + (  ISERROR (FIND ("_ 40K", A2,1)) * 4) + (ISERROR (FIND ("_ 60K", A2,1)) * 4) + (ISERROR (FIND ("_ AB", A2,1)) *  3) + (ISERROR (FIND ("_ CD", A2,1)) * 3) + (ISERROR (FIND ("_ EF", A2,1)) * 3)  

Приведенный выше ISERROR (FIND ("X",.,.)) * X вернет 0, если X не найден и x (длина X ), если он найден. Итак, технически вы обрезаете A2 справа с возможными совпадениями.

Преимущество этого подхода над другими упомянутыми заключается в том, что более очевидно, какая замена (или удаление) происходит, поскольку «подстановка» не вложена.

  = ПОДСТАВИТЬ (текст, старый_текст, новый_текст)  если: a = !, b = @, c = #, ... x =>, y = ?, z = ~, "" = "", то: abcdefghijklmnopqrstuvwxyz ... попробуйте это равное:! @ # $% ^  & * () - = + []  {} |;:/? ~ ...;}?  ; * (|] :;  

  --- поэтому, если a = z, b = y, ... y = b, z = a, то результат -  --abcdefghijklmnopqrstuvwxyz = zyxwvutsrqponnopqrstuvwxyz (и z изменяется на a, затем возвращается к z) ... (шаблон начинает давать сбой после m = n, n = m ... и n становится n)  

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАНОВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ  (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАНОВИТЬ (ПОДСТАВИТЬ) («ЗАМЕНЯТЬ» («ЗАМЕНЯТЬ», «b», «c»)  , «#»), «d», «$»), «e», «%»), «f», «^»), «g», «&»), «h», «*»)  , «i», «(»), «j», «)»), «k», «-»), «l», «=»), «m», «+»), «n»,  "["), "o", "]"), "p", ""), "q", "  {")," r ","} ")," s "," | ")," t ","; ")," u ",": ")," v ","/"),"  w ","  ")," y ","? ")," z "," ~ ")," "," ")  

  = ПОДСТАВИТЬ (текст, старый_текст, новый_текст), если: a = !, b = @, c = #,  ... x =>, y = ?, z = ~, "" = "" затем: abcdefghijklmnopqrstuvwxyz ... попробуйте эти равенства:! @ # $% ^ & * () - = + []  {} |;:/? ~ ...;}?  ; * (|] :;  

ПРАВИЛА:

(1) текст для замены находится в ячейке A1
(2) max 64 уровня подстановки (формула ниже имеет только 27 уровней [алфавит + пробел])
(2) “old_text” также не может быть “new_text” (например, если a = z.: Z не может быть “старым текстом” “)

  --- поэтому, если a = z, b = y, ... y = b, z = a, то результат -  --abcdefghijklmnopqrstuvwxyz = zyxwvutsrqponnopqrstuvwxyz (и z изменяется на a, затем возвращается к z) ... (шаблон начинает давать сбой после m = n, n = m ... и n становится n)  

Формула:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАНОВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ  (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАНОВИТЬ (ПОДСТАВИТЬ) («ЗАМЕНЯТЬ» («ЗАМЕНЯТЬ», «b», «c»)  , «#»), «d», «$»), «e», «%»), «f», «^»), «g», «&»), «h», «*»)  , «i», «(»), «j», «)»), «k», «-»), «l», «=»), «m», «+»), «n»,  "["), "o", "]"), "p", ""), "q", "  {")," r ","} ")," s "," | ")," t ","; ")," u ",": ")," v ","/"),"  w ","  ")," y ","? ")," z "," ~ ")," "," ")  

Упрощение формул множественной замены в Excel

Как мне упростить эту формулу, чтобы удалить все повторяющиеся – в Excel?

Ячейка A1: текст ————— текст-текст — текст

Ячейка A2: текст —- текст – текст- –text

Мне нужно, чтобы это было:

Ячейка A1: text-text-text-text

Формула, которую я использую :

= ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A1, «-», «-»), «-», «-»), «-», «-») , “-“, “-“)

Мне было интересно, есть ли способ с той же формулой SUBSTITUTE, чтобы сделать что-то вроде 4 * SUBSTITUTE (A1, “-“, “-“) поэтому он выполняет задание 4 раза без огромной формулы.

Спасибо

в ячейке b1: = SUBSTITUTE (A1, "-", "-") вы можете затем скопировать это в c1, d1, e1 и т. д. В каждой следующей ячейке справа будет на одну ячейку меньше, чем в предыдущей. Добавляйте столбцы, пока не будут устранены все дефисы.

Таким образом, если вы добавляете новую строку, в которой дефисов больше, чем в предыдущих строках, вы можете просто создать дополнительные столбцы вместо изменения формулы, которая получает грязный.

1

Маленькая уловка для одной ячейки решение. Если A1 не содержит пробелов, в A2 введите:

  = SUBSTITUTE (TRIM (SUBSTITUTE  (A1, "-", "")), "", "-")  

Это может обрабатывать любое количество подряд идущих тире:

Если A1 действительно содержит некоторые пробелы, слегка потребуется более сложная формула!

ИЗМЕНИТЬ # 1:

Если A1 содержит пробелы, они должны быть сначала “защищены” чем-то вроде:

  = SUBSTITUTE (A1, "", CHAR (1))  

, а затем при самой внешней замене “защита” должна быть удалена. Окончательная формула:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ОБРЕЗАТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A1, "", СИМВОЛ (1)), "-", "")),  "", "-"), CHAR (1), "")  

Я признаю, что это довольно некрасиво, но он может обрабатывать неограниченное количество тире.

Улучшите этот ответ

отредактировано 17 января ’18 в 13:58

ответил 17 янв. в 13:22

• Привет, спасибо. Как работает формула с пробелами? Спасибо – Жоао 17 янв., 13:52
• @Joao См. мой РЕДАКТИРОВАТЬ №1 – Студент Гэри 17 января 2018, 13:59
• Привет, Гэри, я перепробовал все формулы, которые вы сказали, и все здесь говорят, что чего-то не хватает … – Жоао 17 янв., 18: 49

добавить комментарий |

Маленькая уловка для решения с одной ячейкой. Если A1 не содержит пробелов, в A2 введите:

  = SUBSTITUTE (TRIM (SUBSTITUTE  (A1, "-", "")), "", "-")  

Это может обрабатывать любое количество подряд идущих тире:

Если A1 действительно содержит некоторые пробелы, потребуется немного более сложная формула!

РЕДАКТИРОВАТЬ №1:

Если A1 содержит пробелы, они должны быть сначала «защищены» чем-то вроде:

  = SUBSTITUTE (A1, "", CHAR (1))  

, а затем, в самой внешней замене, “защита” должна быть удалена . Окончательная формула:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ОБРЕЗАТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A1, "", СИМВОЛ (1)), "-", ""))  , "", "-"), CHAR (1), "")  

Я признаю, что это довольно некрасиво, но он может обрабатывать неограниченное количество тире.

0

Если вы думаете, что ваши потребности могут измениться, используйте таблицу поиска

На веб-сайте exceljet.com Дэйв Брунс очень хорошо справился с этим. Это очень хорошо работает, когда вы хотите увидеть ваши исправления заложены в таблицу и редактировать или изменять их.

Это позволяет избежать жесткого кодирования всех исправлений в формулах, которые может быть трудно проверить (и невозможно, когда напечатано на бумаге).

Работает с помощью функции ИНДЕКС . Создайте таблицу поиска :

  до |  после --------- |  --------- ЯБЛОКО |  ALICEBANANA |  БОБЧЕРРИ |  КАРЛКИВИ |  КЕННИ  

• Используйте Insert: Table , чтобы сообщить Excel, что это таблица, и назовите ее исправления :

Затем в основной таблице используйте эту формулу:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (J2, ИНДЕКС (исправления [до], 1), ИНДЕКС (исправления [после], 1)), ИНДЕКС (исправления [  до], 2), ИНДЕКС (исправления [после], 2)), ИНДЕКС (исправления [до], 3), ИНДЕКС (исправления [после], 3)), ИНДЕКС (исправления [до], 4), ИНДЕКС (  исправления [после], 4))  

• Да, вы можете использовать «возврат» (перевод строки) в формуле. Excel игнорирует их, но они упрощают чтение.

• В числах 1,2,3,4 нет волшебства … они номера строк в таблице исправлений для функции INDEX .

• Количество row (4) в поисковой таблице совпадает с числом 4 в формуле. Если вам нужно больше строк, читайте дальше:

Расширяйте при необходимости

Если вам нужно добавить больше строк в свой исправления , это очень просто. Просто измените формулу замены. Предположим, вы хотите увеличить количество строк до восьми:

  = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (J2, INDEX (исправлено [до]  , 1), INDEX (исправления [после], 1)), INDEX (исправления [до], 2), INDEX (исправления [после], 2)), INDEX (исправления [до], 3), INDEX (исправления [  после], 3)), ИНДЕКС (исправления [до], 4), ИНДЕКС (исправления [после], 4)), ИНДЕКС (исправления [до], 5), ИНДЕКС (исправления [после], 5)), ИНДЕКС  (исправления [до], 6), INDEX (исправления [после], 6)), INDEX (исправления [до], 7), INDEX (исправления [после], 7)), INDEX (исправления [до], 8)  , ИНДЕКС (исправления [после], 8))  

Улучшите этот ответ

ответил 10 декабря ’20 в 18:37

добавить ком мент |

Если вы считаете, что ваши потребности могут измениться, используйте таблицу поиска

Веб-сайт У exceljet.com был очень хороший способ справиться с этим Дэйвом Брунсом. Это очень хорошо работает, когда вы хотите видеть свои исправления в виде таблицы и редактировать или изменять их.

Это позволяет избежать жесткого кодирования всего исправления в формулах, которые сложно проверить (и невозможно при печати на бумаге).

Это работает с использованием функции ИНДЕКС . Создайте таблицу поиска :

  до |  после --------- |  --------- ЯБЛОКО |  ALICEBANANA |  БОБЧЕРРИ |  КАРЛКИВИ |  КЕННИ  

• Используйте Insert: Table , чтобы сообщить Excel, что это таблица, и назовите ее исправления :

Затем в основной таблице используйте эту формулу:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (J2, ИНДЕКС (исправления [до], 1), ИНДЕКС (исправления [после], 1)), ИНДЕКС (исправления [  до], 2), ИНДЕКС (исправления [после], 2)), ИНДЕКС (исправления [до], 3), ИНДЕКС (исправления [после], 3)), ИНДЕКС (исправления [до], 4), ИНДЕКС (  исправления [после], 4))  

• Да, вы можете использовать «возврат» (перевод строки) в формуле. Excel игнорирует их, но они упрощают чтение.

• В числах 1,2,3,4 нет волшебства … они номера строк в таблице исправлений для функции INDEX .

• Количество row (4) в поисковой таблице совпадает с числом 4 в формуле. Если вам нужно больше строк, читайте дальше:

Расширяйте при необходимости

Если вам нужно добавить больше строк в свой исправления , это очень просто. Просто измените формулу замены. Допустим, вы хотите увеличить до восьми строк:

  = SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (J2, INDEX (исправления [до]  , 1), INDEX (исправления [после], 1)), INDEX (исправления [до], 2), INDEX (исправления [после], 2)), INDEX (исправления [до], 3), INDEX (исправления [  после], 3)), ИНДЕКС (исправления [до], 4), ИНДЕКС (исправления [после], 4)), ИНДЕКС (исправления [до], 5), ИНДЕКС (исправления [после], 5)), ИНДЕКС  (исправления [до], 6), INDEX (исправления [после], 6)), INDEX (исправления [до], 7), INDEX (исправления [после], 7)), INDEX (исправления [до], 8)  , ИНДЕКС (исправления [после], 8))