Lưu ý: Chúng tôi muốn cung cấp cho mình nội dung trợ giúp tiên tiến nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể có thể. Trang này được dịch tự động nên rất có thể chứa các lỗi về ngữ pháp hoặc câu chữ không bao gồm xác. Mục đích của chúng tôi là khiến cho nội dung này trở bắt buộc hữu ích với bạn. Phấn kích cho chúng tôi biết ở dưới trang này rằng thông tin có có lợi với bạn không? dưới đây là nội dung bài viết bằng giờ Anh để bạn tìm hiểu thêm dễ hơn.
Bạn đang xem: Truy xuất dữ liệu trong excel
Tóm tắt
Từng bước nội dung bài viết này mô tả phương pháp để tìm tài liệu trong bảng (hoặc phạm vi ô) bằng phương pháp dùng hàm dựng sẵn khác biệt trong Microsoft Excel. Chúng ta có thể sử dụng công thức không giống nhau để có công dụng tương tự.
Tạo trang tính mẫu
Bài viết này cần sử dụng một trang tính mẫu để minh họa Excel hàm dựng sẵn. để ý đến ví dụ về tham chiếu tên từ cột A cùng trả về tuổi của fan đó từ bỏ cột C. Để tạo trang tính này, hãy nhập dữ liệu sau đây vào một trang tính Excel trống.Bạn đã nhập quý hiếm mà bạn muốn tìm vào ô E2. Chúng ta có thể nhập phương pháp trong bất kỳ ô trống trong và một trang tính.
| A | B | C | D | E | ||
| 1 | Tên | Phòng | Tuổi thọ | Tìm giá trị | ||
| 2 | Henry | 501 | 28 | Mary | ||
| 3 | Stan | 201 | 19 | |||
| 4 | Mary | 101 | 22 | |||
| 5 | Larry | 301 | 29 |
Thuật ngữ định nghĩa
Bài viết này sử dụng các điều kiện dưới đây để mô tả các hàm dựng sẵn Excel:
| Thuật ngữ | Định nghĩa | Ví dụ |
| Bảng mảng | Tra cứu toàn cục bảng | A2:C5 |
| Lookup_Value | Giá trị được tra cứu thấy trong cột đầu tiên của Table_Array. | E2 |
| Mảng kiếm tìm kiếm- hoặc -Lookup_Vector | Phạm vi ô có chứa quý giá tra cứu có thể xảy ra. | A2: A5 |
| Col_Index_Num | Số cột vào Table_Array quý giá khớp sẽ tiến hành trả về cho. | 3 (thứ bố cột vào Table_Array) |
| Result_Array- hoặc -Result_Vector | Phạm vi chỉ chứa một hàng hay như là một cột. Nó phải gồm cùng kích cỡ như mảng tìm kiếm hoặc Lookup_Vector. | C2:C5 |
| Range_Lookup | Một quý giá lô-gic (TRUE hoặc FALSE). Nếu đối số này là TRUE hoặc được bỏ qua, thì hàm sẽ trả về kết quả khớp tương đối. Giả dụ FALSE, nó vẫn tìm hiệu quả khớp thiết yếu xác. | FALSE |
| Top_cell | Đây là tham chiếu mà bạn muốn dựa vào giá trị bù trừ. Top_Cell phải tham chiếu mang lại một ô hoặc phạm vi ô tức thời kề. Nếu như không, hàm OFFSET trả về #VALUE! . | |
| Offset_Col | Đây là số cột, bên trái hoặc mặt phải, mà bạn có nhu cầu góc trên bên trái của công dụng để tham chiếu ô. Ví dụ, "5" làm cho đối số Offset_Col xác định ô bên trên cùng phía bên trái trong tham chiếu là năm cột ở bên yêu cầu của tham chiếu. Offset_Col có thể là tích cực (có nghĩa là sinh sống bên cần của tham chiếu bắt đầu) hoặc âm (nghĩa là ở phía trái của tham chiếu bắt đầu). |
Hàm
LOOKUP()
Hàm LOOKUP tìm kiếm một cực hiếm trong một hàng tốt cột với khớp với nó với một quý hiếm trong cùng một vị trí vào một cột hoặc hàng khác nhau.Sau đây là một lấy ví dụ về cú pháp bí quyết tra cứu:
=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)
Công thức sau search tuổi thọ của Mary trong trang tính mẫu:
=LOOKUP(E2,A2:A5,C2:C5)
Công thức thực hiện giá trị "Mary" vào ô E2 cùng tìm thấy "Mary" vào véc-tơ tra cứu vãn (cột A). Công thức rồi khớp với mức giá trị trong cùng hàng trong hiệu quả vector (cột C). Bởi "Mary" trong mặt hàng 4, hàm LOOKUP trả về quý hiếm từ sản phẩm 4 trong cột C (22).Ghi chú: Hàm LOOKUP yêu cầu là bảng được sắp tới xếp.
Để biết thêm thông tin về hàm tra cứu , hãy bấm số nội dung bài viết sau phía trên để xem bài viết trong cơ sở kiến thức và kỹ năng của Microsoft:
Làm nắm nào để dùng hàm tra cứu trong Excel
VLOOKUP()
Hàm VLOOKUP hoặc dọc tra cứu vãn được áp dụng khi tài liệu được liệt kê trong cột. Hàm này search kiếm một giá trị trong cột kế bên cùng phía bên trái và khớp cùng với nó với tài liệu trong một cột đã chỉ định và hướng dẫn trong cùng hàng. Bạn có thể sử dụng VLOOKUP để tìm dữ liệu trong một bảng được sắp xếp hoặc ko được sắp đến xếp. Ví dụ sau đây dùng bảng với tài liệu không được sắp xếp.Sau đây là một lấy ví dụ về cú pháp công thức VLOOKUP :
=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)
Công thức sau search tuổi lâu của Mary trong trang tính mẫu:
=VLOOKUP(E2,A2:C5,3,FALSE)
Công thức thực hiện giá trị "Mary" trong ô E2 với tìm thấy "Mary" trong cột ngoài cùng phía trái (cột A). Bí quyết rồi khớp với mức giá trị trong thẳng hàng trong Column_Index. Ví dụ này sử dụng "3" có tác dụng Column_Index (cột C). Vị "Mary" trong hàng 4, VLOOKUP trả về quý giá từ hàng 4 vào cột C (22).
Để biết thêm tin tức về hàm VLOOKUP , bấm số nội dung bài viết sau trên đây để xem bài viết trong cơ sở kỹ năng và kiến thức của Microsoft:
Làm nuốm nào để thực hiện hàm VLOOKUP hoặc HLOOKUP nhằm tìm quý hiếm khớp đúng chuẩn
INDEX() cùng MATCH()
Bạn hoàn toàn có thể dùng những hàm INDEX cùng MATCH với mọi người trong nhà để có kết quả tương từ bỏ như thực hiện tra cứu hoặc VLOOKUP.
Sau đấy là một lấy một ví dụ về cú pháp kết hợp hàm INDEX với MATCH để chế tạo ra ra công dụng tương trường đoản cú như tra cứu và VLOOKUP trong ví dụ như trước đó:
=INDEX(Table_Array,Match(Lookup_Value,Lookup_Array,0),Col_Index_Num)
Công thức sau search tuổi thọ của Mary vào trang tính mẫu:
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)
Công thức sử dụng giá trị "Mary" trong ô E2 với tìm thấy "Mary" vào cột A. Nó rồi khớp với cái giá trị trong đứng thảng hàng trong cột C. Bởi vì "Mary" trong mặt hàng 4, cách làm trả về quý giá từ hàng 4 trong cột C (22).
LƯU Ý: Nếu không có các ô vào mảng tìm kiếm khớp cùng với Lookup_Value ("Mary"), phương pháp này sẽ trả về #N/A.Để biết thêm tin tức về hàm INDEX , bấm số bài viết sau trên đây để xem nội dung bài viết trong cơ sở kỹ năng và kiến thức của Microsoft:
biện pháp dùng hàm INDEX để tìm tài liệu trong bảng
OFFSET() với MATCH()
Bạn rất có thể dùng những hàm OFFSET cùng MATCH với mọi người trong nhà để chế tạo ra công dụng tương trường đoản cú như các hàm trong ví dụ trước.Sau đây là một ví dụ về cú pháp kết hợp hàm OFFSET với MATCH để chế tác ra hiệu quả giống như VLOOKUPvà tra cứu :
=OFFSET(top_cell,Match(Lookup_Value,Lookup_Array,0),Offset_Col)
Công thức này đang tìm tuổi lâu của Mary vào trang tính mẫu:
=OFFSET(A1,MATCH(E2,A2:A5,0),2)
Công thức thực hiện giá trị "Mary" trong ô E2 và tìm thấy "Mary" trong cột A. Cách làm rồi khớp với cái giá trị trong thuộc hàng cơ mà hai cột bên đề xuất (cột C). Vị "Mary" trong cột A, phương pháp trả về cực hiếm trong sản phẩm 4 trong cột C (22).
Để biết thêm tin tức về hàm giá trị bù trừ , bấm số nội dung bài viết sau đây để xem nội dung bài viết trong cơ sở kỹ năng của Microsoft:
Trường hợp lưu trữ dữ liệu thành từng Tab trong một file Excel cực kỳ phổ biến. Đó rất có thể là dữ liệu theo từng tháng trong thời hạn với cấu trúc giống nhau mà bạn cần phải kết hợp dữ liệu từ hầu như Tab này trong cùng một file Excel khi bạn đang áp dụng Power BI hoặc nguồn query/Get và Transform trong Excel. Và trong bài blog này, chúng ta sẽ thuộc bàn về kiểu cách tích hợp tài liệu từ các Tab trong một File Excel và thậm chí là vận dụng cùng một quy trình thay đổi dữ liệu mang đến từng tab vì cấu tạo của chúng giống nhau. Hi vọng đây sẽ là một ứng dụng tuyệt vời nhất của power BI giúp cho bạn xử lý gọn gàng nhẹ câu hỏi liên kết những bảng dữ liệu tách bóc rời này.Trước hết, nhằm thực hành, các bạn hãy sẵn sàng nguồn đơn giản trong 1 file Excel như sau. Trả sử bạn có một bảng tính Excel với tứ Tab: Q1, Q2, quận 3 và Q4. Trong những Tab là một số trong những dữ liệu bán hàng trong cha tháng trong những quý. Ví dụ như bảng tính q1 trông như vậy này:
Bước 1: chọn một bảng mẫu trong những Tab được liệt kê
Trong power BI, hãy liên kết với tệp Excel của chúng ta như bình thường. Sau đó, các bạn không đề nghị chọn bất kể bảng nào gấp mà chỉ việc ấn chuột đề nghị vào thư mục Excel ‘Sample.xlsx’, kế tiếp Transform Data:
Bước 2: chế tạo ra truy vấn chủng loại của bạn
Nhân đôi truy vấn (Duplicate) sinh sống trên và call là mẫu mã truy vấn mới (Sample Query).
Xem thêm:
Sau đó, và vấn đề đó là quan trọng: đào thải tất cả các cột không giống trong bảng ngoại trừ cột Data:
Làm vấn đề đó giúp sử dụng ngôn từ M được tạo nên cho bước tiếp theo bạn sẽ làm. Việc loại trừ tất cả các cột này thay đổi cách nhưng mà M tiến hành và đảm bảo rằng thương hiệu của bảng tính sẽ không còn được mã hóa cứng ở bất cứ đâu.Sau kia nhấp vào links “Table” bên phía trong ô và các bạn sẽ thấy văn bản của bảng tính:Khi làm như vậy, nguồn BI sẽ tự động bổ sung thêm 2 bước đó chính là Promoted Header (chuyển mẫu đầu thanh tên trường) và Change Types (thay thay đổi định dạng). Họ cần chú ý bước Change Type sẽ hotline ra thương hiệu trường và điều này là tránh việc có lúc áp dụng cho các bảng nghỉ ngơi Tab khác vày tên trường đang khác hoàn toàn.Vì vậy, chúng ta cần xóa đi bước “Change
Type”:Bây giờ chúng ta có thể thực hiện bất kỳ biến đổi nào khác mà bạn phải trên truy vấn vấn này, nhưng các bạn sẽ cần đề nghị tránh bất kỳ biến đổi nào tạo ra mã M kể đến ngẫu nhiên tên cột như thế nào trên bảng tính. Vì bọn họ sẽ áp dụng những bước thay đổi này với những bảng tính khác bao hàm tên các trường không giống nhau. Hãy luôn nhớ rằng, những chuyển đổi này sẽ rất cần phải được áp dụng cho những bảng tính khác và bọn chúng sẽ thua trận nếu chúng áp dụng cho những cột chưa hẳn là cột hiện tại tại. Đây là vì sao tại sao các bạn phải xoá bước thay đổi loại trước đó, cũng chính vì nó đặt các loại vào thời điểm tháng một, mon hai với tháng ba, và chúng ta cũng có thể mở trình chỉnh sửa nâng cấp (Advance Editor) và chất vấn mã M cho toàn cục truy vấn chỉ để đảm bảo an toàn không có bất cứ tên trường làm sao được gọi ra.Trong trường hợp này toàn bộ những gì họ cần làm là unpivot những cột tháng bằng cách chọn cột “sản phẩm” và áp dụng “Unpivot Other Column” trên Transform tab, và tiếp nối đổi tên các cột thích hợp hợp:Việc có tên trường sản phẩm ở đây sẽ không phải vấn đề vị ở những Tab đều có tên trường này.
Bước 3: tạo ra một chức năng
Tiếp theo, bạn cần tạo một tham số mới bằng cách nhấp vào nút Manange Parameters/New Parameters, hotline tham số Worksheet, đặt kiểu dữ liệu thành văn phiên bản và trả về thương hiệu của bảng tính bạn đã chọn trong cách trước:Bây giờ, quay lại truy vấn mẫu, tìm công việc được call là lọc sản phẩm về phía đầu mà các bạn đã lọc xuống một bảng tính với nhấp vào hình tượng bánh răng kề bên bước để sửa đổi nó:Sau đó, sửa đổi bước nhằm nó áp dụng giá trị trả lại bởi tham số để lọc theo thay vị giá trị cứng mã hoá mà các bạn đã nhập trước đó. Để thực hiện việc này, hãy nhấp vào nút Hiển thị mặt dưới, chọn tham số và tiếp nối chọn tham số Worksheet trong vỏ hộp thả xuống tiếp theo:Cuối cùng, đi tới ngăn truy vấn ở bên trái screen và bấm vào phải vào truy tìm vấn mẫu và lựa chọn “Create Function”:Bạn sẽ được nhắc để cung cấp cho các tác dụng mới một tên; điện thoại tư vấn nó là Get
Data:Và họ đã tạo ra một công dụng Function là Get Data bao gồm Mẫu Sample Query cùng Parameter
Bước 4: call hàm và phối kết hợp dữ liệu
Cuối cùng, cù lại bạn dạng sao giống nhau của tầm nã vấn thuở đầu được tạo nên ở đầu bước 2. Kế tiếp đi cho “Add column tab” bên trên thanh điều khoản và nhấp vào nút “Invoke Custom Function” và điện thoại tư vấn hàm Get
Data, trải qua trong ngôn từ của cột tên mang đến tham số chỉ của hàm:Cuối cùng, nhấp vào Expand/Aggregate trên cột mới và không ngừng mở rộng các bảng lồng nhau:Sau khi các loại bỏ ngẫu nhiên cột không cần thiết nào, các bạn sẽ thấy dữ liệu từ tất cả các bảng tính kết hợp thành một bảng duy nhất như mong muốn muốn:
Đừng quên đặt các loại dữ liệu trên mỗi cột!Hi vọng nội dung bài viết trên để giúp đỡ các chúng ta cũng có thể giải quyết được trong số những bài toán nổi bật với tài liệu ở Excel được trả ra những Tab khác biệt có thuộc cấu trúc. Với giờ thì happy analyzing!