讓教學組提早下班系列 - 快速轉換學習扶助篩選測驗身份別代碼

 最近又到了各年級學習扶助篩選測驗的時候！

我們學校教學組必須利用校務係統篩選出每個年級國英數各科學習落後的學生，

並標示學生身份別相關代號後再上傳到學習扶助的網站中。

教學組問我是否有比較快速的方法！

於是就找到了 Google 試算表的相關公式，

只要貼上從校務係統下載的資料就能得到想要的結果囉！

有需要的老師趕快試試看吧！

首先需轉換學生的年級和班級

🎯將年級轉換為數字(我們學校有特教班，所以多個特，如果沒有可以忽略)

=ARRAYFORMULA(SWITCH(MID(F2:F, 1, 1), "一", 1, "二", 2, "三", 3, "四", 4, "五", 5, "六", 6, "特", "特", ""))

🎯將班級轉換成數字：

=ARRAYFORMULA(IF(F2:F="", "", IF(REGEXMATCH(F2:F, "十一"), 11, 

  SWITCH(IFERROR(REGEXEXTRACT(F2:F, "年(.+)班")), 

    "一", 1, "二", 2, "三", 3, "四", 4, "五", 5, 

    "六", 6, "七", 7, "八", 8, "九", 9, "十", 10, 

    ""))))

🎯將校務系統身份別轉換成代號：

這邊需要(身份別、別名對照表)二個工作表來相互配合

新增一個名為「身份別」的工作表，為學習扶助系統上傳的名稱和代號

新增一個名為「別名對照表」的工作表，內容為校務系統身份別名稱和上傳標準名稱的對照

最後再把下列公式貼到 C 欄，就可以直接得到相關代碼了！(這邊要留意的是，校務系統下載來的身份別要在 J 的欄位)

=ARRAYFORMULA(

  IF(J2:J="", "", 

    BYROW(J2:J, LAMBDA(cell, 

      TEXTJOIN(",", TRUE, UNIQUE(

        IFERROR(

          XLOOKUP(

            IFERROR(

              MAP(TRIM(SPLIT(cell, ",")), LAMBDA(x,

                LET(

                  matched, FILTER('別名對照表'!B$2:B, REGEXMATCH(x, '別名對照表'!A$2:A)),

                  IF(COUNTA(matched)>0, INDEX(matched,1), x)

                )

              )),

              TRIM(SPLIT(cell, ","))

            ),

            '身份別'!B$2:B, '身份別'!A$2:A, 

            ""

          )

        )

      ))

    ))

  )

)

👉 學習扶助篩選測試上傳格式範本下載 

快來試試吧~