2013年2月26日 星期二

在Excel中以列印尺寸(公分)設定列高和欄寬

以 Excel 設計表格時,欄寬的設定數值無法以簡單的比率與公制單位換算,因為程式會自動為字寬與格線間留白一點點,所以總是要反覆確認實際印出的尺寸,很麻煩。今天菇狗了一下,在官方文件「Excel 的列高和欄寬限制」這篇中說到:

個別列的最大列高是 409 點 (1 點大約等於 1/72 英吋)。個別儲存格的最大欄寬是 255 字元。這個值代表格式為標準字型的儲存格中可顯示的字元數目。

由此得知列高與公分的換算比例是很單純的1:(72/2.54)

在官方文件「Excel中的度量單位和尺規」這篇中說到:

與 Microsoft Word 不同的是,Excel 沒有提供水平或垂直尺規,也沒有快速的方法讓您能夠以英吋為單位來度量工作表的寬度或高度。Excel 使用字元、點、和像素作為度量單位。
    • 儲存格的寬度是以字元和像素為單位顯示,而非以英吋為單位。當您拖曳欄名的邊界以調整工作表上的欄寬時,提示工具會以字元為單位來顯示寬度,並在括號中顯示像素。
    • 儲存格的高度是以字元和像素為單位顯示,而非以英吋為單位。當您拖曳列名的邊界以調整工作表上的列高時,提示工具會以點為單位顯示高度,並在括號中顯示像素。

下表顯示點和像素轉換成英吋的大約規格。

像素英吋
18240.25
36480.5
72961
1081441.5
1441922

而在網兄 crdotlin 的Excel VBA 的學習經驗分享園地(原文源自對岸OFFICE精英俱樂部論壇)處,看到以下說明:

在工作表的欄與列中:
RowHeight的單位:point
Height的單位:point
ColumnWidth的單位:以"標準字體"的0123456789這10個字元的平均值為計量單位
Width的單位:point
Height與Width可以表示某區域的總高度或寬度。
ColumnWidth的單位與pixel之間的關係:
假設標準字體為:宋體、字型大小12,則每個阿拉伯數字為8個pixel,加上字與列邊的距離左右各2.5個pixel,總共為13個pixel。即ColumnWidth=1換算成pixel為13。同理ColumnWidth=2換算成pixel為21。

由上述得知,欄寬為設定選項中標準字體(個人習慣改成Consolas)之0~9這10個字元的平均值,再加左右各2.5個pixel,也就是5個像素。依照上述標準像素與英吋的對照,1個像素約為0.02645833(2.54/96)公分;欄寬1換算成公分為(2.54/96*標準字體字元平均值)+0.1323(2.54/96*5)。

到這裡換算比率幾乎都找到了,然而卻不知道上述的字元pixel是如何算出的,找了好一會都沒找到適合方法來計算「標準字體字元平均值」,而之前曾在網兄台灣黑熊的「用公分為單位設定Excel中欄寬及列高」這篇文章處,看到他直接以2.54/72*6來計算,算出來跟上述宋字體2.54/96*8是一樣的,我猜想可能是得知12級字字元為6pt(也不知如何得知?經驗?),再以72pt:96px:1inch的比例,換算為公分。

後來看到官方技術支援網頁 "Relationship Between Inches, Picas, Points, Pitch, and Twips" 這個網頁,說到字元的寬度是以PITCH定義的,表示在1英吋中可以排列的字元數量(cpi),通常用於固定字寬的字型。與點(Point)的換算是1 pitch = 120 points = 10 Picas。依照這個定義,2.54cm的寬度可以排進10個12點的字元,那一個字元寬0.254mm;然而每個12級字型都有不同的寬度,這個單位顯然不符合實際。


最後發現了 "How to Use Macros to Set Row Height and Column Width" 這個官方技術支援網頁,可以透過使用巨集,直接以公分或英吋設定列高及欄寬!而且它說Excel採用預設字型,欄寬10表示排列10個,非粗、非斜、10級(point)、Arial字型的"0"字所需要的寬度。但是我實際於選項設定中改以12級Times New Roman做標準字型,再去列10個Arial的0,自動欄寬卻跑出9.7,這真是個模糊的設定啊!!

