跳到主要內容

如何在Excel中創建動態打印區域?

通常,在設置工作表後,打印區域是恆定的。 在某些情況下,您希望打印區域隨時隨您刪除或添加的打印內容進行調整。 如何實現呢? 在本文中,我們將向您展示在Excel中創建動態打印區域的方法。

在Excel中創建動態打印區域


箭頭藍色右氣泡 在Excel中創建動態打印區域

如下面的屏幕截圖所示,假設您的正常打印區域是A1:E5,但是范圍數據可能會一直增長到第10行和G列。您可以按照以下步驟創建動態打印區域。

1.首先需要創建一個動態的命名範圍。 請點擊 公式 > 名稱管理員。 看截圖:

2。 在裡面 名稱管理員 對話框中,單擊 全新 按鈕打開 新名字 對話框。 在“新名稱”對話框中,輸入 打印面積公式姓名 框,然後輸入公式 =OFFSET($A$1,0,0,COUNTA($A$1:$A$10),COUNTA($A$1:$G$1)) 框,最後單擊 OK 按鈕。 看截圖:

備註: 在公式, 10澳元$ G $ 1 表示打印區域不會大於第10行和G列。您可以根據需要進行更改。

3.返回到 名稱管理員 對話框,請關閉它。

4.選擇要設置為打印區域的數據范圍(在這種情況下,我們選擇 A1:E5),然後點擊 頁面佈局 > 打印區 > 設置打印區域。 看截圖:

5。 點擊 公式 > 名稱管理員 打開 名稱管理員 對話框。

6。 在裡面 名稱管理員 對話框中,選擇 打印區域 ,在 姓名 框,然後將原始公式替換為 = 打印區域公式 (您在上面創建的動態範圍的名稱) 框,然後單擊 按鈕保存更改。 最後關閉 名稱管理員 對話框。

現在創建了動態打印區域。 您可以看到打印區域正在​​根據您添加或刪除的數據進行調整,直到到達指定的行和列為止。 看截圖:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (10)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Plain and simple, I've tried it a dozen times, made adjustments and still doesn't work. It won't take the formula. It keeps reverting, which means its reading the formula as an error. Don't get it. The name of my sheet is Contract Invoice. I don't see where you put that in the formula. I've tried replacine the COUNTA with the name of the sheet, tried putting it before and after COUNTA, but still nothing. My email is obviously listed above. If anyone has any suggestions, I'll try them.
This comment was minimized by the moderator on the site
Hi John,
If you wanted a print area for all non-blank rows, you might have something like
=OFFSET('Contract Invoice'!$A$1:$S$1,0,0,MAX(IF('Contract Invoice'!$A$1:$A$1005<>"",ROW('Contract Invoice'!$A$1:$A$1005),0)))

or whatever you like. The sheet name in the formula shouldn't matter.

You'd then define this name (say =my_print_area) as above mentioned above and set the "scope" to the workbook.

Then define your Print_Area to link to this formula and set the Scope to "Contract Invoice".

Hope that helps.
This comment was minimized by the moderator on the site
This was the best solution to this problem I found, after hours of searching
Rated 5 out of 5
This comment was minimized by the moderator on the site
this is definitely super usefull. However, each time I close and reopen the file, the Print_Area name configuration is gone. So each time I have to go to Define Names, and say Print_Area = Print_Area_Formula. Someone else with the same issue?? Thx!
This comment was minimized by the moderator on the site
Yeah, this is the only problem, but it only seems to happen when you open the Page Layout settings. Otherwise, it seems to be fine.
This comment was minimized by the moderator on the site
Ca ne fonctionne pas. A chaque fois qu'on faire le gestionnaire de noms et qu'on rentre dedans, il a remplacé la formule par la zone area de départ
This comment was minimized by the moderator on the site
Hi, just one thing, is your formula dynamic?. Would this be better =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))?

Then when the pivot changes or cells or columns are added the formula does not need to change.
This comment was minimized by the moderator on the site
First of all, thanks a lot!


Secondly: you don't need to define two "names" in the Mame Manager. As a simpler alternative, just select some random cells and set the print area (on those random cells), then open the Name Manager: it will have the print_area already in: here you can replace the content with the formula you want.
This comment was minimized by the moderator on the site
hi
thank you, it is great.

is there any way to have a conditional reference for the offset? I mean use the offset from the first cell in column A that contains a letter A, or equal to number 3 or filled by the color yellow.

I tried the following formula but it doesn't work.

offset(indirect(CELL("address",INDEX(A:A,MATCH("A",A:A,0)))),0,1,COUNTA(Sheet1!$b+Sheet1!$b:$b),7)

thanks

Mike
This comment was minimized by the moderator on the site
Hello,Its useful &exciting.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations