Поиск строки в строке и удаление ее в excel

У меня есть таблица, похожая на приведенную ниже

si  id  a   b   c   d   e
1   123 abc bcd abc def efg
2   234 bcd cde def efg fgh
3   345 cde efg efg abc ghi

Что я хочу сделать, так это найти, присутствует ли строка «abc» в строке, и если она присутствует, удалите ее и скопируйте последующие столбцы в свою позицию, т.е. в первой строке «abc» присутствует в столбце A, поэтому я хочу удалить его и переместите столбец B-E в столбец A-D. Во 2-м ряду «abc» отсутствует, поэтому этот ряд следует сохранить как есть, но в 3-м ряду «abc» находится в столбце D, поэтому его следует удалить, а «ghi» следует вставить на его место, оставив таким образом столбец Е пустой.

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

Есть ли способ использовать формулы excel, а не vba.


excel excel-formula
person Raj    schedule 19.07.2017    source источник
comment
Вы не можете удалить ячейку и переместить другие с помощью формул.   -  person Scott Craner    schedule 19.07.2017
comment
@ScottCraner Я думаю, ты сможешь. Вам нужно использовать формулу на cpearson.com/excel/NoBlanks.aspx, но изменить это так, что abc выходит пустым. Если разберусь, напишу ответ.   -  person Jerry Jeremiah    schedule 19.07.2017
comment
В подходе @JerryJeremiah Chip используется вспомогательный столбец с формулой. Я со Скоттом в этом. Формула не может изменить значение других ячеек.   -  person teylyn    schedule 19.07.2017
comment
@teylyn Он говорит, что я смог сделать это с помощью ПОИСКПОЗ, поэтому он, должно быть, неправильно объясняет это ...   -  person Jerry Jeremiah    schedule 19.07.2017

Ответы (1)


arrow_upward
2
arrow_downward

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

Начнем с этого:

   |   A   |   B   |   C   |   D   |   E   |   F   |   G   |
---+-------+-------+-------+-------+-------+-------+-------+--
 1 |    si      id       a       b       c       d       e
 2 |     1     123     abc     bcd     abc     def     efg
 3 |     2     234     bcd     cde     def     efg     fgh
 4 |     3     345     cde     efg     efg     abc     ghi
 5 |
 6 |
 7 |
 8 |
 9 |
10 |

Шаг 1: в A6 поставьте =A1

Шаг 2. Скопируйте A6 в B6:G6, A7:A10 и B7:B10.

Теперь лист выглядит так:

   |   A   |   B   |   C   |   D   |   E   |   F   |   G   |
---+-------+-------+-------+-------+-------+-------+-------+--
 1 |    si      id       a       b       c       d       e
 2 |     1     123     abc     bcd     abc     def     efg
 3 |     2     234     bcd     cde     def     efg     fgh
 4 |     3     345     cde     efg     efg     abc     ghi
 5 |
 6 |    si      id       a       b       c       d       e
 7 |     1     123
 8 |     2     234
 9 |     3     345
10 |

Шаг 3: В C7 введите эту формулу массива (не забудьте нажать Alt-Shift-Enter вместо простого нажатия Enter):

=IFERROR(INDEX($C2:$G2,1,SMALL(IF($C2:$G2<>"abc",COLUMN($C2:$G2)-2),COLUMN(A1))),"")

Шаг 4: скопируйте C7 в D7:G7 и C8:G9

Теперь лист выглядит так:

   |   A   |   B   |   C   |   D   |   E   |   F   |   G   |
---+-------+-------+-------+-------+-------+-------+-------+--
 1 |    si      id       a       b       c       d       e
 2 |     1     123     abc     bcd     abc     def     efg
 3 |     2     234     bcd     cde     def     efg     fgh
 4 |     3     345     cde     efg     efg     abc     ghi
 5 |
 6 |    si      id       a       b       c       d       e
 7 |     1     123     bcd     def     efg
 8 |     2     234     bcd     cde     def     efg     fgh
 9 |     3     345     cde     efg     efg     ghi
10 |

Как это работает:

=IF($C2:$G2<>"abc",COLUMN($C2:$G2)-2) генерирует массив значений с числом, если значение не равно abc, и значением FALSE, если значение равно abc. -2 важен, потому что слева от данных есть два столбца заголовков.

=SMALL(IF($C2:$G2<>"abc",COLUMN($C2:$G2)-2),COLUMN(A1)) возвращает n-е наименьшее число в возвращенном массиве — он пропускает все значения FALSE.

=INDEX($C2:$G2,1,SMALL(IF($C2:$G2<>"abc",COLUMN($C2:$G2)-2),COLUMN(A1))) возвращает значение местоположения этого n-го наименьшего числа - если там нет значения из-за отфильтрованных элементов, возвращается ошибка

=IFERROR(INDEX($C2:$G2,1,SMALL(IF($C2:$G2<>"abc",COLUMN($C2:$G2)-2),COLUMN(A1))),"") возвращает значение и заменяет ошибки пустой ячейкой.

person Jerry Jeremiah    schedule 19.07.2017