Преобразование матрицы в excel в 2 столбеца формулой
Помогите пожалуйста в решении следующей задачи. В ячейках A2:F4 имеется массив (матрица) с четным количеством столбцов. Нужно сделать из него два столбца, чтобы 2 соседние ячейки строки оказались в разных столбцах. В ячейки G2:H10 ввожу формулу массива
={СМЕЩ(матрица;(СТРОКА()-СТРОКА($G$2))/(ЧИСЛСТОЛБ(матрица)/2);(ОСТАТ((СТРОКА()-СТРОКА($G$2));(ЧИСЛСТОЛБ(матрица)/2))*2);1;2)}
В результате получаю #ЗНАЧ! Что я делаю не так. Почему формула ={СМЕЩ(матрица;0;2;1;2)} работает если вводить её в две соседние ячейки, а та что выше не работает? Если делать два вспомогательных столбца для расчета смещения по строкам/столбцам, то формула тоже работает, но хочется обойтись без вспомогательных столбцов
Ответы (2 шт):
фигурная скобка означает что введена формула массива, появляется автоматически после нажатия клавиш Ctrl+Shift+Enter
Фигурные скобки - признак формулы массива. Но это не все... Формулы массива могут возвращать одно значение или массив значений. Формулы массива вводятся двумя способами - в одну ячейку или в диапазон ячеек. Функцией СМЕЩ можно смещаться относительно ячейки или относительно диапазона.
Имеем матрицу
Имеем короткую формулу :
=СМЕЩ(A2;0;2)
Сместиться относительно A2 на 0 строк (без смещения по строкам), 2 столбца. Результат - значение ячейки C2
=СМЕЩ(A2;0;2;1;2)
Из предыдущего примера знаем, что получаем ссылку на C2, но теперь добавился размер диапазона - 1 строка, 2 столбца: C2:D2. Если формулу пересчитать (выделить ее в строке формул и нажать F9), увидим возвращаемый массив, состоящий из значений диапазона:
={3;4}
Вот и подошли к формулам массива.
В одной ячейке не может храниться больше одного значения. Формула может возвратить массив значений, но отображаться будет только одно (для данного примера - из верхней левой ячейки диапазона, но в формулах сложнее это может быть любое значение диапазона, зависит от применяемых функций)
Если ввести последнюю формулу в одну ячейку, то будет отображено только значение из C2. И неважно, введена формула как массивная или как обычная.
Но если выделить две ячейки в строке, вписать формулу в строку формул и завершить ввод формулы Ctrl+Shift+Enter, получим результат - массив значений, возвращаемый формулой, распределится по ячейкам. Естественно, для массива другого размера количество выделенных ячеек должно быть другим.
С диапазоном почти так же, но смещается ссылка на диапазон, а не на ячейку.
=СМЕЩ(матрица;0;2;1;2)
Для указанной матрицы исходные ссылки: A2:F4. Сместились на 0 строк, 2 столбца - получили диапазон C2:H4. Это если не указан размер диапазона. Но формула говорит, что нужно из полученного диапазона взять 2 ячейки в 1 строке. Отсчет ведется от верхней левой ячейки, результат - все те же C2:D2, другие ссылки игнорируются.
Для закрепления полученной информации предлагаю самостоятельно разобраться, как работает формула из заглавного сообщения :)
Есть решение задачи намного проще, с применением обычных формул (без массивного ввода). Да и функция СМЕЩ летуча (пересчитывается при любых изменениях на листе). Если есть замена - СМЕЩ лучше не использовать:
=ИНДЕКС(матрица;СТРОКА(A1)/3,01+1;ОКРУГЛ(ОСТАТ(СТРОКА(A1);3,01);)*2-1)
=ИНДЕКС(матрица;СТРОКА(A1)/3,01+1;ОКРУГЛ(ОСТАТ(СТРОКА(A1);3,01);)*2)
Две формулы для двух столбцов, различие только в определении столбца (-1 есть/нет)
Для применения с матрицами разного размера 3 (тройку) заменить на ЧИСЛСТОЛБ(матрица)/2 или счет значений в одной строке. Еще лучше - заменить на ссылки на ячейку, в которой высчитывается нужное значение