總之不管了,用官方的算式應該錯不了吧!節錄使用公分的部分巨集於下:

使用下列的巨集,以公分為單位指定列高和欄的寬度。
================ 以公分指定列高巨集碼 ================
Sub RowHeightInCentimeters()
    Dim cm As Single
    ' Get the row height in centimeters.
    cm = Application.InputBox("Enter Row Height in Centimeters", _
        "Row Height (cm)", Type:=1)
    ' If cancel button not pressed and a value entered.
    If cm Then
        ' Convert and set the row height
        Selection.RowHeight = Application.CentimetersToPoints(cm)
    End If
End Sub
================ 以公分指定列高巨集碼 ================


使用下列的巨集,以公分為單位指定欄寬。
================ 以公分指定欄寬巨集碼 ================
Sub ColumnWidthInCentimeters()

    Dim cm As Single, points As Integer, savewidth As Integer
    Dim lowerwidth As Integer, upwidth As Integer, curwidth As Integer
    Dim Count As Integer

    ' Turn screen updating off.
    Application.ScreenUpdating = False
    ' Ask for the width in inches wanted.
    cm = Application.InputBox("Enter Column Width in Centimeters", _
        "Column Width (cm)", Type:=1)
    ' If cancel button for the input box was pressed, exit procedure.
    If cm = False Then Exit Sub
    ' Convert the inches entered to points.
    points = Application.CentimetersToPoints(cm)
    ' Save the current column width setting.
    savewidth = ActiveCell.ColumnWidth
    ' Set the column width to the maximum allowed.
    ActiveCell.ColumnWidth = 255
    ' If the points desired is greater than the points for 255
    ' characters...
    If points > ActiveCell.Width Then
        ' Display a message box because the size specified is too
        ' large and give the maximum allowed value.
        MsgBox "Width of " & cm & " is too large." & Chr(10) & _
            "The maximum value is " & _
            Format(ActiveCell.Width / 28.3464566929134, _
            "0.00"), vbOKOnly + vbExclamation, "Width Error"
        ' Reset the column width back to the original.
        ActiveCell.ColumnWidth = savewidth
        ' Exit the Sub.
        Exit Sub
    End If
    ' Set the lowerwidth and upper width variables.
    lowerwidth = 0
    upwidth = 255
    ' Set the column width to the middle of the allowed character
    ' range.
    ActiveCell.ColumnWidth = 127.5
    curwidth = ActiveCell.ColumnWidth
    ' Set the count to 0 so if it can't find an exact match it won't
    ' go on indefinitely.
    Count = 0
    ' Loop as long as the cell width in is different from width
    ' wanted and the count (iterations) of the loop is less than 20.
    While (ActiveCell.Width <> points) And (Count < 20)
        ' If active cell width is less than desired cell width.
        If ActiveCell.Width < points Then
            ' Reset lower width to current width.
            lowerwidth = curwidth
            ' set current column width to the midpoint of curwidth
            ' and upwidth.
            Selection.ColumnWidth = (curwidth + upwidth) / 2
        ' If active cell width is greater than desired cell width.
        Else
            ' Set upwidth to the curwidth.
            upwidth = curwidth
            ' Set column width to the mid point of curwidth and lower
            ' width.
            Selection.ColumnWidth = (curwidth + lowerwidth) / 2
        End If
        ' Set curwidth to the width of the column now.
        curwidth = ActiveCell.ColumnWidth
        ' Increment the count counter.
        Count = Count + 1
    Wend
End Sub
================ 以公分指定欄寬巨集碼 ================


