在 Excel 裡,如果使用 XLOOKUP 函數來對資料表查詢,除了不需像 VLOOKUP 函數一定得由第一欄比對查詢資料之後,也可以使用萬用字元來查詢。
以下的範例,要練習利用 XLOOKUP 函數以欄位名稱和萬用字元查詢。
【設計與解析】
1. 在 XLOOKUP 函數中使用欄位名稱來查詢
在 Excel 裡,如果使用 XLOOKUP 函數來對資料表查詢,除了不需像 VLOOKUP 函數一定得由第一欄比對查詢資料之後,也可以使用萬用字元來查詢。
以下的範例,要練習利用 XLOOKUP 函數以欄位名稱和萬用字元查詢。
【設計與解析】
1. 在 XLOOKUP 函數中使用欄位名稱來查詢
在 Excel 的工作表裡有一個成績表,你想建立一個依指定欄位「自動」排序成績表?
如下圖,在下拉式清單中選取一個欄位,成績表就自動依這個欄位排序。
建立自動排序的成績表,就不用重覆手動操作排序了。
【設計與解析】
本篇列出部落格中關於成績、分數計算的相關文章。
在 Google 表單中其實是可以將問卷指定為「測驗」,除了可以用來考試,也能提供標準答案,還可以幫你算分數。
但是,學校的老師問到:如果要用Google表單測驗「多選題」,而答錯部分有倒扣,在處理上似乎有困難,該如何解決?多選題的計分方式是每題分數為S分,共有n個選項,假設答錯選項有k個,則得分:
Google 表單並沒有提供「多選題」的測驗計分方式,所以以下的設計是以 Excel 檔自動計算分數。當然,你也可以將公式寫在 Google試算表中。
1. 新增施測題目的Google 表單。
如下圖,在 Excel 的工作表裡,如果想要根據資料清單篩選出各店的訂單編號,並且依下圖格式呈式,該如何處理。(下圖中的編號是由左至右分欄顯示)
【設計與解析】
步驟一:篩選資料
儲存格E4:=FILTER($A$4:$A$27,$B$4:$B$27=E3,"")
在 Excel 2021 版(Office 365)有新增了動態陣列公式,這個為不太理解傳統陣列公式者,在使用公式時帶來了方便性,讓公式的設計更簡化。
使用以下的例子:使用傳統陣列和動態陣列公式列出模糊搜尋清單,來看看其不同之處。
只要輸入一個字,要從資料區中找出含有這個字的儲存格清單。
定義名稱:
在 Excel 裡,如果使用排序工具手動將數列排序,則無法因應數列內容變動時,自動更改排序內容。而改以公式來處理,則可以數列內容調整時,排序結果隨之改變。
【數列的排序】
(1) 動態陣列
儲存格C4:=SORT(A4:A16)
學校同仁大都還沒轉換至 Excel 2021,本篇列出 Excel 2021 新增的函數,先來初探其有何妙用之處。
本篇你將學到函數介紹:XLOOKUP、SORT、SORTBY 、FILTER 、UNIQUE 、SEQUENCE 、RANDARRAY 、XMATCH 、LET等函數
1. XLOOKUP 函數
你可能熟悉 VLOOKUP 函數的查詢功能,其主要是由第一欄中查詢符合的位置,再對照傳回指定欄位對應的儲存格內容,所以查詢內容必須位於第一欄。這個函數很好用,但也是帶來某種不方便。
同事問到:在Google日曆新增活動時,別人都有「預約時段」功能,為何我的沒有?
關於預約時段的功能,可以參考:
Google-使用日曆中的預約時段功能來建立預約時間、空間或人員的網頁
如果你沒有「預約時段」,可以進入日曆的設定:
在 Excel 裡,如果在資料清單裡要判斷所有項目是否全部相同,該如何處理?
如下圖,資料內容有可能出現儲存格裡是空白的狀況,如果考慮空格或是不考慮空格該如何計算?
【設計與解析】
1. 所有相目全部相同
儲存格F4:{=IF(MAX(COUNTIF(A4:E4,A4:E4))=5,"V","")}
在教室上課或是遠距教學時播放 PowerPoint 簡報檔,有時就會遇到要使用播放多個簡報檔來交互運用。PowerPoint 是允許使用者同時播放多個簡報檔。
教師在班上用觸屏授課或遠距教學時,如果能讓桌面乾淨、畫面主題明確,相信有助於教學實施。
以下圖為例,當你開啟二個 PowerPoint 檔,並且都在播放狀態,使用者透過 Alt+Tab 鍵,可以切換要編輯那一個簡報檔,也可以切換要顯示播放那一個簡報檔。
而當視窗一但多了,會顯的混亂,且切換時會變長。
學校教室裡大多配有數位講桌(含有電腦),也可以外接筆記型電腦,教室裡也有很大的觸屏可以運用。當利用 Google Meet 實施遠距教學時,如果教學現場教室裡有學生,而遠端也有學生時,教室裡的這些工具,如何好好的來運用?
在此,先假設教室裡的觸屏和數位講桌已做好互動連結,學生在Google Meet裡可以看到老師分享的桌面。
【將觸屏當做很大的手寫板】
當在遠距教學時,在 Google Meet 中將整個桌面分享給學生,同時要顧及教室現場的學生,所以直接在觸屏上書寫,將觸屏當做很大的手寫板。教師站在觸屏前操作,電腦的桌面同步連動。將觸屏結合以下的工具來發揮。
1. 利用 Office 內建的「繪圖」工具
教師在計算小考成績時,除了計算全部次數的平均之外,還可能會使用:
(1) 計算最優項目計算平均分數
(2) 勾選特定項目計算平均分數
參考下圖,如果使用人工方式來計算這樣的平均,所有的老師和小老師都會哭哭了。
根據前一篇文章:Excel-將時間分別遞增時、分、秒
如果想要將將日期分別遞增年、月、日,如何處理?
(1) 遞增年
儲存格B5:=DATE(YEAR(B4)+1,MONTH(B4),DAY(B4))
延續這篇:
Excel+Google-輸入英文字詞自動產生字典網址和Google翻譯唸出字詞的QR Code
本篇要利用 Google試算表來產生讓Google翻譯唸出字詞(日語)的網址QR Code。
將Google翻譯網址: