如图,将第一个图中C列第4行开始的所有数据,提取到如图二的sheet中,即将原来横着的数据竖过来,用什么公式可以解决这个问题呢
å ä½æä¾çå ¬å¼æå¤å¶å转置ç²è´´å¤§é½æ¯å¯è¡çï¼ä¸é¢æä¾ä¸ä¸ªæ´ç®åå¿«æ·çå ¬å¼ä¾åèãå å¨Sheet1ä¸æä¸è¡æ¨ªåæå¨é¼ æ éæ©è¯¥è¡æ°æ®ï¼ç¶ææ ä¸æ¥çä¸è®¡æ°ï¼å¿ä¸è®°ä¸è¿ä¸ªæ°åï¼ä¹å°±æ¯Sheet2ä¸çè¡æ°ï¼å¹¶æ¥çä¸æ°æ®çè¡æ°ï¼ä¹å°±æ¯Sheet2ä¸çåæ°ã
转å°ï¼Sheet2ä¸ï¼éæ©A1å¾å³æå¨é¼ æ ï¼ä½¿éæ©çåæ°=Sheet1çè¡æ°ï¼ååä¸æå¨é¼ æ ï¼ä½¿éæ©çè¡æ°=Sheet1çåæ°ï¼å¦å¾ï¼Sheet1æ11è¡13åï¼Sheet2ä¸å°±éæ©A1:K13ï¼ç¶åè¾å ¥:
=TRANSPOSE(
ç¹Sheet1åéæ©æ°æ®åºåï¼A2:M12ï¼
åè¾å ¥å ¬å¼å³æ¬å·ï¼ï¼
å ¬å¼ç¼è¾æ ä¸æ¾ç¤ºä¸ºï¼
=TRANSPOSE(Sheet1!A2:M12)
åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼
å³å¯å¾å°ç»æ
追çè¿å¯ç¨Offsetå ¬å¼ï¼ç¨offsetçä¼ç¹ï¼ä¸ç¨èèSheet1ä¸çæ°æ®å¤å°ãå¦å¾ï¼
A1=OFFSET(Sheet1!$A$1,COLUMN(A:A),ROW(1:1)-1)&""
å³æå°åºç°ç©ºç½åå æ ¼ï¼ä¸èµ·ä¸æå°åºå°ç©ºç½è¡ã
å¦æExcelçæ¬è¾é«ï¼å¦2013以ä¸ï¼ç¨Power Queryï¼ç®ç§°PQï¼æç®åï¼ä¸ä¸æ°æ®æºçååï¼å¢å ãåå°æ°æ®ï¼ä¿®æ¹æ°æ®ï¼å ³èãå¦æ æ¯2010çæ¬ï¼åéè¦åç¬ä¸è½½Power Qureyæ°æ®å·¥å ·ã
ç¨PQç®ç´æ¯é天äºï¼è½»ç¹å ä¸é¼ æ å³å¯å¾å°ç»æï¼ä»»ä½ æ°æ®æºæä¹ååï¼åªè¦å¯¹å¾å°çç»æè¿è¡å·æ°å³å¯å¾å°æ´æ°ã