Как переносить данные с помощью Excel

есть ли способ, которым я могу сделать следующее преобразование?

введите описание изображения здесь

Nov-2010 1195 1175 1676 36500.5 $33,607 Dec-2010 1215 1189 1775 46866.5 $38,714 

в

 Nov-2010 1195 1175 1676 36500.5 $33,607 Dec-2010 1215 1189 1775 46866.5 $38,714 

Нужно ли сначала заполнять пробелы?

Я предполагаю, что не каждый месяц имеет ровно пять чисел. Если вам это нужно, вам не нужны какие-либо дополнительные проверки, и вы можете просто вставить более простую формулу в F4: I9 (объяснено ниже). Но моя формула работает, если один месяц имеет 2 значения, а другой месяц – 12 значений.

Если исходные данные находятся в A1: B10, а ваш транспонированный заголовок находится в E4: E5, как показано на скриншоте таблицы, вы можете поместить его в F4 и скопировать в F4: Z10

 =IF(IF(ISERROR(MATCH($E5,$A:$A,0)>MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-1),MATCH(9.99999999999999E+307,$B:$B)+1>=MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4),MATCH($E5,$A:$A,0)>MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-1),OFFSET($B$1,MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-2,0),"") 

Это работает:

Интегральный бит равен OFFSET($B$1,MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-2,0) который получает значение из определенной ячейки на основе значения в Е4. (Это то, что вы вставляете в F4: I9, если каждый месяц имеет ровно пять значений.) Но этого недостаточно, если каждый месяц может иметь различное количество значений. В этом случае нам нужно знать, как остановить его от получения большего количества значений, чем мы хотим:

Если в E ниже текущей строки, мы можем использовать IF(MATCH($E5,$A:$A,0)>MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-1,OFFSET($B$1,MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-2,0),"") для необязательного отображения значения.

Если нет другого месяца в E ниже текущей строки, мы можем использовать IF(MATCH(9.99999999999999E+307,$B:$B)+1>=MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4),OFFSET($B$1,MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-2,0),"") для необязательного отображения стоимость.

Но нам нужны оба, поэтому мы должны использовать if, чтобы решить, какой из них использовать, и в итоге получим большой. Здесь для ясности он распространяется по нескольким строкам – вы можете вставить его в панель формул, как это, но вы не можете вставить его в ячейку напрямую из-за фидов строк.

 =IF( IF( ISERROR(MATCH($E5,$A:$A,0)>MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-1), MATCH(9.99999999999999E+307,$B:$B)+1>=MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4), MATCH($E5,$A:$A,0)>MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-1), OFFSET($B$1,MATCH($E4,$A:$A,0)+COLUMN()-COLUMN($E4)-2,0), "") 

Nov-2010 должен быть выше 1195 , чтобы использовать команду Copy, Paste Special, Transpose в листе. Функция OFFSET может работать с небольшими математическими строками и столбцами, но это функция volatile¹ и лучше всего подходит, когда ничего не будет достаточно. Функция INDEX обеспечивает альтернативную альтернативу OFFSET.

Ваши выборочные данные аккуратно предлагают 5 связанных записей в столбце B для каждой даты в столбце A. По моему опыту, выборочные данные не всегда отражают фактические данные. Вам нужно будет заполнить каждую строку до максимума, чтобы какая-либо одна дата могла иметь связанные значения.

В E4: F4 в качестве стандартных формул,

 'in E4 =IFERROR(INDEX(A:A, AGGREGATE(15, 6, ROW(A$1:INDEX(A:A, MATCH(1E+99,A:A )))/SIGN(LEN(A$1:INDEX(A:A, MATCH(1E+99,A:A )))), ROW(1:1))), "") 'in F4 =IFERROR(INDEX(INDEX($B:$B, MATCH($E4,$A:$A, 0)):INDEX($B:$B, IFERROR(MATCH($E5,$A:$A, 0)-1, MATCH(1E+99,$B:$B ))), COLUMN(A:A)), "") 

Заполните E4 до тех пор, пока не закончите даты из столбца A для извлечения. Заполните F4 до тех пор, пока вы не достигнете максимального количества связанных значений, которые могла бы иметь любая дата, а затем заполнить до дат в столбце E.

aggregate_offset


¹ Летучие функции пересчитываются всякий раз, когда что-либо во всей книге изменяется, а не только когда что-то, что влияет на их результат, изменяется. Примерами летучих функций являются КОСВЕННЫЕ , СМЕЩЕНИЕ , СЕГОДНЯ , ТЕПЕРЬ , RAND и RANDBETWEEN . Некоторые подфункции функций рабочего листа CELL и INFO также сделают их неустойчивыми.

  • Добавление / вычитание целых чисел из времени
  • как добавить нули после чисел, чтобы получить длину исправления в EXCEL
  • Флагирование дубликатов в Excel на основе даты и адреса электронной почты
  • Вычисление занятых ресурсов в это время?
  • в excel, я хочу подсчитать количество ячеек, которые не содержат определенного символа
  • Преобразование формата времени в excel
  • Ссылка на формулу из другой ячейки вместо текста ячейки
  • Когда 1 ячейка проверяет (условие IF), все остальные ячейки также проверяют
  • Как установить заголовок для оси X и оси Y для графика рассеяния XY в Excel
  • Связать ячейки Excel с ссылочным столбцом
  • Удалите все после определенной строки в Excel 2010
  • Давайте будем гением компьютера.