偷你小小結論
  1. 列高很單純,就是:1列高=28.3465(72/2.54)公分1公分=0.0353(2.54/72)列高
  2. 欄寬較複雜,基本上是「Excel選項設定中的標準字體」之字元寬度(大約8)+左右與框線留空距離(各2.5),單位為像素pixel。
    欄寬值n大約=(2.54/96)*((n*8)+5)公分;也就是欄寬值*0.2117再加0.1323公分
    公分數值cm大約=(cm*96/2.54-5)/8 欄寬值;也就是公分數值減去0.1323再除以0.2117,即為欄寬值
  3. 使用巨集的話,設定好的巨集只會留在單一檔案內,建議可以保留這個檔案,單獨用來查公分對照的欄寬及列高值即可。

其他參考資料:

2 則留言:

  1. 台灣黑熊和你的網誌,我都看過了。基本上,很久以前就試過,其實都很懷疑,特別是黑熊先生的版本。最近因為要更精確的值,所以自己用Excel做了一個測試表。老實講,越弄我越糊塗。

    這個表,在欄寬部分,依據我的顯示器設定的 1920*1080,2007版 Excel中 新明細體 12大小為樣本,我呢....非常精確!基本上格式是這樣:

    模式1 比對

    標準模式-欄寬 setting
    標準模式-欄寬 px
    整頁模式-欄寬 cm
    整頁模式-欄寬 px

    模式2 比對

    整頁模式-欄寬 cm
    整頁模式-欄寬 px
    標準模式-欄寬
    標準模式-欄寬 px

    可以看得出來,模式1 中是用欄寬為標準,從1~12,然後根據數值變化,逐一記錄下來,中間當然會經過標準模式與整頁模式切換,精確去了解,每一個設定上會帶來的數值表現。

    模式2 剛好相反,我用整頁模式 cm為標準,以相反的方式去看標準模式下的欄寬並記錄之。列高的紀錄也很耐人尋味。

    模式1 比對

    標準模式-列高 (十字線)
    標準模式-列高 (儲存格)
    標準模式-列高 px
    整頁模式-列高 cm
    整頁模式-列高 px

    模式2 比對

    整頁模式-列高 cm
    整頁模式-列高 px
    標準模式-列高 (十字線)
    標準模式-列高 (儲存格)
    標準模式-列高 px

    你一定會覺得奇怪,什麼是十字線和儲存格。這意思是,你列與列之間的游標十字線狀態,所看見的值與儲存格內,看見的值是不同的,正因為如此才嚇到!在欄寬裏,沒看見這樣得現象。

    以欄寬8和整頁模式中的8cm的測試報告是這樣:

    模式1 比對

    標準模式-欄寬 setting:8
    標準模式-欄寬 px :79
    整頁模式-欄寬 cm :1.71
    整頁模式-欄寬 px :81

    模式2 比對

    整頁模式-欄寬 cm :8
    整頁模式-欄寬 px :378
    標準模式-欄寬 :40.33
    標準模式-欄寬 px :370

    以下是你的結論:

    欄寬 cm=欄寬值*0.2117+0.1323cm -------> 8*0.2117+0.1323=1.8259 cm
    欄寬值=(公分數值-0.1323)÷0.2117 ------> (8-0.1323)/0.2117=欄寬 37.16

    看出來了嗎!其實根本不合!

    最近在搞排班表A4最適化,所以我有在想,除了%比外,也希望從欄寬與列高下手。因為每個月排班人員與日數可能不同,除了縮放比例外,也希望能從這裏下手,讓系統能更好操作一點。
    很無奈,必須先解開這謎底!而且我認為,這也和螢幕大小與設定的解析度相關,這個一定會左右px,而不是單純微軟自己講的那樣而已.......

    回覆刪除
    回覆
    1. 過了好久才發現這篇留言,為你的認真點讚~
      十年前貼了這篇以後,我持續發現使用不同的螢幕、選用不同字型,或是設定系統內容顯示百分比到125%、150%等,都會影響PX、字元寬度(大約8那個),與左右框線留空距離,最後欄寬部分只能去使用上面那個官方巨集,然而亦曾發生使用巨集但尺寸仍有偏差的經驗,只能多試幾次或是換個設計工具了~

      刪除