Các nhãn và các tên trong công thức

  • Thread starter handung107
  • Ngày gửi
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#1
Hôm nay tôi lang thang một vòng quanh khu vườn EFC của chúng ta, và tự nhiên thấy loạt bài về Excel Fan của vongphuc cũng như tuanktcdcn bị bỏ lửng mà tiếc quá, có rất nhiều đề tài bé bé xinh xinh mà chúng ta không chú ý đến, vì thế, tôi muốn trở lại từng chủ đề còn bỏ sót để từ từ ghép lại cho hoàn chỉnh.
Loạt bài này mong được các bạn bổ sung để đưa vào :

Phần 2 - Nguyên tắc chung khi làm việc trên Excel

Bài 7 : Sử dụng tên - Đặt tên

Những qui định về việc đặt tên cho các ô, các công thức, các hằng :
1. Các ký tự được phép : Ký tự đầu tiên của 1 tên phải là mẫu tự, hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là các mẫu tự, các số, các dấu chấm, và các ký tự gạch dưới. Td : _01_01_04 hay HH_01
2. Các tên không thể là một tham chiếu ô . Td : R1C1 hay A$1
3. Có thể dùng nhiều từ, nhưng không có khoảng trống. Các ký tự gạch dưới hay dấu chấm có thể được dùng để nối từ. Td : Product_01 hay Product.01
4. Số ký tự : Một tên có thể lên đến 255 ký tự
5. Loại kiểu chữ dùng trong tên : Excel không phân biệt ký tự in hoa và ký tự thường trong các tên

Sử dụng các nhãn và tên :

Các sheet thường có các nhãn ở đỉnh của mỗi cột và phía bên trái của mỗi hàng nhằm mô tả dữ liệu ở bên trong của nó. Bạn có thể sử dụng các nhãn này trong các công thức lúc bạn muốn tham chiếu đến các dữ liệu liên quan. Bạn cũng có thể đặt các tên mô tả để dễ gợi nhớ cho các Cell, dãy các Cell, các công thức hay các hằng.
Lưu ý : Excel không nhận ra các nhãn trong công thức nên bạn hãy vào Tools/Options/Calculation. Bên trong Workbook options bạn hãy chọn hộp kiểm Accept labels in formulas

Đặt tên các Cell trong một bảng tính :
Chọn ô, dãy các ô mà bạn muốn đặt tên.
- Đặt tên một ô hoặc một dãy các ô.
Nhấp hộp Name ở bên trái ngoài cùng của thanh công thức rồi gõ nhập tên muốn đặt. Nhấn Enter
- Đặt tên các ô bằng cách sử dụng các nhãn hàng và cột hiện có : Nếu dãy ô của bạn có sẵn tiêu đề cột hay hàng ở ngay bên trên, dưới, trái, phải bạn có thể vào Menu Insert/Name/Create và chọn dấu kiểm Top row, Bottom row, Left Column, Right Column.
Tên được tạo ra bằng thủ tục này chỉ tham chiếu đến các ô có chứa giá trị, không chứa các nhãn hàng và cột đang có
- Đặt tên thông qua Insert/Name/Define hay dùng tổ hợp phím tắt Ctrl+F3, hôp thoại Name Define hiện ra, Trong Names in workbook bạn gõ tên muốn đặt, trong Refers to : bạn gõ vùng tham chiếu. Nếu đặt tên cho một hằng hay một công thức thì hộp thoại Refers to bạn sẽ gõ hằng hay công thức vào đây.
-Đặt nhãn thông qua hộp thoại Insert/Name/Label. Thường được sử dụng để đặt nhãn cho một cột có tiêu đề, chọn từng cột luôn cả tiêu đề, add vào hộp thoại Add Label Range/Add/Ok. Nhãn của vùng sẽ lấy theo tiêu đề cột.
Tên và nhãn sẽ được sử dụng rất nhiều và hiệu quả mà chúng ta sẽ tuần tự đề cập đến trong những phần sau - Sức mạnh của tên
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#2
So sánh Label và Name

