close

3103

Excel儲存格內含多個2位數如何取各個數值個位數並以空白鍵分隔

許初陽:錦子老師您好,請教一下,我是一位國小老師,2020/8/31日開學,學生原始座位如C3:H8儲存格,學校規定每一週都要將右邊一排()學生向左邊移一排(),第一排學生變成最後一排(),不知該如何設計公式,可以每個星期一一開啟檔案自動排好?謝謝!

image

錦子老師:這個問題解決方法如下:

步驟1:點取B2儲存格,輸入公式:

=TEXT(LEFT(SUM(IFERROR(MID(A2,ROW($1:$12)*3-1,1)*10^(7-ROW($1:$12)),)),(LEN (A2)+1)/3),REPT("0 ",(LEN(A2)+1)/3))

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入,將公式複製到B3:B12儲存格。

image

【公式說明】

=TEXT(LEFT(SUM(IFERROR(MID(A2,ROW($1:$12)*3-1,1)*10^(7-ROW($1:$12)),)),(LEN (A2)+1)/3),REPT("0 ",(LEN(A2)+1)/3))

公式1MID(A2,ROW($1:$12)*3-1,1)

傳回A2儲存格內容從112皆乘以31的字元位置(2,5,8,11….)開始抓取1個字元。

image

公式2IFERROR(公式1*10^(7-ROW($1:$12)),)

如果公式1乘以10N次方(7-ROW($1:$12))出現錯誤訊息,則不運算,否則執行公式1乘以10N次方(7-ROW($1:$12))

image

公式3SUM(公式2)

計算公式2運算結果的總和。

image

公式4LEFT(公式3,(LEN(A2)+1)/3)

抓取公式3運算結果左邊N個字元(A2儲存格字串長度加1除以3)

image

公式5TEXT(公式4,REPT("0 ",(LEN(A2)+1)/3))

公式4運算結果設定每一位數(A2儲存格字串長度加1除以3)格式成「"0 "」文字格式。

image

其實也可以將公式4與公式5變換位置,公式變更為:

=LEFT(TEXT(SUM(IFERROR(MID(A2,ROW($1:$12)*3-1,1)*10^(7-ROW($1:$12)),)),REPT ("0 ",7)),(LEN(A2)+1)/3*2-1)

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(1) 人氣()