Bạn đang xem bài viết Hướng Dẫn Cách Tạo Named Range Động Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Thiết lập Named Ranges
Sau đó, cửa sổ New Name sẽ mở ra
Có vài option quan trọng bạn cần biết:
Name: Nhập tên vùng bạn muốn đặt vào
Scope: Phạm vi sử dụng tên. Với scope workbook, bạn có thể áp dụng tên vừa đặt cho toàn bộ các trang tính. Với các scope nhỏ hơn như các sheet, bạn chỉ có thể áp dụng cho riêng sheet đó mà thôi
Refers to: Vùng muốn áp dụng tên.
Hoàn thành xong việc đặt tên, bạn nhấn OK để áp dụng.
Ta thấy ở ví dụ trên, tên vùng =nameRange (mục “Name”) được gán với vùng =Sheet1$A$1:$H$10 (mục “Refers to”) cố định.
Thiết lập vùng tham chiếu động
Excel sẽ tự động nhập ký hiệu $ (để cố định tham chiếu đến một ô nhất định) dù là không có nhu cầu nào đòi hỏi phải dùng kí hiệu này. Một Named Range có thể được thiết lập có giá trị như ở mục Refers to là =Sheet1$A$1:$H$10. Không có kí hiệu $ thì lúc này, Named Range sẽ trở nên “động”. Vùng đã được thiết lập “động” với ô được chọn tại thời điểm lúc bạn tạo Named Range.
Để chứng minh, bây giờ mình chọn ô C4 rồi đặt tên vùng A1-B5 (không có kí hiệu $).
Giá trị nhỏ nhất trong Named Range là 1 (ô A1) nhưng sau khi tính toán, kết quả lại là 2. Tại vì sao? Bởi Named Range đã “động”, tức là linh động di chuyển xuống 1 ô.
Ta cũng có thể ghép khớp với ký hiệu $ để cố định cột/hàng cụ thể.
Áp dụng Named Range động
Luôn luôn tham chiếu đến ô bên trên
Nếu bạn có công thức mà luôn tham chiếu đến ô bên trên thì điều gì sẽ xảy ra khi bạn thêm 1 côt/hàng nữa?
Range trong công thức không hề thay đổi. Dù định dạng đã được sao lại nhưng công thức thì không.
Vì thế, Named Range động sẽ giải quyết vấn đề này giúp bạn.
Thêm bao nhiêu hàng/cột không quan trọng, công thức sẽ luôn tham chiếu trực tiếp đến ô bên trên.
Đơn giản hóa việc tính toán
Một trong những lợi ích chính của Named Range đó là đơn giản hóa việc tính toán. Ví dụ cho thấy tổng việc tính toán có thể được tạo ra và copied xuống.
Tên vùng D2-D5: totalCalc được gán với vùng =Sheet2!B2+Sheet2!C2. Excel đã tính ra từng kết quả khác cho mỗi ứng dụng của Named Range động.
Tính tổng lũy tiến trong Excel
Tên vùng E2-E5: runningTotal được gán với vùng =Sheet2$D$2:$E$2.
Ta không thể tạo Named Range động toàn cục được. Trong mỗi ví dụ ở trên thì tên của sheet được đặt cho Named Range, vì thế cần phải có 1 cái Named Range động mới cho mỗi Worksheet. Mình đã thấy đôi chút về việc sử dụng ký hiệu “! ” mà không có tên sheet để tạo nên tính toàn cục. Tuy nhiên, điều này có thể gây ra 1 vài trạng thái bất thường và có thể dẫn đến Excel bị phá hủy (kết thúc chương trình/việc tính toán của bạn). Trong những trường hợp như thế, nếu muốn bạn có thể nghiên cứu bằng cách dùng hàm INDIRECT hoặc hàm OFFSET.
Hi vọng thủ thuật nhỏ này sẽ giúp ích cho các bạn trong công việc. Để có thể nâng cao kỹ năng sử dụng Excel, kỹ năng làm báo cáo, Gitiho xin giới thiệu với các bạn khóa học EXG01 – Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ. Hãy tham gia ngay để nhận được nhiều ưu đãi từ hệ thống. Mọi chi tiết xin liên hệ Gitiho.com
Hướng Dẫn Cách Viết Code Với Đối Tượng Range Trong Vba Excel
Cách viết đối tượng Range trong VBA
Range được hiểu là 1 ô hoặc 1 vùng ô trong Excel. Trong VBA có 2 dạng biểu diễn đối tượng Range:
Khi muốn biểu diễn 1 vùng ô mà bạn đã xác định rõ tọa độ của các dòng, cột trong vùng ô đó thì bạn có thể viết theo các cách sau:
Bạn có thể viết Range dựa theo ô ở điểm đầu và ô ở điểm cuối (Range dựa vào Cells để viết)
Dạng thứ 2: Range không cố định địa chỉ
Khi bạn chưa xác định rõ được vùng cần làm việc là vùng nào, dòng bao nhiêu, cột bao nhiêu bởi vùng đó có thể thay đổi trong quá trình làm việc thì bạn phải thông qua các biến. Mỗi khi câu lệnh hoạt động thì biến đó sẽ nhận 1 giá trị, Căn cứ vào giá trị đó để xác định vùng ô mà bạn muốn làm việc.
Cách viết như sau:
Một số phương thức, thuộc tính của Range thường sử dụng trong VBA
Vì Range là đối tượng thường sử dụng trong lập trình VBA nên có rất nhiều phương thức, thuộc tính được sử dụng. Một số cú pháp gọi thuộc tính tiêu biểu là:
Ví dụ thứ 1: Chọn vùng ô A2:D2
Range(“A2:D2”).Select
[A2:D2].Select
Ví dụ thứ 2: Gán giá trị vào 1 vùng ô
Range(“A1”).Value = “abc”
Có nghĩa là gán vào ô A1 đoạn text là abc
Range(“A2:A5”).Value = Range(“B2:B5”).Value
Có nghĩa là lấy giá trị vào vùng A2:A5 tương ứng theo giá trị vùng B2:B5
Range(“D5”).Formula = “=COUNTIF(D1:D4,””abc””)”
Có nghĩa là gán vào ô D5 công thức =COUNTIF(D1:D4,”abc”)
Ví dụ thứ 3: Ẩn dòng, cột
Range(“A1:A5”).EntireRow.Hidden = True
Có nghĩa là ẩn từ dòng 1 đến dòng 5
Range(“A1:F1”).EntireColumn.Hidden = False
Có nghĩa là bỏ ẩn từ cột A tới cột F
Ví dụ thứ 4: Xóa dữ liệu, xóa ô, xóa dòng, cột
Range(“A1:F10”).ClearContents
Có nghĩa là chỉ xóa dữ liệu trong vùng A1:F10
Range(“A1:F10”).Clear
Có nghĩa là xóa toàn bộ dữ liệu và định dạng trong vùng A1:F10
Range(“A2:A5”).Delete
Có nghĩa là xóa toàn bộ các ô trong vùng A2:A5
Range(“A2:A5”).EntireRow.Delete
Có nghĩa là xóa toàn bộ các dòng từ dòng 2 tới dòng 5
Range chỉ là 1 đối tượng nhỏ nằm trong đối tượng lớn hơn là các Worksheet, Workbook. Do đó:
Khi không nói cụ thể thuộc Worksheet nào, Workbook nào thì chúng ta hiểu: Đó là Range thuộc ActiveSheet (Sheet đang được kích hoạt), Sheet này thuộc ActiveWorkbook (Workbook đang được kích hoạt)
Range(“A1”).ClearContents cũng chính là ActiveWorkbook.ActiveSheet. Range(“A1”).ClearContents
Trong khi đó nếu file Excel có nhiều Sheet, rất dễ xảy ra trường hợp ActiveSheet thay đổi liên tục trong quá trình chạy lệnh code, dẫn tới kết quả sai.
Trong trường hợp làm việc với nhiều Workbook cùng lúc, cũng rất dễ xảy ra việc ActiveWorkbook được thay đổi liên tục trong quá trình làm việc, dẫn tới kết quả chạy sai vị trí mong muốn.
Nếu muốn nói 1 Range thuộc 1 Sheet khác, cần nói cụ thể Sheet đó trước khi nhắc tới Range. Nếu muốn nhắc tới Sheet đó thuộc 1 Workbook khác, cần chỉ rõ tên Workbook.
Ví dụ:
Sheet5.Range(“A2:A5”).Select
Chọn vùng A2:A5 của Sheet5, trong Workbook đang làm việc
Workbook(“abc.xlsm”).Sheets(“VD”).Range(“A1”).Value = “123”
Gán giá trị 123 vào ô A1 trong Sheet có tên là VD, thuộc Workbook có tên là abc, loại file là xlsm
ThisWorkbook.Worksheets(1).Range(“VungDK”).ClearContents
Xóa dữ liệu trong vùng được đặt tên (Define Name) là VungDK, vùng này nằm trong Sheet có thứ tự là 1 của Workbook nơi đặt lệnh code.
Khi mới học lập trình VBA, chúng ta nên lưu ý viết đầy đủ cả Workbook và Worksheet khi làm việc với đối tượng Range. Trường hợp chỉ có duy nhất 1 Workbook thì nên viết cụ thể tên sheet chứa Range cần làm việc. Điều này sẽ giúp các bạn tránh nhầm lẫn, biết rõ vị trí kết quả, tránh trường hợp chạy câu lệnh bị sai vùng Range sang sheet khác.
Tìm hiểu sự khác nhau giữa các thuộc tính Text Value và Value2 của đối tượng Range trong VBA Hướng dẫn cách viết thuộc tính OFFSET của đối tượng Range trong VBA Excel Hướng dẫn cách viết thuộc tính RESIZE của đối tượng Range trong VBA Excel Hướng dẫn sử dụng vòng lặp For.. Next trong VBA Hướng dẫn cách sử dụng vòng lặp For Each trong VBA Excel
Hướng Dẫn Cách Khắc Phục Lỗi Excel The Name Already Exists
Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách khắc phục 1 lỗi gây khá nhiều phiền toái trong Excel là ” Name already exists on destination sheet “. Chúng ta sẽ cùng nhau tìm hiểu ý nghĩa, nguyên nhân và cách khắc phục của lỗi này. Áp dụng từ phiên bản excel 2007, 2010, 2013, 2016.
1. Ý nghĩa của lỗi name already exists
Lỗi này bắt đầu bằng việc xuất hiện 1 thông báo như sau:
Trong đó có dòng chữ ” which already exists on the destination worksheet “
Thông báo này được hiểu là:
Công thức hoặc Sheet mà bạn muốn di chuyển hoặc copy có chứa tên (trong Namebox) là ‘…’ (ví dụ tên là Author), tên này đã có sẵn tại 1 sheet trong file mà bạn muốn copy/di chuyển tới (trùng tên trong Namebox). Bạn có muốn sử dụng tên này không? Có 2 cách để chọn:
Để sử dụng tên trong sheet là đích đến, bấm Yes
Để đặt lại tên khác để tránh bị trùng tên, bấm No và đặt lại tên mới trong hộp thoại Name Conflict
Khi bấm No thì sẽ xuất hiện hộp thoại Name Conflict như sau:
Tên cũ: Author (như thông báo ở trước đó)
New name: Đổi sang tên mới. Nhập tên mới vào textbox rồi bấm OK
Nếu không quyết định thay đổi tên mới mà bấm Cancel thì sẽ có thông báo:
Như vậy khi bạn không hiểu rõ lỗi này thì bạn sẽ gặp rất nhiều hộp thoại báo lỗi. Hẳn điều này khiến bạn gặp bối rối phải không nào. Nhưng giờ khi đã hiểu hết ý nghĩa của lỗi này thì việc khắc phục lại không còn khó khăn nữa.
2. Nguyên nhân lỗi name already exists
Xuất phát từ việc sử dụng Name trong Excel. Nguyên tắc đặt tên là không được đặt tên trùng. Khi xuất hiện tên trùng thì Excel sẽ bắt chúng ta:
Đổi sang tên khác thì mới được sử dụng
Quy định lại cho tên mới đó có phạm vi hoạt động hạn chế hơn tên cũ để tránh xung đột khi sử dụng tên.
Chính vì Name được sử dụng rất nhiều trong các công thức, trong VBA nên việc quản lý tên phải thật chặt chẽ. Do đó việc Excel cảnh báo tên trùng sẽ giúp chúng ta tránh được nhiều xung đột trong hệ thống. Tuy nhiên việc này sẽ khiến chúng ta gặp bối rối nếu như không hiểu rõ cơ chế xử lý lỗi Name trùng của Excel.
3. Cách khắc phục lỗi name already exists
Cách 1: Tuân theo hướng dẫn của Excel và Đặt lại tên mới
Để đặt lại tên khác để tránh bị trùng tên, bấm No và đặt lại tên mới trong hộp thoại Name Conflict
Trong hộp thoại Name Conflict bạn đặt lại tên mới. Nếu trùng nhiều tên thì mỗi tên đều phải đặt lại
Cách này thường sử dụng khi số tên bị trùng ít
Cách 2: Quản lý lại tên trong Name Manager
Nếu bạn bỏ qua toàn bộ các thông báo, báo lỗi về việc trùng tên thì bạn phải vào mục Name Manager trong thẻ Formulas để quản lý lại danh sách tên (phím tắt Ctrl + F3)
Cột Name: những tên lạ (chưa từng có trong file ban đầu), những tên trùng nhau,
Cột Refers to: tên chứa lỗi #REF! (lỗi không xác định được tham chiếu của tên) hoặc tham chiếu tới workbook khác, file khác
Khi đó chúng ta có thể sửa hoặc xóa các tên này theo ý muốn. Đảm bảo rằng cần sửa hết các Name lỗi, Name có liên kết tới bên ngoài file.
Hướng Dẫn Cách Tính Trung Bình Động Trong Excel
Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách tính nhanh trung bình động đơn giản trong Excel, sử dụng hàm để tính đường trung bình trong N ngày/tuần/tháng/năm trước, và cách thêm đường trung bình động vào biểu đồ Excel.
Trung bình động là gì?
Nhìn chung, trung bình động được định nghĩa là một chuỗi các giá trị trung bình từ các tập hợp giá trị khác nhau trong cùng một bộ dữ liệu.
Trung bình động thường được sử dụng trong thống kê, dự báo chu kỳ thay đổi kinh tế và dự báo thời tiết để hiểu được xu hướng của chúng. Trong giao dịch chứng khoán, trung bình động là một chỉ số thể hiện giá trị trung bình của chứng khoán qua các thời kỳ. Trong kinh doanh, đó là một nghiệp vụ tính trung bình doanh thu của 3 tháng trước để dự đoán xu hướng gần đây.
Ví dụ, trung bình động của nhiệt độ trong ba tháng có thể tính bằng cách tính nhiệt độ trung bình của tháng 1 đến tháng 3, sau đó tính nhiệt độ trung bình của tháng 2 tới tháng 4, rồi tháng 3 tới tháng 5…
Cách tính trung bình động giản đơn trong Excel
Có 2 cách tính trung bình động giản đơn trong Excel – bằng công thức và bằng tùy chọn khuynh hướng. Các ví dụ sau đây sẽ minh họa cho cả hai kỹ thuật này.
Ví dụ 1. Tính trung bình động qua một thời kỳ cụ thể
Trung bình động giản đơn có thể được tính bằng hàm AVERAGE. Giả sử bạn có một danh sách trung bình nhiệt độ hàng tháng trong cột B, và bạn muốn tìm trung bình động cho ba tháng (như hình trên)
Viết công thức bình thường cho 3 giá tri đầu tiên và nhập nó vào ô thứ 3 từ trên đếm xuống (ví dụ ô C4), sau đó sao chép công thức sang các ô khác trong cột: =AVERAGE(B2:B4)
Bạn có thể cố định các ô (như ô B2) nếu bạn muốn, nhưng cũng hãy sử dụng các tham chiếu hàng không cố định để công thức được điều chỉnh phù hợp cho các ô khác nhau.
Hãy nhớ rằng trung bình cộng được tính bằng cách tính tổng các giá trị sau đó chia cho số các giá trị được tính trung bình, bạn có thể xác nhận kết quả bằng công thức :
Ví dụ 2. Tính trung bình động cho N ngày/tuần/tháng/năm cuối trong một cột
Giả sử bạn có một danh sách các dữ liệu, cụ thể là doanh số bán hàng hay giá cổ phiếu, và bạn muốn biết trung bình của ba tháng cuối tại một thời điểm bất kỳ. Để thực hiện điều này, bạn cần một công thức tính toán lại trung bình ngay sau khi nhập vào giá trị cho tháng tiếp theo. Hàm lồng ghép với hàm và.
=AVERAGE(OFFSET(first cell, COUNT(entire range)-N,0,N,1))
N là số ngày/tuần/tháng/năm trước.
Giả sử các giá trị trung bình được tính từ hàng 2 cột B, công thức sẽ như sau: =AVERAGE(OFFSET(B2,COUNT(B2:B100)-3,0,3,1))
Hàm COUNT “COUNT(B2:B100)” đếm có bao nhiêu giá trị được nhập vào cột B. Chúng ta bắt đầu đếm từ ô B2 vì ô B1 là tiêu đề.
Hàm OFFSET lấy giá trị ở ô B2 (tham số thứ nhất) là ô bắt đầu, sau đó tịnh tiến theo giá trị được trả về bởi hàm COUNT 3 dòng lên phía trên (trong trường hợp này: -3 – tham số thứ 2 của hàm offset). Kết quả là, công thức tính tổng của 3 giá trị trong vùng gồm 3 dòng (3 – ở trong tham số thứ 3 của hàm offset) và 1 cột (1 – tham số cuối cùng của hàm OFFSET), kết quả này chính là tổng của 3 tháng cuối cùng mà chúng ta muốn
Cuối cùng, kết quả tổng được hàm AVERAGE sử dụng để tính trung bình động.
Nếu bạn làm việc với trang tính luôn cập nhật các hàng mới, hãy đảm bảo hàm có đủ số hàng để chứa dữ liệu mới. đó không phải là vấn đề bạn chèn nhiều cột hơn bình thường ngay sau khi bạn có ô tính đầu tiên, bởi thế nào hàm cũng loại bỏ tất cả các hàng rỗng
Trong ví dụ, bảng dữ liệu chỉ chứa dữ liệu trong 12 tháng, nhưng chúng ta đã trừ hao cho hàm COUNT vùng dữ liệu B2:B100.
Ví dụ 3. Tính trung bình động cho N giá trị cuối trong một hàng
Nếu bạn muốn tính trung bình động cho N ngày/tháng/năm cuối trong cùng một hàng, bạn chỉ cần điều chỉnh công thức OFFSET như sau:
=AVERAGE(OFFSET(first cell,0,COUNT(range)-N,1,N,))
Giả sử ô B2 chứa số liệu đầu tiên trong hàng, và bạn muốn tính trung bình động cho 3 số liệu cuối hàng, công thức sẽ như thế này:
Nếu bạn đã tạo một biểu đồ, việc thêm đường trung bình động cho biểu đồ rất nhanh chóng. Chúng tôi sẽ hướng dẫn bạn sử dụng tính năng Excel Trendline theo các bước sau đây.
Bảng Format Trendline hiển thị phía bên tay phải trang tính 2013 và hộp thoại tương ứng sẽ xuất hiện trong Excel 2010 và 2007.
Trên bảng Format Trendline, nhấp vào biểu tượng Trendline Options¸chọn Moving Average và nhập khoảng thời gian vào hộp :
Đóng bảng Trendline, đường trung bình động sẽ được thêm vào biểu đồ như sau:
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Tạo Named Range Động Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!