視頻鏈接
一、動態圖表實現原理
1.小試牛刀-利用IF創建簡單的動態圖表
1. 目標（通過勾選複選框控制圖表）
2. 步驟
a. 首先插入2個複選框，並編輯複選框名字—“彩盒”、“寵物用品”
b. 如何使複選框與單元格聯動？
點擊右鍵設置“控件格式”，選中一個單元格作為單元格鏈接，則發現勾選複選框顯示TRUE，不勾選複選框顯示FALSE
c. 接下來通過IF函數，實現單元格鏈接與單元格數據的聯動，將IF函數公式剪切到“公式-定義名稱-引用位置”，目的是將“彩盒”這個名稱與IF函數連接起來。
注意，需要全部加上絕對引用
=IF($U$1,$B$2:$B$13,$F$2:$F$13)
d. 最後插入二維折線圖，右鍵點擊“選擇數據”，輸入“系列名稱：彩盒”，“系列值：Sheet1!彩盒”
此時會發現，勾選複選框“彩盒”會顯示彩盒的折線數據，不勾選“彩盒”則不會顯示數據
同理，“寵物用品”的添加也是相同的
但是此時有一個小問題，即分別勾選“彩盒”和“寵物用品”時，縱座標軸的數值在不斷變化，看起來不美觀，可以將其設置成固定的數值
彩盒和寵物用品分別圖例
兩者不夠勾選右鍵設置座標軸，填入自己想要的數字，以大的為準
此時發現無論勾選“彩盒”還是“寵物用品”，縱座標軸的數值均不變
e. 但是此時距離我們完成目標還差一步，在目標中複選框是位於圖例前面的，如下圖：
首先勾選“圖表元素”，即在圖表中顯示圖例
選中圖表，設置為“置於底層”，
然後刪掉複選框中的文字，只留下單獨額複選框
之後將複選框移置圖例前面
至此可實現：通過勾選複選框控制圖表
二、利用Offset函數與控件創建動態圖表
1.Offset函數概述
語法
OFFSET(reference, rows, cols, [height], [width])
參數說明
reference（必需）：
基準單元格或範圍。rows（必需）：
向上或向下偏移的行數（正數向下，負數向上）。cols（必需）：
向左或向右偏移的列數（正數向右，負數向左）。height（可選）：
返回區域的高度（行數）。默認為1。width（可選）：
返回區域的寬度（列數）。默認為1。
返回值
返回一個以reference為起點，經過rows和cols偏移後，指定height和width大小的範圍。
示例用法
a. 基本用法：返回單個單元格
=OFFSET(A1, 2, 3)
以A1為基準，向下偏移2行，向右偏移3列，返回D3的值。
b. 返回一個區域
=OFFSET(A1, 0, 0, 3, 2)
以A1為基準，不偏移，返回一個3行2列的區域，範圍為A1:B3。
c. 動態範圍
=SUM(OFFSET(A1, 1, 1, 5, 1))
以A1為基準，取從A1開始、下移一行右移一列，共5行1列的區域，計算區域的總和。
d. 結合其他函數
=AVERAGE(OFFSET(B2, 0, 0, 3, 3))
以B2為基準，選取一個3行3列的區域，計算區域的平均值。
注意事項
-
OFFSET返回的是一個動態範圍，不能單獨用於直接計算；通常需要與函數（如SUM、AVERAGE等）配合使用。
OFFSET函數是動態的，每次工作表數據變化時都會重新計算，可能對性能造成影響。
如果偏移量超出表格範圍，公式會返回#REF!錯誤。
2.Offset函數的動態引用示例
問題：常規的用數據透視表插入數據無法實現原數據變化透視表跟著變化
首先選中任意一個單元格，點擊“插入-數據透視表-確定”，做一個根據“產品類別”彙總“金額”的數據透視表
此時原數據是125行，若增加3行，即為128行，刷新數據透視表，發現數據並未發生變化。
方法：用offset()函數定義一個變化的數據區域，然後插入數據透視表
首先取一個可變化的數據區域
利用offset函數，以A1為基準，向下移0行，向右移0行，取11列數據-即取到K列，取A列所在的所有非空單元格行，公式=OFFSET($A$1,0,0,COUNTA($A:$A),11)，（counta函數）
然後剪切公式，新建“公式-定義名稱，名稱‘數據區域，引用位置‘所剪切的公式’”
接著“插入-數據透視表-選擇單元格區域：數據區域-勾選新工作表”，選擇“產品類別”和“金額”，此時的總計數據量是207萬多。
然後插入3行新數據（並將其中一個金額改成1000萬），再刷新數據透視表，發現此時數據透視表的總計變成1209萬多。
3.動態圖表1-永遠返回最後10行數據
1. 目標圖表
2. 步驟
取b列最後10個單元格數據，公式 =OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)
COUNTA($B:$B)
統計整列 B 中的非空單元格數量。
如果列 B 中有 15 個非空單元格，那麼 COUNTA($B:$B) 的值為 15。
COUNTA($B:$B)-10
通過減去 10，計算出從第幾行開始的偏移量。例如，如果列 B 有 17 個非空單元格，這部分計算的結果是 17 - 10 = 7，即從第 7 行開始偏移
然後將該公式定義為名稱是“成交量”的一個新公式（該公式只取成交量的倒數10位的數據）
插入空白折線圖，右鍵“選擇數據”，點擊“系列-添加”，輸入“系列名稱：成交量”，“系列值：='圖表2 (2)'!成交量”，此時點擊圖表發現對應原數據最後10行
注：此時多添加幾行結果一樣顯示最後10行
橫軸座標為1-10，我們需要對橫軸做同樣的過程
4.動態圖表2-通過控件控制圖表數據
1.目標圖表
每點一下第1個滾動條，所取數據向下走1行，但取的行數不變，都是20行數據；第2個滾動條控制取多少數據，點一下取22行，再一點取23行、24行、25行…
這兩個滾動條分工不同，分別控制offset中的兩個參數，一個是下移多少行，另一個是取多少行
2.步驟
點擊“開發工具”，插入“表單控制中的滾動條”，右鍵“設置對象格式”，找到控制
首先“插入-窗體-滾動條”，並複製一個，如下圖：
接下來開始寫公式=OFFSET($B$1, $E$3, 0, $E$8, 1)
具體公式參數
$A$1：起始單元格，是偏移計算的參考位置。
$E$3：表示向下偏移的行數，由單元格 F3 的值決定。
0：表示列偏移為 0，即仍在列 A。
$E$8：表示返回的範圍包含的行數，由單元格 F6 的值決定。
1：表示返回的範圍包含的列數為 1 列。
然後將該公式剪切並定義為名稱是“成交量2”的公式，並插入空白柱形圖，右鍵“選擇數據”，添加“系列”，“系列名稱：成交量、系列值：=Sheet10!成交量2”，此時拖動2個滾動條發現柱形圖會隨之變化，至此成交量柱形圖已完成；但是還差日期
日期同理
