Как в Excel данные из нескольких столбцов автоматически размещать в одном друг за другом?
Или же есть ли возможность ставить условие закрепления ячейки в формуле?
Есть 4 столбца с данными. Нужно их в отдельном столбце все поместить воедино, чтобы сначала шли значения из первого, затем второго, третьего и четвёртого (хотя порядок не особо важен).
Сначала подумал, что поможет такое:
=ЕСЛИ(ЕПУСТО(A1);ЕСЛИ(ЕПУСТО(B1);ЕСЛИ(ЕПУСТО(C1);ЕСЛИ(ЕПУСТО(D1);"Конец";D1);C1);B1);A1)
Типа, изначальная мысль, чтобы шла проверка ячейки в первом столбце на пустоту. Если не пусто, то берётся сама ячейка. Если пусто, то начинает проверяться следующий столбец и т.д. Ну в формулах я не силён, так что не смейтесь, что в обратном порядке сделал.
Суть то в том, что когда протягиваешь формулу, то все значения ячеек меняются, и чем дальше, тем бОльший объём данных игнорируется формулой. В моих влажных мечтах представляю инструмент, которым можно было бы задавать условие, при котором ячейка в каждом отдельном аргументе в формуле закрепляется/открепляется. Чтобы изначально менялись только ячейки А. Потом, когда выполняется условие пустоты по ячейкам в столбце А, менялись ячейки столбца Б, пока тот также не иссякнет, разблокировав ячейки Д.. Ну вы поняли. Есть ли что-то подобное или какой-то другой способ объединения данных из разных столбцов? Слияние не подходит. Каждое значение должно быть в отдельной в ячейке и нетронутым
По поводу совмещения данных из нескольких столбцов в одном прекрасно подходит формула
=СЦЕПИТЬ(A1;B1;C1) Ее и проверять на пустоту не нужно - если значения нет, она просто пропустит этот элемент.
Для соединения всех элементов столбца 1 за другим тогда так:
=СЦЕПИТЬ(СЦЕПИТЬ(A1;A2;A3);СЦЕПИТЬ(B1;B2;B3);СЦЕПИТЬ(C1;C2;C3))
Что там за проблема с протягиванием у тебя я честно говоря не очень понял, нужно какое-то более понятное объяснение.
Сцепка не подойдёт. Она совмещает данные в одной ячейке, а мне нужно, чтобы они шли друг за другом по вертикали
Сцепка из 1 2 3 4 делает, а мне надо
1
2
3
4
С протягиванием вот в чём беда - по мере спуска меняются позиции ячеек по вертикали. Т. е. сперва на пустоту проверяется А1, А2, А3.... А500. И вот когда столбец А закончится, т. е. в А501 станет пусто и формула начнёт проверять значения в столбце B, то она начнёт с B501, а всё что выше канет в забытье. То же самое и со следующими столбцами. Закреплять их нелья, ведь в чём смысл - формула тогда застрянет на Б1, бесконечно проверяя её, сколько ни протягивай вниз
Второй вариант тоже не то
Если нужно просто разместить столбцы вертикально один под другим - банально, скопировать/вставить.
У меня появилась идея, но у меня не хватает мозгов её реализовать.
Если в мою формулу добавить ПОИСК поз и некоторые условия? Т. е. сначала просто проверяется, есть ли текст в столбце А. Если да, то ставит ячейку А (без закреплений при протягивании, разумеется). Если пустота, то: подставляет ячейку Б, НО! чтобы это была не ячейка Б501 из-за протягивания, то сделать так, чтобы из этого числа 501 вычиталось лишнее через ПОИСКПОЗ (если я правильно понимаю, как это работает).
А чтобы вычитание не продолжалось, должны быть отдельные условия. Навскидку всё так: если А501 = пустота, и если ячейка выше содержит значение из столбца А, то подставляется Б$1. И ЕСЛИ выше ячейка содержит значение Б1, то подставляется значение Б2 (без закрепления).
Ой, только сейчас понял, что ПОИСКПОЗ не нужен тогда
Тьфу ты, опять "подставляется Б2", которое при протягивании формулы станет Б502 на момент, когда столбец А опустеет. Всё таки поискпоз нужен, но как-то надо его так настроить, чтобы он в первую ячейку после исчерпания столбца А вставлял Б501-500 (ну типа номер текущей строки минус номер предыдущей, утрированно), но чтобы потом он перестал это делать. Вернее, чтобы продолжал вычитаться один и тот же поискпоз - 500. Может через индекс (с указанием содержимого Б1) можно так сделать?
Т. е.: когда А501 = пустота, а ячейка выше содержит значение из А$1:А$5000, то вставляется B$1. Если выше значение из B$1, то = (B1)-ПОИСКПОЗ"ячейка выше" (потому что B1 уже будет на 502), а ЕСЛИ текущий столбец содержит значение из B$1, то = (B1) - ПОИСКПОЗ"ячейка текущего столбца, в которой содержится значение, равное значению B$1". Тогда после проставившегося Б$1 будет вычетание строки, на которой находится Б1 и в следующих строках ПОИСКПОЗ будет делать (B508)-502 и так каждый раз, т. е. всё будет проставляться как положено. Ну и на фоне должна быть, разумеется, проверка пустоты Б, а после неё подставление столбца С с такими же условиями, потом Д... И всё это в правильном порядке и возможно с доп. условиями, чтобы ПОИСКПОЗ, связанный с наличием Б1 не влиял на вычитание позиций в C, D...