- Nhãn (Label) được tạo ra khi bạn sử dụng hộp thoại Insert/Name/Label.
- Tên (Name) : đã nói ở phần trên.
So sánh :
- Khi bạn đã đặt tên cho 1 ô, dãy ô thì trong hộp thoại Name Define sẽ hiển thị. Nhưng nhãn không hiển thị tại đây.
- Để nhãn có thể áp dụng được trong công thức, bạn phải đặt dấu kiểm ở Tool/Option/Calculation/Accept Labels in Formula.
Bây giờ tôi giả sử có 1 mảng gồm 4 cột và 3 hàng (A1:D3). Từ A1 đến D1 là tiêu đề cột gồm (B1 : Product 1; C1 : Product 2; D1 : Product 3). Từ A2 đến A3 là tiêu đề hàng gồm (A2 : East; A3 : West). Các ô còn lại bạn lần lượt nhập số lượng cho từng sản phẩm. Tôi giả sử các số hạng trong dãy B2:D3 lần lượt từ 10,20,30,40,50,60.
Bạn hãy chọn mảng B1 : D3 rồi vào Insert/Name/Create, chọn Top Row và mảng A2 : D3 chọn Left Column. Vậy là bạn đã đặt các tên cho từng dãy B2:B3 (ProDuct_1), C2:C3(Product_2), D2:D3(Product_3), B2:D2(East), B3:D3(West). Nếu bạn mở hộp Name, bạn sẽ thấy đủ 5 tên này.
Nhưng nếu đặt nhãn, bạn phải chọn từng cột hay từng hàng một. Td B1:B3 (nhãn sẽ là Product 1 cho dãy B2:B3), C1:C3, D1:D3. Và tất cả các nhãn này không có mặt trong hộp tên.
Nếu bạn thu bảng tính lại còn 39% hay nhỏ hơn, Excel sẽ bổ sung một đường biên xung quanh nhãn mà bạn đã chỉ định với lệnh Label Ranges cùng tên nhãn của từng dãy. Đường biên này không được in ra và nó cũng không hiển thị khi phóng bảng tính lớn hơn 39%
Khi bạn sử dụng công thức SUM(Product_1) để tính tổng là bạn sử dụng tên của dãy B2:B3. Nhưng nếu bạn nhập SUM(Product 1) để tính tổng là bạn sử dụng nhãn, cả 2 đều cho ra kết quả như nhau.
Nếu bạn muốn xem số lượng Product 3 ứng với vùng East nghĩa là dữ liệu ở ô
D2, bạn chỉ cần nhập công thức sau : = Product_3 East (S/d tên) hay =Product 3 East (s/d nhãn) bạn sẽ được kết quả giống như nhập các hàm tham chiếu dò tìm sau :
=INDEX(B2:D3,1,3)
=INDIRECT(ADDRESS(2,4))
Hẹn các bạn lần sau, chúng ta sẽ nói thêm về Sức mạnh của tên và nhãn
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#3
Đặt tên cho vùng dữ liệu động (Dynamic Ranges)

