Tên bảng Excel Dưới dạng biến

Tôi đã viết một macro để chia một bảng lớn thành nhiều bảng nhỏ hơn trên các trang tính riêng biệt. Khi điều này được thực hiện, tôi muốn đặt hai công thức sumif giống nhau vào cùng một ô trong mỗi trang tính. Mã hiện tại của tôi, đang hoạt động, sử dụng các cột làm tham chiếu

For Each ws In Sheets

    ws.Activate
        Range["A1:A6"].EntireRow.Insert
        Range["F3"].Formula = _
        "=sumif[E:E,""Translation"",H:H]"
        Range["F4"].Formula = _
        "=sumif[E:E,""Translation"",H:H]"
        Range["E3"].Value = "Proofreading"
        Range["E4"].Value = "Translation"
    
    With Range["E3:F4"]
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .BorderAround Weight:=xlMedium
    End With
    
    Range["A1"].Select
    
Next ws

Tuy nhiên, tôi muốn có thể tham chiếu phạm vi đã đặt tên trong bảng, công thức cho phạm vi đó [dựa trên macro đã ghi] sẽ giống như

    Range["F3"].FormulaR1C1 = _
        "=SUMIF[TABLENAME[[#All],[Issue Type]],""Translation"",TABLENAME[[#All],[Word count]]]"

Có thể xác định một biến, chẳng hạn như ActiveSheet. ListObjects[1] và chèn biến vào công thức để khi vòng lặp đi đến từng trang tính, nó sẽ thực hiện phép tính dựa trên bảng trong trang tính đó [thay thế trình giữ chỗ "TABLENAME" trong mã ví dụ của tôi? Tôi đã thử một số . Nếu nó có ích gì, tôi đã tạo nó để bảng trên mỗi trang tính có cùng tên với trang tính

Cảm ơn trước

Trong ví dụ này, mục tiêu là tạo tham chiếu động cho Bảng Excel trong một công thức. Nói cách khác, tạo một công thức có thể tham chiếu đến một bảng Excel theo tên dưới dạng một biến. Cách dễ nhất để làm điều này trong Excel là tập hợp tham chiếu dưới dạng giá trị văn bản bằng cách sử dụng phép nối, sau đó sử dụng hàm INDIRECT để chuyển đổi tham chiếu văn bản thành tham chiếu Excel thích hợp

Trong ví dụ được hiển thị, các công thức trong L5. L7 hoạt động giống như những công thức đơn giản hơn này

=SUM[West[Amount]]
=SUM[Central[Amount]]
=SUM[East[Amount]]

Tuy nhiên, thay vì mã hóa cứng bảng thành từng công thức SUM, tên bảng được liệt kê trong cột K và các công thức trong cột L sử dụng phép nối để tạo tham chiếu cho từng bảng. Điều này cho phép sử dụng cùng một công thức trong L5. L7

Bí quyết là hàm INDIRECT để đánh giá tham chiếu. chúng tôi bắt đầu với

=SUM[INDIRECT[K5&"[Amount]"]]

trở thành

=SUM[INDIRECT["West"&"[Amount]"]]

và sau đó

=SUM[INDIRECT["West[Amount]"]]

Hàm INDIRECT sau đó phân giải chuỗi văn bản thành tham chiếu có cấu trúc phù hợp

=SUM[West[Amount]]

Và hàm SUM trả về kết quả cuối cùng là 27.500 cho miền Tây

Ghi chú. INDIRECT là một hàm không ổn định và có thể gây ra các vấn đề về hiệu suất trong sổ làm việc lớn hơn, phức tạp hơn

tblName được định nghĩa là một ListObject, nhưng thuộc tính Tên của ListObject sẽ trả về một chuỗi. Vì vậy, hoặc bạn cần sử dụng một biến chuỗi để giữ tên danh sách hoặc bạn cần loại bỏ. Tên

 

Jon von der Heyden

MrExcel MVP, Người điều hành

Tham gia ngày 6 tháng 4 năm 2004Tin nhắn10,888Phiên bản Office

1. 365

Nền tảng

1. các cửa sổ

• Ngày 8 tháng 2 năm 2011

• #3

Ngoài ra, tại sao bạn lloping và sau đó gán nó cho một biến sẽ chỉ giữ một biến tại một thời điểm?

 

A

hoạt hình

Thành viên tích cực

JoinedSep 28, 2009Messages396

• Ngày 8 tháng 2 năm 2011

• #4

Trang tính sẽ chỉ có một bảng. Tôi đang lặp lại để lấy tên bảng vì trang tính này sẽ được sao chép [bảng mới được tạo trên trang tính đã sao chép]. Mã tương tự sẽ hoạt động đối với các trang tính đã sao chép. Tất cả những gì tôi cần là lấy tên Bảng của bảng tính đang hoạt động

để thực hiện các tính toán khác nhau. Hướng dẫn này sẽ hướng dẫn các bước để tham chiếu động một tập hợp dữ liệu trong các chức năng khác

Hình 1. Tổng doanh thu cho ba thành phố

Thiết lập dữ liệu

• Chúng tôi sẽ thiết lập dữ liệu bằng cách chèn ngày và bán hàng into Column A to Column F as shown in figure 2. Chúng tôi cũng sẽ đặt tên cho chúng bằng ba thành phố như trong hình.

Hình 2. Dữ liệu hiển thị ba bảng có tên “Texas,” “California” và “Vegas. ”

Tham chiếu động của tên bảng với phạm vi

Chúng tôi sẽ nhập các thành phố vào Ô A11 đến Ô A13 và đặt phạm vi bán hàng . as shown in figure 3.

Hình 3. Gán phạm vi đã chọn cho từng tên bảng

cú pháp

=function[INDIRECT[ref_text]

Công thức

________số 8

Sử dụng hàm SUM và INDIRECT

Chúng tôi sẽ tính tổng doanh số bán hàng của các thành phố khác nhau bằng công thức trên. Chúng tôi sẽ chèn công thức này vào Ô C11

hinh 4. Sử dụng hàm SUM để tham chiếu động tên bảng

Chúng ta sẽ nhấn enter để lấy tổng doanh số.

Hình 5. Tổng doanh thu cho Texas

Chúng tôi sẽ sử dụng trình đơn thả xuống để có được tổng doanh thu cho California và Vegas

Hình 6. Tổng doanh thu cho ba thành phố

Ghi chú

Chúng tôi có thể tìm thấy doanh số bán hàng trung bình bằng cách nhập công thức bên dưới

=AVERAGE[INDIRECT[B11]]

Chúng tôi có thể sử dụng các kết hợp khác với tham chiếu động đến tên bảng

Giải trình

Hàm INDIRECT tạo  tham chiếu động đến tên bảng  “Texas” and returns the values of the Range for the table name, which, in this case, is “B5:B8”. CHỨC NĂNG TỔNG HỢP sau đó tính tổng các giá trị trong Ô B5 đến B8.

Kết nối tức thì với Chuyên gia thông qua Dịch vụ Excelchat của chúng tôi

Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn là một ứng dụng đơn giản của một công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và thất vọng, hãy thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút

Bạn có thể đặt tên bảng trong Excel không?

Tên cột bảng có thể được sử dụng làm tham chiếu trong hàm Excel không?

Tiêu đề bảng có thể là công thức không?

Excel có tự động tạo tên cho bảng không?