Функция ТРАНСПОРТ в Excel

Что делает функция транспонирования в Excel?

Функция транспонирования в Excel используется для поворота значений или переключения значений из строк в столбцы и из столбцов в строки, так как эта функция принимает массив ячеек в качестве аргумента, поэтому для ее выполнения нам нужно нажать CSE или Control + Shift + Enter, сначала выберите точный размер диапазона, который необходимо транспонировать, а затем используйте формулу транспонирования.

Синтаксис

Ниже представлена ​​формула ПРОЗРАЧНОСТИ в excel.

Перенести формулу в Excel

Аргументы

массив: Необходимые. Диапазон ячеек, которые нужно транспонировать.

Это функция массива.

Как использовать функцию TRANSPOSE в Excel? (с примерами)

Вы можете скачать этот шаблон Excel с функцией TRANSPOSE здесь — Шаблон Excel с функцией TRANSPOSE

Пример # 1

Исходные данные, которые вы хотите транспонировать, находятся в формате A3: B8.

транспонировать пример 1

  • Шаг 1: Выберите диапазон, в котором вы хотите транспонировать значение, здесь D6: I7.

транспонировать пример 1.1

  • Шаг 2: Теперь введите формулу ТРАНСПОЗИЦИИ в выбранной области и нажмите CTRL + SHIFT + ENTER (или COMMAND + SHIFT + ENTER в Mac).

= ТРАНСПОРТ (A3: B7)

транспонировать пример 1-2

Вы получите транспонированный выход в D6: I7, как показано ниже.

Пример функции транспонирования в Excel 1-3

В этом случае D3: I4 — это транспонированный вывод копирования-вставки, а D6: I7 — транспонированный вывод.

Обратите внимание, что функция ТРАНСПОРТА в Excel

{= ТРАНСПОРТ (A3: B7)}

Фигурные скобки {}, показанные выше, указывают на то, что функция была введена как формула массива.

Если вам нужно изменить какое-либо значение в исходных данных, скажем, B8 = SUM (B3: B7), транспонированный вывод, в котором вы использовали TRANSPOSE, будет автоматически обновлен, но не в том случае, когда вы использовали копию — вариант вставки.

Пример 1-6 функции транспонирования в Excel

Его также можно использовать вместе с другими функциями для получения желаемого результата. Давайте посмотрим на несколько примеров этого.

Пример # 2

Предположим, у вас есть список учеников и их оценки, как показано ниже. Некоторые студенты не сдавали экзамены. Следовательно, для этих студентов столбец с оценками остается пустым.

Пример 2 функции Excel TRANSPOSE

Вы хотите транспонировать эти данные, и когда ячейка пуста, вы хотите поместить в эту ячейку «Отсутствует».

Вы можете сделать это, используя Transpose вместе с функцией IF в excel. Для этого вы можете использовать следующую формулу TRANSPOSE в excel:

= ТРАНСПОРТИРОВАТЬ (ЕСЛИ (B3: D10 = ””, ”ОТСУТСТВУЕТ”, B3: D10))

«ЕСЛИ (B3: D10 =» «,« ОТСУТСТВУЕТ »» означает, что если какая-либо ячейка в диапазоне B3: D10 пуста, вместо нее будет помещено «Отсутствует». Это приведет к транспонированию данного диапазона.

ТРАНСПОРТ Пример 2-1

Предположим, что в этом списке, если какой-либо учащийся набрал менее 70 баллов или не явился на экзамен, это считается ОТСУТСТВИЕМ, вы можете изменить Формулу ТРАНСПЗИЦИИ следующим образом:

= ТРАНСПОРТИРОВАТЬ (ЕСЛИ (B3: D10 <70; «НЕИСПРАВНОСТЬ»; B3: D10)

и вы получите окончательный результат, как показано ниже.

Пример функции Excel TRANSPOSE 2-2

Пример # 3

Иногда возникает необходимость добавить некоторые символы к существующим вместе с транспонированием. Предположим, у вас есть список идентификаторов в B4: B7, как показано ниже.

ТРАНСПОРТ Пример 3

Вы хотите переставить идентификаторы и добавить к ним префикс «ID». Вы можете сделать это с помощью формулы в excel:

= TRANSPOSE («ID» & B4: B7)

Пример функции TRANSPOSE 3-2

Это транспонирует данные и добавит префикс к каждому содержимому ячейки.

ТРАНСПОРТ Пример 3-3

Вы также можете добавить любое значение в качестве суффикса, используя приведенную выше формулу ТРАНСПОРТИРОВКИ. Это может быть полезно при использовании функции транспонирования для объединения слов, указанных в разных строках, в одну ячейку. Для этого вы можете выполнить следующие шаги:

  • Введите синтаксис: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Не нажимайте Enter.
  • Выберите функцию транспонирования в Excel, как показано ниже.

ТРАНСПОРТ Пример 3-4

  • Нажмите клавишу F9.

Пример 3-5

  • Удалите {} из формулы.

Пример 3-6

  • Нажмите Ввод.

Пример 3-7

Он объединит ячейки в одну ячейку.

Пример # 4

Предположим, у вас есть данные библиотеки, в которой несколько предметов расположены на полке, и есть разные полки, как показано ниже.

Пример 4

Вы можете захотеть получить местоположение любого из субъектов (указанное в H3: H11), используя эти данные (B4: E6).

Пример 4-1

Вы можете сделать это, используя синтаксис

= ИНДЕКС ($ B $ 4: $ B $ 6, ПОИСКПОЗ (1, MMULT (- ($ C $ 4: $ E $ 6 = ), TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)), 0 ))

Давайте посмотрим, как работает эта формула TRANSPOSE в Excel.

  • — ($ C $ 4: $ E $ 6 = <Тема>)

Если в H3 задано , это будет — ($ C $ 4: $ E $ 6 = H3).

Это формирует массив из 1 и 0, чтобы указать наличие или отсутствие значения. Для биологии в H3 он сформирует массив как: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0))

Он создает массив из 3 строк в столбце, а степень 0 гарантирует, что числа будут преобразованы в 1. Результатом этой функции будет {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 = <Тема>), TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0))

MMULT в excel умножает вывод A и B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) дает результат {1; 0; 0}

  • ПОИСКПОЗ (1; ММУЛЬТ (<..>); 0)

Результату C будет соответствовать 1. MATCH (1, {1; 0; 0}, 0) возвращает позицию 1.

  • ИНДЕКС ($ B $ 4: $ B $ 6; ПОИСКПОЗ (<…>, 0))

Он будет определять значение ячейки, для которой функция сопоставления указала позицию. ИНДЕКС ($ B $ 4: $ B $ 6, 1) вернет A1.

Пример 4-2

Аналогично для предмета «География» это будет:

  1. — ($ C $ 4: $ E $ 6 = D6) возвращает {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) возвращает {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) возвращает {0; 0; 1}
  4. MATCH (1, {0; 0; 1}, 0) возвращает 3
  5. INDEX ($ B $ 4: $ B $ 6, 3) возвращает A3.

То, что нужно запомнить

  1. Опция копирования-вставки создает дубликаты.
  2. Эта функция связывает данные с источником.
  3. Если количество выбранных строк и столбцов не равно количеству столбцов и строк исходных данных, будет выдана ошибка #VALUE.
  4. После ввода ни одна отдельная ячейка, являющаяся частью этой функции, не может быть изменена.
  5. Нажмите CTRL + SHIFT + ENTER, чтобы ввести формулу в Excel.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

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

Ваш адрес email не будет опубликован.