Từ bài này trở đi, tôi không đề cập đến nhãn nữa, vì tên thường được sử dụng nhiều hơn. Khi xử lý dữ liệu liên quan đến nhiều vùng, ô, ta thường viết theo quy tắc Góc trái trên : Góc phải dưới.
Có 2 loại vùng : Vùng cố định và vùng di động.
- Đặt tên vùng cố định : đánh vào hộp Refer to công thức xác định vùng
Td : 'Sheet1'!$A$1:$H$300, rồi Add. (Góc trái trên và góc phải dưới của vùng cố định không nhất thiết phải là địa chỉ tuyệt đối, tuỳ thuộc bạn áp dụng công thức đối với tên vùng)
Nếu đặt tên vùng cố định bằng địa chỉ tương đối, bạn phải làm chủ được sự sai lệch địa chỉ khi copy công thức có tên vùng từ ô này sang ô khác.
Thông thường, ta hay sử dụng vùng di động, nghĩa là khi ta bổ sung thêm dữ liệu vào vùng thì tên vùng sẽ tự cập nhật theo.
- Đặt tên vùng di động : Trong hộp Refer To ta nhập công thức sau :
=OFFSET('Tên Sheet'!Góc trái trên,0,0,COUNTA($Cột:$Cột), Độ rộng vùng)
Trong đó :
*Tên Sheet chứa vùng
*Địa chỉ cố định góc trái trên của vùng
*Độ lệch ban đầu của góc trái trên theo hàng
*Độ lệch ban đầu của góc trái trên theo cột
*Chiều cao của vùng được tính bằng cách đếm cột căn cứ
*Độ rộng của vùng bằng cách đánh vào một số cụ thể
Nhập công thức xong, bạn cũng Add để đóng hộp thoại.
Bây giờ, tôi giả sử vùng dữ liệu của tôi gồm : Cột A (Họ và Tên), Cột B (Địa chỉ), Cột C (Mã số thuế) trên Sheet tên DMKH và tôi bắt đầu nhập liệu từ hàng thứ 2 trở đi
Đặt tên :
HOTEN : =OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),1)
DIACHI : =OFFSET('DMKH'!$B$2,0,0,COUNTA($A:$A),1)
MST : =OFFSET('DMKH'!$C$2,0,0,COUNTA($A:$A),1)
Và tên CSDL là :
DL==OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),3) (CSDL có 3 cột)

Nếu tôi muốn vùng DL này được mở rộng cả về phía bên phải của cột C thì :

DL : =OFFSET('DMKH'!$A$2,0,0,COUNTA('DMKH'!$A:$A),COUNTA('DMKH'!$1:$1)).
Chúng ta đã đặt tên xong cho vùng cố định và di động, tên này được sử dụng thoải mái cho bất kỳ Sheet nào trong Workbook. Excel còn có một chức năng rất hay mà chúng ta lại sử dụng nó rất mờ nhạt : đó là đặt tên cho các công thức. Khi bạn đã làm chủ được việc đặt tên này, bạn sẽ thấy độ dài của một công thức không còn phải đánh dài vô tội vạ nữa, và thuật toán sẽ trở nên thật gọn gàng và dễ hiểu. Bạn sẽ thấy Excel cho bạn những kết quả hơn cả bạn mong đợi nữa. Hẹn các bạn lần sau nhé.
 
Sửa lần cuối:
L

levanduyet

Welcome
16/10/04
535
11
18
HCM
my.opera.com
#4
To: Chi Handung107
Bài của Chị quá tuyệt, chắc chắn rằng sau khi cải tổ box EFC Chị phải là người phụ trách một mảng trong đó.
Có bạn nào đồng ý với ý kiến của mình thì xin có ý kiến !!!
Lê Văn Duyệt
 
B

Bình_OverAC

Over Abnormal / Crazy
14/5/04
845
7
18
36
Nha Trang
#5
Bây giờ thì em mới hiểu được hết cách sử dụng hàm OFFSET. Cám ơn chị Handung nhiều. Hôm vừa rồi không gặp được chị tiếc quá.
Càng lúc em càng thấy yêu EFC của mình các anh chị nhỉ.
Em sẽ cố gắng học hỏi và post bài nhiều hơn
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#6
Dynamic Ranges (tt)

Tôi giả sử cột dữ liệu của tôi là cột A và nhập liệu từ hàng 1 gồm những dạng Format khác nhau vừa Text, vừa Number. Để đặt tên cho cột a, tôi dùng hộp thoại Insert/Name/Define như trên.

Trong hộp Refers to nhập :

1/ Vùng dữ liệu mở rông về phía dưới đến hàng có số dưới cùng :

=OFFSET($A$1,0,0,MATCH(1E+300,$A:$A,1),1) (1E+300 : số 1 và 300 số 0)

(Nếu bạn nhập 1 số nhỏ, td :20, vùng mở rộng sẽ kéo xuống hàng dưới cùng có số 20)

2/Vùng dữ liệu mở rộng về phía dưới đến hàng có dạng Text ở dưới cùng :

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

3/Vùng dữ liệu mở rông về phía dưới dựa trên giá trị của 1 Cell Td : Ở Cell B1 ta nhập số 40

