顯示具有 google-sheet 標籤的文章。 顯示所有文章
顯示具有 google-sheet 標籤的文章。 顯示所有文章

2022/10/18

使用HYPERLINK取用地點名稱建立Google Map超連結


疫情影響,大家這幾年應該都悶壞了,重點是,日本解除門禁了!大家應該都有想要即刻奔出國的慾望吧😂

最近如果有開始在計畫自助旅行的朋友,可以試試使用Excel規劃行程,我個人很喜歡使用Google Sheet安排,共同編輯很方便,大家把想去的地方全部加上後,再匯入到Google Map中看怎麼走比較順,規劃起來非常快速呢!

行程大致完成之後,再搭配 Hyperlink 這個超實用的函數,不管是要導去官網,還是想要直接連去Google Map看地圖、看行車路線,快速就能搞定,出門在外就不用手忙腳亂的啦!

這篇使用台灣最近最熱門的景點龍貓隧道、張美阿嬤農場、日本的吉卜力公園,以及日本環球影城做為範例!都是我好想去的地方呢💖



    基本說明

    HYPERLINK函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同,此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  綠底  的為可修改資料,且會影響公式結果的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



    函數HYPERLINK介紹

    在儲存格中建立超連結,內容皆需要使用雙引號「"」框起。
    函數公式:  HYPERLINK( 網址 , 顯示文字 )  

    • 網址
      ① 將網址放入公式中:
       EX:HYPERLINK("https://supersmartcookie.blogspot.com/" , 顯示文字 )

      ② 將網址放入其他儲存格中代入:
       EX:HYPERLINK( A3 , 顯示文字 )
    • 顯示文字:選填,為超連結要顯示在儲存格中的文字內容。若無填寫預設為網址。
       EX:HYPERLINK( 網址 ,"SuperSmartCookie")



    Google Map地圖網址使用方式

    這邊僅介紹搜尋與顯示路線的方式,其它搜尋方式會因為中文搜尋而有點限制,詳細內容可以參考:《 Google Map 地圖網址官方說明頁 》。

    這邊介紹的 Google Map 的網址語法,能夠跨平台使用,不論是使用 Android 安卓系統,或是使用 iOS 蘋果系統,都可以共通。

    • 建立網址:使用固定的網址格式,並加上參數。
    1. 參數介紹
      可以使用地點名稱、地址,若參數中間有空白,需使用符號「 + 」或「 & 」取代。
      地點名稱的內容需要很明確,就與我們一般在搜尋 Google Map 一樣,若是範圍太廣,Google就會列出許多點讓妳做選擇。因此若要指定地點,名稱就一定要明確,才能夠顯示到我們要的地點,直接取用Google Map中使用的名稱,是最快的方式了。

    2. 網址語法
      搜尋:顯示出指定地點的位置
         https://www.google.com/maps/search/ 參數

      路線:顯示出指定地點到另一指定地點的路線
         (在地圖網址打開後,才能選擇另一指定地點)
         https://www.google.com/maps/dir/ 參數



    函數實際操作

    此篇的函數公式結果顯示如下(  黃底  內容):
      《 步驟 》
    1. 建立顯示位置 (C3):
      使用Hyperlink函數,在網址中鍵入Google Map搜尋的指定網址語法「https://www.google.com/maps/search/」,後面需要帶入景點的儲存格,因此使用符號「&」連接,景點儲存格位置為「B3」,顯示出的超連結文字設定為「MAP」,所有內容(除了儲存格外) 皆需要使用雙引號「"」框起。
      =HYPERLINK("https://www.google.com/maps/search/"&B3,"MAP")

    2. 建立顯示路線 (D3):
      使用Hyperlink函數,在網址中鍵入Google Map路線的指定網址語法「https://www.google.com/maps/dir/」,後面需要帶入景點的儲存格,因此使用符號「&」連接,景點儲存格位置為「B3」,顯示出的超連結文字設定為「MAP」,所有內容(除了儲存格外) 皆需要使用雙引號「"」框起。
      =HYPERLINK("https://www.google.com/maps/dir/"&B3,"MAP")

    3. 將公式帶入其它儲存格 (C4~D6):
      選擇C3~D4,將滑鼠移到右下角待鼠標變成十字形狀,點選滑鼠左鍵不放下拉到要代入的位置,再放開左鍵。

    4. 完成~~~~~
      滑鼠移至藍色並帶有下底線的文字,就會出現網址,滑鼠點擊就會開啟指定地點的Google Map。


    EXCEL公式小工具

    使用方式
    1. 選擇超連結點選後的 Google Map 顯示方式
    2. 輸入景點的儲存格位置
    3. 輸入超連結顯示的文字,若未填寫預設為「Map」
    4. 按下 Enter 按鈕
    5. 點擊輸出的函數公式就可以直接複製
    6. 將它貼在想要顯示的儲存格內即可
    選擇顯示方式
    景點儲存格位置
    超連結顯示文字
    Copied!

    2022/10/13

    使用建立名稱+資料驗證+INDIRECT建立兩層下拉式選單


    使用資料驗證製作下拉式選單,是個很便利的功能,還可以減少資料輸入錯誤的機會。

    但什麼是兩層下拉式選單呢?最基本的資料驗證,是建立一層下拉式選單,而第二層的功能,則是要能夠隨著選擇第一層之後,對可以選擇的內容進行調整,雖然設定方式相對複雜一些,但絕對能夠大大提升表單的便利與實用性。

    其他參考:

    《 如何製作下拉選單選擇名稱並顯示對應資料 》 --- INDEX+MATCH


      基本說明

      Microsoft Excel 與 Google Sheet 使用資料驗證建立一層下拉選單的設定,請參考這裡。
      建立名稱的方式兩者相同,但 Microsoft Excel 的網頁版本有一些限制,所以在命名前要再多加確認與注意了。
      INDIRECT函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  綠底  的為可修改資料,且會影響公式結果的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。
      此篇使用 Google Sheet 作為範例。



      建立名稱/ 命名範圍


      為儲存格範圍命名、取名稱,能讓儲存格範圍的用途更清楚易懂、一目了然,直接帶入還能夠簡化公式。Excel 和試算表的位置相同,不同之處有補充在下面的注意中。
        建立方式:
      1. 選擇好要命名的範圍,且不用包含標題列。
        (下方範例中為 B6 至 B10 )
      2. 找到公式列位於左上角的儲存格名稱方塊。
      3. 將要取的名稱直接輸入至方塊內。
      4. 完成。

      注意:
      • 名稱限制:
        開頭不能以字母、數字、底線( _ )、反斜槓( \ ) 以及 TRUE 或 FALSE
        其餘字元中不能有空格與標點符號,但可以使用底線及英文句點

      • 試算表:可至資料→已命名範圍,中進行修改與移除,快捷鍵為Ctrl + J
      • Excel 電腦版本:可至公式→名稱管理員,中進行修改與移除
      • Excel 網頁版本:在名稱建立後就無法移除與修改 (2022/10/8)



      函數INDIRECT介紹

      指定字串內容組成儲存格位置,並回傳內容。
      函數公式:  INDIRECT( "文字或儲存格位置" )

      ① 直接寫入儲存格位置 (同一工作表):
        內容為文字,加上「"」雙引號框起。
       EX:INDIRECT ( "B3" )

      ② 代入其他儲存格位置 (同一工作表):
       EX:INDIRECT ( C2 )

      ③ 直接寫入儲存格位置 (不同工作表):
        工作表名稱後還需要加入英文小寫的「!」驚嘆號識別,後面直接接續儲存格位置,最後用「"」雙引號框起。
       EX:INDIRECT ( "工作表2!A3" )

      ④ 代入其他儲存格位置 (不同工作表):
        工作表名稱後還需要加入英文小寫的「!」驚嘆號識別,並用「"」雙引號框起,再加上「&」連結儲存格位置。
       EX:INDIRECT ( "工作表3!" & D5 )



      實際操作

      此篇的函數公式結果顯示如下(  黃底  內容):
        《 步驟 》
      1. 建立第一層選單 (C2):
        選擇儲存格C2,找到上排文字功能列中的資料→資料驗證,條件選擇為「範圍內的清單」,並選擇下方資料的標題儲存格位置,為B5:C5。

      2. 命名資料範圍 (水果班 B6:B10):
        選擇B6~B10,點擊鍵盤快捷鍵 Ctrl + J,輸入名稱「水果班」。

      3. 命名資料範圍 (蔬菜班 C6:C10):
        選擇C6~C10,點擊鍵盤快捷鍵 Ctrl + J,輸入名稱「蔬菜班」。

      4. 建立第二層選單的資料範圍 (E6):
        因為已建立好的名稱可以直接用於儲存格中,因此使用INDIRECT函式,讓它等於第一層選單的儲存格內容,記得下方要預留空間給資料,需要與命名資料範圍一樣大小,不然會有溢出的情況。

      5. 建立第二層選單 (C3):
        選擇儲存格C3,找到上排文字功能列中的資料→資料驗證,條件選擇為「範圍內的清單」,並選擇下方資料的標題儲存格位置,為E6:E10。

      6. 完成~~~~~
        驗證範圍會隨著第一層選單的調整而改變。


      EXCEL公式小工具

      使用方式
      1. 輸入儲存格位置
      2. 選擇儲存格所在工作表位置
      3. 若為不同工作表,請再輸入工作表的名稱
      4. 按下 Enter 按鈕
      5. 點擊輸出的函數公式就可以直接複製
      6. 將它貼在想要顯示的儲存格內即可
      儲存格位置
      儲存格位於:
      工作表名稱
      Copied!

      2022/10/08

      在Google Sheet使用COUNTIF自訂條件格式標示重複資料


      這篇是針對Google Sheet中條件格式設定的功能,因為Google Sheet中並沒有這個選項,所以有時候要判斷資料實在是很麻煩,但只要用條件格式設定再配合函數公式,就可以輕鬆達成了!

      雖然這個方式在 Microsoft Excel 中也可以使用,但 Microsoft Excel 中的條件式格式設定,就有能夠直接標示出重複值的功能,所以就不需要多這一道手續了唷。

      其他參考:

      《 如何計算儲存格中包含特定文字的數量 》 --- COUNTIF ( */ ? )


        基本說明

        COUNTIF函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同,此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  紅底  的為符合條件格式設定的重複資料;標示為  淺灰底  的為黃底儲存格的公式文字。



        條件格式設定方式

        依序點擊 上排工具列 → 格式 → 條件格式設定 後
        右側會開啟「條件式格式規則」的視窗。

        單色
        ① 套用範圍:
         需要被判斷的儲存格範圍,可以直接輸入儲存格位置,也可以點選旁邊的田字符號新增。


        ② 格式規則:
         非空白文字右側▼箭頭,點選後還有很多規則可以使用,例如文字是否相同或是有包含、日期的早晚,以及數字大小的判斷,而本篇要使用的是公式的部分。
         格式設定樣式則是看個人喜好,這邊不多加說明。下排由 B 開始的是設定項,依序為字體粗細、斜體、底線、刪除縣、文字顏色、文字底色。在做任何調整後,都會即時的顯示在預設欄位中。



        色階
        ① 套用範圍:
         與上方單色的方式一樣,設定需要被判斷的儲存格範圍,可以直接輸入儲存格位置,也可以點選旁邊的田字符號新增。


        ② 格式規則:
         這個方式僅能針對數值做判斷,因為它會依照數值的大小去調整顏色,有興趣都可以玩玩看。


        實際操作

        此篇的函數公式結果顯示如下(  紅底  內容):
          《 步驟 》
        1. 使用COUNTIF計算項目數量 (C9)
          COUNTIF詳細用法可以回到上方目錄參考,要判斷的範圍為B欄,要判斷的條件,因為這邊要核對的資料為整欄,所以這邊寫為B1即可,最後再加上「$」符號將欄位固定住。
          =COUNTIF($B:$B,$B1)

        2. 判斷重複資料 (C10)
          只要資料重複,使用COUNTIF計算出來的數量就會大於1,因此我們將剛剛寫好的公式加上「>1」的判斷。
          =COUNTIF($B:$B,$B1)>1

        3. 條件式格式設定
          將剛剛說明過的條件式規則視窗打開,選擇要套用的範圍,並將格式規則的「非空白」調整為「自訂公式:」(要往下拉~它在最底下~),將剛剛寫好的公式放入「值或公式」的空白格中,最後隨意選擇自己喜歡的顏色,預設的底色為綠色。


        4. 完成~~~~~


        EXCEL公式小工具

        使用方式
        1. 輸入要核對是否重複資料的欄位
          (僅需要輸入欄的英文字)
        2. 按下 Enter 按鈕
        3. 點擊輸出的函數公式就可以直接複製
        4. 將它貼在想要顯示的儲存格內即可

        如何自訂條件格式標示重複資料 --- COUNTIF

        資料欄位



        Copied!

        2022/10/06

        使用DAY+DATE計算出某年中月的天數


        如果要顯示出某年某個月份中的天數,使用DAY+DATE就可以達成。

        其他參考:


          基本說明

          DAY 和 DATE 函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同,此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  綠底  的為可修改資料,且會影響公式結果的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



          函數DAY介紹

          將日期的日傳換為數字格式。
          函數公式:  DAY(日期) 

          ① 將內容放入公式中:
           EX:DAY("2022/10/6")

          ② 將內容放入其他儲存格中代入:
           EX:DAY( A3 )



          函數DATE介紹

          將年、月、日的數值轉換為日期。
          函數公式:  DATE (年, 月, 日) 

          ① 將內容放入公式中:
           EX:DATE ( 2022 , 10 , 6 )

          ② 將內容放入其他儲存格中代入:
           EX:DATE( A3 , B3 , C3 )



          函數實際操作

          此篇的函數公式結果顯示如下( 當月天數  黃底  內容):
            《 步驟 》
          1. 列出要調整的日期 (B6):
            這邊使用題曲儲存格內容的方式,因此要將儲存格年、月、日的格式調整為2位數,才不會出錯噢。使用DATE函式,擷取出日期中的年與月,年份位於最左側,使用LEFT,共提取4個位元,月份在中間,因此使用MID,從第6個字元開始擷取2個字元,月份後再加上「+1」,才能正確顯示為當月,否則會顯示為前一個月,最後在日的部分補0,就會顯示為當月的最後一天。
            =DATE(LEFT(B3,4),MID(B3,6,2)+1,0)

          2. 計算當月日期 (B8)
            使用DAY函式,直接將剛剛寫好的公式放入,就可以僅擷取出日的部分。
            =DAY(DATE(LEFT(B3,4),MID(B3,6,2)+1,0))

          3. 完成~~~~~


          EXCEL公式小工具

          使用方式
          1. 將要使用的儲存格日期調整為2位數
          2. 輸入儲存格所在的位置
          3. 按下 Enter 按鈕
          4. 點擊輸出的函數公式就可以直接複製
          5. 將它貼在想要顯示的儲存格內即可
          儲存格位置



          Copied!

          2022/10/01

          使用TIME+TODAY+NOW製作倒數時間的計時器


          應該很多人和我一樣,剛上班就在期待下班吧😂,妳是不是也是常常看了時間,但是不知道到底還要多久才能夠回家呢?如果在每次看時間的時候,就要加減一次,而且不能光明正大用手機計算,這樣真的很麻煩呢!所以一起來用 Microsoft Excel 或 Google Sheet 來做個倒數計時器吧!想知道的時候就可以立刻知道剩餘時間還有多久,而且在上班開它們可是一點也不奇怪,是不是超棒的!

          *兩者的網路版本皆無法即時更新時間,但只要內容的設定值有任何改變,就會同步更新,所以只要隨便在另外的儲存格中打字,就會更新時間了唷。

          其他參考:


            基本說明

            TODAY/  NOW/ TIME函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同。

            但兩個的網頁版本都不會每秒鐘自動更新 TODAY/  NOW2的時間,僅能在設定值變更的時候才進行更新。Google Sheet 還能夠設定於每分鐘或每小時更新(在設定中的重新計算調整)。且因為這兩個函數為變動值,在編輯的時候就會更新內容,所以可能會稍微的影響到 Excel 的效能。

            此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



            函數TIME介紹

            輸入數值,將時、分、秒轉換成時間值。
            函數公式:  TIME(小時, 分鐘, 秒)

            小時/ 分鐘/ 秒
            ① 將內容放入公式中
             EX:TIME( 12 , 分鐘, 秒 )
             EX:TIME( 小時,  30 , 秒)
             EX:TIME( 小時, 分鐘 , 15 )

            ② 將內容放入其他儲存格中代入
             EX:TIME( A3 , 分鐘, 秒 )
             EX:TIME( 小時,  C2 , 秒)
             EX:TIME( 小時, 分鐘 , D5 )




            函數實際操作

            此篇的函數公式結果顯示如下(  黃底  內容):
              《 步驟 》
            1. 計算出現在時間 (C5):
              使用NOW函數 (詳細使用方式可以到上方參考中察看),這時會由系統自動設定格式,為「日期+上午/下午+時間」的格式。
              =NOW()

            2. 加入要計算的時間 (C6):
              使用TIME函數,將要結算時間的小時/分鐘/秒數用逗點分隔,範例中使用我的下班時間下午6點為例,使用24進位,因此小時輸入18,分鐘與秒數則是0。
              =TIME(18,0,0)-(NOW())

            3. 調整倒數結果格式 (C7):
              使用TEXT函數 (詳細使用方式可以到上方參考中察看),將剛剛寫好的公式放進函式中,並使用小時+分鐘的格式碼「HH:MM」,若想要更精準的有秒數,可以再加上秒的格式碼「HH:MM:SS」。
              =TEXT(TIME(18,0,0)-NOW(),"HH:MM")

            4. 完成~~~~~


            EXCEL公式小工具

            使用方式
            1. 輸入倒數的時間,預設為00:00:00
            2. 選擇想要顯示的格式
            3. 按下 Enter 按鈕
            4. 點擊輸出的函數公式就可以直接複製
            5. 將它貼在想要顯示的儲存格內即可
            倒數時間 : :
            顯示格式
            Copied!

            2022/09/29

            使用IF同時核對3項資料是否相同


            如果要核對資料是否相同,使用 IF 來判斷就可以了,如果增加到 3 個也是一樣的噢,而且可以用的方法還很多種,不過這篇僅教我個人比較偏好的一種方式,因為它看起來比較清楚,也比較整齊呢!
            但這個方式有一個小缺點,就是 3 項資料必須要用相同的排序核對,不然會顯示很多錯誤喔。


              基本說明

              IF函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同,此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  綠底  的為可修改資料,且會影響公式結果的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



              函數IF介紹

              判斷運算邏輯是否正確,並回傳是或否的設定值。
              函數公式:  IF(運算條件, 運算正確, 運算錯誤)

              • 運算條件:
                使用數學運算子「 =、>、<」進行判斷。
                ① 直接輸入在公式內
                 EX:IF("A"="B", 運算正確, 運算錯誤)

                ② 使用儲存格內資訊:
                 EX:IF( A1 > B1 , 運算正確, 運算錯誤)

              • 運算正確/ 運算錯誤:
                輸入運算條件正確或錯誤時要顯示的內容,運算正確為必填,運算錯誤則為選填,若沒填寫出現錯誤的時候會顯示為FALSE。
                ① 輸入數值
                 EX:IF(運算條件, 123, 456)

                ② 輸入文字
                 EX:IF(運算條件, "Super", "Smart")

                ③ 使用其他儲存格的內容
                 EX:IF(運算條件, B3, B4)



              函數實際操作

              此篇的函數公式結果顯示如下(  黃底  內容):
              如下方表格,有項目A、B、C,需要確認 3 個項目是不是內容都相同。
                《 步驟 》
              1. 判斷項目A與項目B是否相同 (B10):
                使用IF公式,運算條件使用「=」等號判斷B3與C3是否相同,運算正確先填1,運算錯誤可先不填。
                =IF(B3=C3,1)

              2. 自訂顯示結果 (B12)
                顯示結果用文字符號的打勾和叉叉來呈現,因為是文字,所以需要用「"」雙引號框起,也可以替換為任何自己喜歡的。
                =IF(B3=C3,"✔","✘")

              3. 加入第 3 個判斷 (B14):
                Excel對於是否的判斷,會回傳是=TRUE=1,與否=FALSE=0,因此我們就要使用這個方式,將儲存格相乘得到結果。如果B3=C3相等=1,C3=D3相等=1,相乘1×1=1=TRUE,若其中有一個為否=0,相乘1×0=0=FALSE。
                =IF((B3=C3)*(C3=D3),"✔","✘")

              4. 套用至其他儲存格中 (E3-E7):
                點擊寫好公式的 E3,點著右下角不放拉到要套用的儲存格。

              5. 完成~~~~~


              EXCEL公式小工具

              使用方式
              1. 輸入第 1 個項目的儲存格位置
              2. 輸入第 2 個項目的儲存格位置
              3. 輸入第 3 個項目的儲存格位置
              4. 按下 Enter 按鈕
              5. 點擊輸出的函數公式就可以直接複製
              6. 將它貼在想要顯示的儲存格內即可
              儲存格位置 1
              儲存格位置 2
              儲存格位置 3
              Copied!

              2022/09/27

              在報告中使用TODAY/ NOW列出現在的日期與時間


              工作上很常有各種報告要做,檢核表、日報、月報、季報、年報等等,雖然實際報告的時候都會使用PPT完成,但若是要上交簽核的紙本,通常還是會使用WORD或是EXCEL來完成。
              在這些報表中很重要,卻最常被疏忽掉的一個項目,就是日期了。有時候報告時間很急,一個不小心疏忽沒檢查到,就會是上次製作報表的日期,或是模板中不知道民國哪一年的古早日期。
              這篇來教大家,如何讓報表上的日期能夠自動更新,還能客製化成自己需要的格式,就不用再擔心發生這種小意外,可以專心的在其他內容上面了呢!

              其他參考:


                基本說明

                TODAY/  NOW函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同。

                但兩個的網頁版本都不會每秒鐘自動更新時間,僅能在設定值變更的時候才進行更新。Google Sheet 還能夠設定於每分鐘或每小時更新(在設定中的重新計算調整)。且因為這兩個函數為變動值,在編輯的時候就會更新內容,所以可能會稍微的影響到 Excel 的效能。

                此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



                函數TODAY介紹

                回傳當天的日期值。
                函數公式:  TODAY()

                這個函數非常簡單,沒有任何的其他設定項目,可與文字和其它日期相關函數做搭配使用(可回到最上方參考查看)。



                函數NOW介紹

                回傳當天的日期與時間值。
                函數公式:  NOW()

                同上,沒有任何的其他設定項目,與TODAY使用方式相同,也可與文字和其它日期相關函數做搭配使用(可回到最上方參考查看)。



                函數實際操作

                此篇的函數公式結果顯示如下( B7  黃底  內容):
                我們要撰寫一個日報表的標題,需要有報表名稱,並加上當天填寫的日期。
                  《 步驟 》
                1. 自動更新當天日期(B10):
                  使用TODAY函數,直接放到儲存格中即可。
                  =TODAY()

                2. 加入報表名稱(B13):
                  要加入的文字內容使用「"」雙引號框起,並用「&」連結步驟1中的公式。
                  ="工作日報表 填寫日期:"&TODAY()

                3. 調整日期格式(B16):
                  完成步驟2會發現日期變成一串數字,而不是日期,因此我們需要用TEXT調整它的格式。套用TEXT函數,資料內容放入函數TODAY,調整的格式為年/月/日,並使用「"」雙引號框起。
                  ="工作日報表 填寫日期:"&TEXT(TODAY(),"YYYY/M/D")

                4. 完成~~~~~


                EXCEL公式小工具

                使用方式
                1. 填寫要使用的報告標題名稱,若不需要可不填寫。
                2. 選擇要顯示的日期,或日期+時間的格式
                3. 按下 Enter 按鈕
                4. 點擊輸出的函數公式就可以直接複製
                5. 將它貼在想要顯示的儲存格內即可
                填寫名稱
                選擇格式
                格式

                格式

                Copied!

                2022/09/25

                使用TEXT+DBNUM2(+SUBSTITUTE)將數字變成大寫中文字


                如果有到銀行辦過事的就知道,大部分金融相關要填寫的單據、票據,都需要使用中文的大寫數字,也就是「壹、貳、參、肆、伍、陸、柒、捌、玖、零」,在 Microsoft  Excel 中只要直接使用TEXT搭配DBNUM2這個格式代碼就可以達成,但這個方式在 Google Sheet 中僅能顯示數詞單位,數字本身並沒有辦法調整為大寫國字,因此這邊額外加上函數SUBSTITUTE,強制取代掉阿拉伯數字,目前看起來是最好的方式了,可以試試看唷。


                  基本說明

                  TEXT+DBNUM2 函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,但僅在 Microsoft Excel 中能將數字順利顯示成全部大寫,Google Sheet 僅會加上數詞單位,數字仍會是阿拉伯數字顯示,因此需要再搭配SUBSTITUTE函數公式,將數字取代為大寫國字。
                  為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;儲存格標示為  綠底  的為可修改資料,且會影響公式結果的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



                  函數實際操作

                  此篇的函數公式結果顯示如下(  黃底  內容):
                    《 步驟 》
                  1. 調整文字格式(B12)
                    使用TEXT函數,先帶入要調整的儲存格位置,格式加上「"」雙引號,帶入[DBNUM2],後面加入0與大寫數字單位。
                    =TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元")

                  2. 調整數字1 (B15)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字1,用「"」雙引號框起,取代的文字為大寫的「壹」。
                    =SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹")

                  3. 調整數字2 (B18)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字2,用「"」雙引號框起,取代的文字為大寫的「貳」。
                    =SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳")

                  4. 調整數字3 (B21)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字3,用「"」雙引號框起,取代的文字為大寫的「參」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參")

                  5. 調整數字4 (B24)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字4,用「"」雙引號框起,取代的文字為大寫的「肆」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆")

                  6. 調整數字5 (B27)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字5,用「"」雙引號框起,取代的文字為大寫的「伍」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍")

                  7. 調整數字6 (B30)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字6,用「"」雙引號框起,取代的文字為大寫的「陸」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸")

                  8. 調整數字7 (B33)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字7,用「"」雙引號框起,取代的文字為大寫的「柒」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒")

                  9. 調整數字8 (B36)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字8,用「"」雙引號框起,取代的文字為大寫的「捌」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌")

                  10. 調整數字9 (B39)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字9,用「"」雙引號框起,取代的文字為大寫的「玖」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌"),"9","玖")

                  11. 調整數字0 (B42)
                    使用SUBSTITUTE將剛剛完成的公式帶入,用逗點分隔要取代的資料,為文字0,用「"」雙引號框起,取代的文字為大寫的「零」。
                    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C2,"[DBNUM2]0億0仟0佰0拾0萬0仟0佰0拾0元"),"1","壹"),"2","貳"),"3","參"),"4","肆"),"5","伍"),"6","陸"),"7","柒"),"8","捌"),"9","玖"),"0","零")


                  12. 完成~~~~~


                  EXCEL公式小工具

                  使用方式
                  1. 選擇使用的系統 (Microsoft和Google兩個的方式不一樣喔~)
                  2. 輸入儲存格位置,也可以直接輸入數值轉換
                  3. 按下 Enter 按鈕
                  4. 點擊輸出的函數公式就可以直接複製
                  5. 將它貼在想要顯示的儲存格內即可
                  選擇使用系統

                  儲存格位置
                  Copied!

                  2022/09/24

                  使用VALUE將文字格式轉換為可計算的數字格式


                  不知道大家在使用Excel的時候,有沒有遇過明明輸入的是數字,卻一直沒辦法用來計算的狀況呢?大多時候,是因為儲存格內的「數字」其實是「文字」,Excel在處理資料的時候,會將文字與數字視為不同的資訊,即使我們用肉眼「看起來」一樣,但從程式的角度來看就天差地遠了唷。
                  轉換格式除了用在核對上,也可以用在統整資訊上,將文字改成數字就可以計算,將數字改成文字就可以調整格式,看起來更直覺、美觀。

                  其他參考:



                    基本說明

                    VALUE函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用,且用法相同,此篇使用 Google Sheet 作為範例圖。為方便辨識,儲存格標示為  黃底  的是帶有函式資料的儲存格;標示為  淺灰底  的為黃底儲存格的公式文字。



                    函數VALUE介紹

                    將內容調整為指定格式的文字,內容需要使用「"」雙引號框起。
                    函數公式:  VALUE( 資料內容 )
                    • ① 將內容放入公式中
                       EX:VALUE( "123456" )
                       EX:VALUE( "9.87" )
                       EX:VALUE( "2022/9/22" )

                    • ② 將內容放入其他儲存格中代入
                       EX:VALUE( A8 )



                    函數實際操作

                    此篇的函數公式結果顯示如下(調整為數字  黃底  內容):
                    若現在有一份蘋果近10天的售出紀錄,日期、項目與數量都在同一格儲存格內,我們要將銷售出的數量進行加總。
                      《 步驟 》
                    1. 擷取文字 (D4)
                      因為範例中匯出的資料很整齊,且格式都相同,因此直接使用RIGHT函數將位於最右側的數量擷取出來 (RIGHT函式用法),要擷取的儲存格為C4,字元數為2。
                      =RIGHT(C4,2)

                    2. 將文字轉換為數字 (E4)
                      在前一個步驟已經將銷售的數量給提取出來,但可以看到D14欄位,並沒有辦法進行加總,因為提取出來的格式為文字,這時只要再加上VALUE公式,就能將文字調整為數值,並用於計算了。
                      =VALUE(RIGHT(C4,2))

                    3. 將公式代入其他儲存格
                      選擇儲存格 E4,滑鼠按下格子右下角不放,拉到要帶入公式的欄位為止。

                    4. 加總數量 (E14)
                      使用函數SUM,將E4到E13加總。
                      =SUM(E4:E13)

                    5. 完成~~~~~


                    EXCEL公式小工具

                    使用方式
                    1. 輸入要替換的儲存格位置
                      也可直接輸入公式,例如:1+2+3
                      輸入數值使用「"」雙引號框起,例如:"123"
                    2. 按下 Enter 按鈕
                    3. 點擊輸出的函數公式就可以直接複製
                    4. 將它貼在想要顯示的儲存格內即可
                    儲存格位置



                    Copied!