=OFFSET($A$1,0,0,$B$1,1) Vùng dữ liệu sẽ là : A1:A40

4/ Vùng dữ liệu động nằm trong một vùng dữ liệu khác : Giả sử tại cột A tôi có một danh sách được sắp xếp theo thứ tự tăng dần (A:Z). Bây giờ, chúng ta sẽ đặt tên cho một vùng động gồm những tên bắt đầu bằng chữ "H", nghĩa là vùng bắt dầu từ tên đầu tiên và kết thúc bằng tên cuối cùng có chữ "H". Để thực hiện, chúng ta đặt vào hộp Refers to công thức :

=OFFSET(INDIRECT(ADDRESS(MATCH("H*",Sheet1!$A$2:$A$1000,0)+1,1)),0,0,COUNTIF(Sheet1!$A$2:$A$1000,"H*",1)
Chúng ta bắt đầu bằng Cell A2 để có tiêu đề cột

Để thấy rõ vấn đề này, chúng ta hãy nhập một số tên trong cột A và sắp xếp tăng dần, và chắc chắn rằng trong số những tên này có một số tên bắt đầu bằng chữ "H". Ta add công thức trên vào hộp Refer to xong, gọi tên dãy bằng cách nhấn F5 và trong hộp thoại Go To / Reference nhập tên dãy. Click OK, sau đó, nhập thêm một số tên và sắp xếp lại, gọi lại hộp thoại Go To / Reference để xem kết quả.
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#7
Tên của dãy chính là tên của một công thức

Bạn có nhận thấy điều này không ? Khi bạn tạo ra một cái tên, cũng là bạn tạo ra một công thức, công thức này không ở trên Cell đó, nó tồn tại trong bộ nhớ của Excel.
Khi bạn làm việc với hộp thoại Define Name Dialog Box, tại Refers to sẽ chứa công thức và tại Name in Workbook chứa tên của công thức. Khi bắt đầu nhập vào hộp Refers to bạn luôn luôn phải đặt dấu = trước.
Td : Tại Name in Workbook đặt tên là TS và Refers To : =Sheet1!$B$3 (nghĩa là Cell B3 có tên là TS)
hay Name in workbook là TG và Refes to : =Sheet1!$B$3:$B$12 (nghĩa là dãy này có tên là TG
Còn trong Name in Workbook bạn đặt : TC
Và Refers to : = A4 + A5 + A6 (nghĩa là tên của công thức trên là TC).
Nếu bạn chấp nhận được điều này, chúng ta sẽ dễ dàng tạo ra những công thức phức tạp hơn và đặt tên cho chúng, công thức của chúng ta sẽ trở nên gọn nhẹ, và khi sửa chữa, chúng ta cũng dễ dàng thao tác hơn.
Chúng ta sẽ bàn về Sức mạnh của tên :
1/ Tên của Cell, Dãy các Cell, công thức sẽ được sử dụng tại mọi sheet trong Workbook. Việc này giúp bạn thuận lợi trong việc đặt các DS (Validation), viết Code trong VBA, làm công thức trong sáng, dễ hiểu, dễ thực hiện.
Td : tính điểm trung bình của môn học. CSDL gồm cột A là cột STT, cột B là cột Họ và Tên, 3 cột (C5:Ex ) điểm kiểm tra miệng, 3 cột (F5:Hx) kiểm tra 15 phút (hệ số 1), 4 cột (I5:Lx) kiểm tra 1 tiết (hệ số 2), 1 cột (M5:Mx) kiểm tra học kỳ (hệ số 3). Cột N là cột tính điểm TB môn.
N5=SUM(C5:M5)+SUM(I5:M5)+SUM(M5)/(COUNTA(C5:M5)+COUNTA(I5:M5)+COUNTA(M5))
Công thức trên thì quá khó hiểu, và chúng ta sẽ đặt tên cho chúng như sau :
Đặt con trỏ tại hàng thứ 5, và đặt :
*TONG = SUM($C5:$M5)+SUM($I5:$M5)+SUM($M5)
* DEM = COUNTA($C5:$M5)+COUNTA($I5:$M5)+COUNTA($M5)
* DTB = TONG/DEM
Bây giờ, ở bất kỳ hàng nào ta chỉ việc đánh =DTB sẽ ra điểm trun gbình của mỗi học sinh.
2/ Tên của công thức cũng giúp bạn tránh được việc nhập Ctrl+Shift+Enter khi nhập công thức mảng.
Td : Công thức mảng sau đây sẽ trả về TRUE nếu từ A1:A11 sắp xếp theo thứ tự tăng và trả về FALSE nếu ngược lại
{=AND(A2:A11>A1:A10)}
Nếu ta đặt tên cho công thức là Thutu =AND(A2:A11>A1:A10) thì sau khi đặt tên cho công thức xong, bạn sử dụng như bình thường, nghĩa là chỉ cần nhập =Thutu, không cần nhập Ctrl+Shift+Enter nữa.
3/Bạn cũng biết trong công thức IF(dk,True,False), bạn chỉ được quyền sử dụng tối đa 7 vòng lặp.
= IF(dk1,gt1,IF(dk2,gt2,IF(dk3,gt3,IF(dk4,gt4,IF(dk5,gt5,IF(dk6,gt6,IF(dk7,gt7,"")))))))
Có nhiều cách khác nhau để sử dụng được nhiều hơn 7 vòng lặp đó, chúng ta sẽ bàn vào lúc khác. Nhưng một trong những cách đó là sử dụng tên cho công thức.
Td : Ta muốn đặt công thức sau :
Nếu B1=1 kết quả là "Một", B1=2 là "Hai", B1=3 là "Ba", B1=4 là "Bốn"...đến B1=12 là "Mười hai". Ta đặt tên như sau :
Đặt con trỏ ở hàng 1, vào hôp thoại ta gõ công thức :
MotDenSau = IF(B1=1,"Một",IF(B1=2,"Hai",IF(B1=3,"Ba",IF(B1=4,"Bốn",IF(B1=5,"Năm",IF(B1=6,"Sáu","Không thấy))))))
Sau đó, đặt tên tiếp cho công thức thứ hai là MotDenMuoiHai
MotDenMuoiHai = IF(B1=7,"Bảy",IF(B1=8,"Tám",IF(B1=9,"Chín",IF(B1=10,"Mười",IF(B1=11,"Mười Một",IF(B1=12,"Mười Hai",MotDenSau))))))
Cuối cùng, công thức của bạn sẽ thật gọn đến không ngờ :
=IF(B1="","",MotDenMuoiHai)
4/Ở phần đầu của loạt bài này, tôi có đề cập đến mảng gồm 4 cột và 3 hàng (A1:D3). Từ A1 đến D1 là tiêu đề cột gồm (B1 : Product 1; C1 : Product 2; D1 : Product 3). Từ A2 đến A3 là tiêu đề hàng gồm (A2 : East; A3 : West). Các ô còn lại bạn lần lượt nhập số lượng cho từng sản phẩm.
Nếu bạn muốn xem số lượng Product 3 ứng với vùng East nghĩa là dữ liệu ở ô
D2, bạn chỉ cần nhập công thức sau : = Product_3 East.
Đó cũng là một trong những ưu điểm của việc đặt các tên trong công thức và chúng ta cần khai thác hết những sức mạnh này. Hẹn các bạn dịp sau
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#8
Sử dụng tên cho những File đã có trên WKT

Chắc các bạn ai cũng có File NXT Hàng Hoá và File NK Chung Tu của thanhnhan, vì tôi thấy File này được các bạn thảo luận và xin gửi File rất nhiều. Bạn hãy mở File NK Chung Tu / Sheet NKchungTu.
Cột A của Sheet này được dùng để điền tự động các loại Mã PN, PX, DT (nếu các bạn không thấy là do tác giả Hide cột này đi và dùng Font chữ màu trắng)

Công thức của bác Thanhnhan là công thức mảng, bạn phải nhập Ctrl+Shift+Enter.

Tại A5 : {=IF(AND(D6<>"",F6=F5,J6=J5),A5,IF(OR($H6=1561,$H6=1551,$H6=1521),"PN"&TEXT(MAX(IF(LEFT($A$5:A5,2)="PN",RIGHT($A$5:A5,3),0)+1),"000"),IF(OR($I6=1561,$I6=1551,$I6=1521),"PX"&TEXT(MAX(IF(LEFT($A$5:A5,2)="PX",RIGHT($A$5:A5,3),0)+1),"000"),IF($I6=5111,"DT"&TEXT(MAX(IF(LEFT($A$5:A5,2)="DT",RIGHT($A$5:A5,3),0)+1),"000"),""))))}

Công thức quá dài và chúng ta sẽ dễ bỏ sót các dấu ngoặc. Tôi sẽ giới thiệu với các bạn cách đặt tên cho công thức này

- Các bạn đặt con trỏ ngay tại A5. Vào Insert Name, bạn lần lượt đặt :
1/ SoPhieuDT :

=TEXT(MAX(IF(LEFT(NKChungTu!$A$5:A5,2)="DT",RIGHT(NKChungTu!$A$5:A5,3),0)+1),"000")

2/SoPhieuXuat :

=TEXT(MAX(IF(LEFT(NKChungTu!$A$5:A5,2)="PX",RIGHT(NKChungTu!$A$5:A5,3),0)+1),"000")

3/SoPhieuNhap :

=TEXT(MAX(IF(LEFT(NKChungTu!$A$5:A5,2)="PN",RIGHT(NKChungTu!$A$5:A5,3),0)+1),"000")

4/MaPhieuDT :

=IF(NKChungTu!$I6=5111,"DT"&SoPhieuDT,"")

5/MaPhieuXuat :

=IF(OR(NKChungTu!$I6=1561,NKChungTu!$I6=1551,NKChungTu!$I6=1521),"PX"&SoPhieuXuat,MaPhieuDT)

6/MaPhieuNhap :

=IF(OR(NKChungTu!$H6=1561,NKChungTu!$H6=1551,NKChungtu!$H6=1521),"PN"&SoPhieuNhap,MaPhieuXuat)

7/MaPhieu :
=IF(AND(NKChungTu!D6<>"",NKChungTu!F6=NKChungtu!F5,NKchungtu!J6=NKChungTu!J5),NKChungTu!A5,MaPhieuNhap)

Công thức trên được đặt tên theo từng đoạn một, và cuối cùng, bạn chỉ cần gõ vào Cell A5 = Maphieu, không cần nhập Ctrl+Shift + Enter nữa, bạn hãy thử làm lại xem. (Tên công thức hơi khó nhìn vì tôi muốn kèm thêm tên Sheet cho chính xác).
Và bạn thấy đó, khi đã đặt tên cho công thức, ta không cần sử dụng công thức mảng...Đó chính là Sức mạnh của tên trong công thức.
 
Sửa lần cuối:
W

WhoamI

Thành viên thân thiết
#9
Cảm ơn chị!
Thật là tuyệt vời! mặc dù em vẫn chưa hiểu được hết!
Nhưng chỉ cần ứng dụng một phần trong cách đặt nhãn và tên, đặc biệt là đặt tên cho công thức là đã giải quyết được nhiều việc rồi, còn lại chỉ là kỹ thuật xử lý của từng người thôi!
 
W

WhoamI

Thành viên thân thiết
#10
Vì em chưa biết tạo UDF nên cái vụ đặt tên cho công thức này em thấy rất thích. Nhưng có một bất tiện là khoảng cách giữa ô đặt công thức đến ô tham chiếu là cố định, và việc dùng tên công thức đó trên nhiều sheet thì phải để ý rất cẩn thận địa chỉ truyệt đối,tương đối trong Refers To. (Mà em thử lại rồi, dùng giữa các sheet cũng không được, ngoại trừ trường hợp công thức đó có tham chiếu là địa chỉ tuyệt đối.) Vậy có cách nào khắc phục không chị? Nếu có, thì xét trong một Workbook việc đặt tên cho công thức cũng gần như là tạo 1 UDF cho workbook đó rồi.
 
Sửa lần cuối:
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#11
Tiếp tục nhé, các bạn của tôi

Hôm nay, chúng ta sẽ tiếp tục với File Chứng Từ Ghi sổ, cũng xem như phần 4 của bài " Cách chuyển dữ liệu vào Chứng Từ Ghi Sổ ". Các bạn hãy mở File Chung_Tu_Ghi_So, chúng ta bắt đầu.
Tại Sheet All Chung Tu, tại I7:I20 có công thức :

=IF(OR(AND(LEN(MTK)>3,TK="NO",TKNO),AND(LEN(MTK)>3,TK="CO",TKCO)),"X",GhiTK)

Nhìn vào công thức trên, bạn sẽ nhận thấy các tên trong công thức đó gồm : MTK, TK, TKNO, TKCO, GhiTK. Bạn vào Insert/Name/Define, và bạn thấy ngay có 2 tên Cell và 3 tên công thức.
2 tên Cell là :

1/ MTK : 'Chung tu ghi so'!$D$6 ( Sheet Chung tu ghi so, Cell D6)
2/ TK : 'Chung tu ghi so'!$C$6 ( Sheet Chung tu ghi so, Cell C6)

3 tên công thức là :

1/TKNO :

=(OR('All chung Tu'!$F7=LEFT(MTK,3),'All chung Tu'!$F7=RIGHT(MTK,3),'All chung Tu'!$F7=MID(MTK,5,3))

(Tại Sheet All Chung Tu, hoặc Cell F7 có giá trị bằng với giá trị lấy từ bên trái 3 chữ số của Cell có tên MTK, hoặc Cell F7 có giá trị bằng với giá trị lấy từ bên phải 3 chữ số của Cell có tên MTK, hoặc Cell F7 có giá trị bằng với giá trị ở giữa vị trí thứ 5 với 3 chữ số của Cell có tên MTK)

2/TKCO :

=(OR('All chung Tu'!$G7=LEFT(MTK,3),'All chung Tu'!$G7=RIGHT(MTK,3),'All chung Tu'!$G7=MID(MTK,5,3))

(Diễn giải tương tự trên)

3/GhiTK :

=IF(OR(AND(TK="NO",LEFT('All chung Tu'!$F7,3)=LEFT(MTK,3)),AND(TK="CO",LEFT('All chung Tu'!$G7,3)=LEFT(MTK,3))),"X","")

( Đánh dấu những hàng nào thoả điều kiện hoặc Nợ (cột F7:F20), hoặc Có (cột G7:G20) giống với giá trị của Cell có tên TK (Chung tu ghi so! D6), thì đánh dấu là "X", nếu không thì để trống). Dùng hàm LEFT để lấy 3 chữ số thoả điều kiện mà thôi, TD : F7= 33311, F8= 33312, ta chỉ cần thoả điều kiện là có giá trị 3 chữ số đầu tiên từ bên trái qua là 333 thì đều đánh dấu bằng chữ "X".

Đến đây, các tên đã được giải thích xong. Công thức trong cột I7:I20, mời các bạn tự diễn giải.

Như bạn WhoamI đã nhận xét, tên của công thức và tên của các Cell các bạn phải cẩn thận với các địa chỉ tương đố và tuyệt đối, chúng ta sẽ đề cập tại phần sau
 
W

WhoamI

Thành viên thân thiết
#12
handung107 nói:
Nhìn vào công thức trên, bạn sẽ nhận thấy các tên trong công thức đó gồm : MTK, TK, TKNO, TKCO, GhiTK. Bạn vào Insert/Name/Define,
Trong Excel Utilities: Download: http://www.appspro.com/Utilities/ExcelUtilities.htm mà chị gởi cho em có 1 cái utility có chức năng là hiển thị tất cả các Refers To của các Define Name được định nghĩa trong Workbook đó, em nghĩ là rất tiện cho việc theo dõi và hiểu bài viết này đó chị!
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#14
Bạn nào cần Add-in về "Tên và nhãn", các bạn vào đây để Download nhé. Add-in là Name Manager.zip, bạn xem trong thư mục Spam đó . Địa chỉ :nghtam72@gmail.com
Password là : handung107
 
W

WhoamI

Thành viên thân thiết
#15
Còn một chức năng khác của menu Insert/Name mà em nghĩ không phải ai cũng để ý là Insert/Name/Apply ( hì, tại em cũng mới biết mà!)
Chức năng này nhằm apply các name ( đối với name của vùng cố định thôi nhé) đã được định nghĩa cho các vùng Refers To tương ứng,
VD:
- Trên Sheet2 bạn đã có một công thức như sau: = Vlookup(sheet1!$B$2:$E$9;$D2;Columns($B$2:$C$9);0)
- Sau đó bạn mới định nghĩa vùng Sheet1!$B$2:$E$9 trong hộp menu Insert/Name/Define là VungChon chẳng hạn.
- Bây giờ bạn muốn apply tên VungChon cho vùng Sheet1!$B$2:$e$9;$B2 trong các công thức bạn đã tạo mà không muốn sửa trực tiếp trên công thức đó. Bạn vào menu Insert/Name/Apply trong danh sách hộp name bạn chọn tên VungChon và ấn Apply.
- Quay lại sheet2 của bạn và kiểm tra công thức của bạn lúc này sẽ là
= Vlookup(VungChon;$D2;Columns($B$2:$C$9);0)
- Ngoài ra trong Insert/Name Còn 1 chức năng nữa là Paste thì đơn giản là paste tên đã được định nghĩa vào công thức thôi.Chúc các bạn đỡ mất công sửa lại name trong công thức như...mình..hì!
 
DucThuan

DucThuan

Tè lè ra rồi !
4/12/04
173
4
18
Đầu đường xó chợ
#16
Rõ ràng là chị Handung là đầu tư khá nhiều thời gian cho bài viết này. Như thế chị phải chính thức phụ trách phụ trách một phần trong EFC.
Rất cảm ơn chị.
Không biết tôi tham gia vào để học hỏi thì có được hay không. Xin các anh chị cho ý kiến.
 
H

handung107

Thành viên thân thiết
28/8/04
576
13
0
VN
www.giaiphapexcel.com
#18
Hoan hô các em, chị đã tính viết tiếp phần "Tên và nhãn", nhung đang đợi các em vì chị không thích độc thoại. WhoamI viết thêm chức năng Insert / Name /Paste / Paste Link nhé, khong khó đâu để hoàn thiện bài này. Còn phần nữa là Tên cục bộ (sử dụng cho riêng Sheet đó) và Tên toàn cục (sử dụng cho các Sheet trong Workbook) nữa, chúng ta sẽ kết luận phần này.
 
T

Tran Chau

Thành viên sơ cấp
23/11/04
149
0
0
59
TP Ho Chi Minh
#19
Attribute VB_Name = "Module1"
Sub mNameLister()
'List all names in a workbook, da thu 11/2004 o Acoun VVN, doc duoc ca Name cua link another workbook
Dim x
For x = 1 To Names.Count
With ActiveCell
.Offset(x, 0) = Names(x).Name 'Name
.Offset(x, 1) = " " & Names(x) 'Address
End With
Next
End Sub
 
L

levanduyet

Welcome
16/10/04
535
11
18
HCM
my.opera.com
#20
Liệt kê và xóa các tên theo yêu cầu

Theo yêu cầu của Maica, tôi đã viết thử một chương trình cho Maica để thao tác trên các tên đã đặt. Hy vọng Maica sẽ hài lòng. Em có thể phát triển thêm.
Chúc em luôn khỏe.
Lê Văn Duyệt
 

Thành viên trực tuyến

  • nguyenkieu
  • homeclassic.vn1
  • Huyền Mia
  • dungkis
  • tannhatban
  • thieunhi2005
  • daongocnam0603
  • DUHOCCHD03136
  • huynhvotrang
  • noithatanhvu
  • xediengiatot
  • Giahanpv

Xem nhiều