Bạn đang xem bài viết Hướng Dẫn Lấy Ra Danh Sách Tên Các Sheet Hiện Có Trong Excel được cập nhật mới nhất tháng 9 năm 2023 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.
Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn lấy ra danh sách tên của các Sheet có trong File Excel một cách nhanh chóng bằng việc sử dụng VBA
Lấy danh sách tên các Sheet trong ExcelĐể làm điều đó, ta mở trình soạn thảo VBA lên và có dòng Code như sau, ở đây trong File mình có 7 Sheet, thực hiện lặp qua các Sheet này và lần lượt lấy tên các Sheet đó vào Excel
Sub DS_Ws() Dim j As Integer
For j = 1 To Sheets.Count Sheet7.Cells(j + 1, 1).Value = Sheets(j).Name Next j End Sub
Biến j được khai báo thuộc kiểu Integer ở trên sẽ chạy từ Sheet đầu tiên tới Sheet cuối cùng trong File Excel của bạn và sau đó thực hiện ghi vào Excel tên của Sheet đó (Sheets(j).Name)
Vừa rồi ta đã thực hiện được 1 Sub để lấy ra danh sách tên của các Sheet hiện có trong Excel một cách khá nhanh chóng, Nếu như bạn muốn mình tạo 1 Function trong VBA để lấy ra tên sheet thì sao.
Tạo hàm lấy tên của Sheet hiện tạiNếu như bạn muốn tạo 1 Function để lấy ra tên Sheet hiện tại, ta có Code khá đơn giản như sau:
Function MySheet() As String MySheet = ActiveSheet.Name End Function
Hàm tự tạo này đơn giản chỉ là lấy ra tên Sheet hiện tại (ActiveSheet.Name) và sẽ không cần truyền đối số cho hàm.
Qua bài viết này, Học Excel Online bạn đã nắm được cách để lấy ra danh sách tên của các Sheet trong File Excel của mình cũng như tạo được 1 Function để lấy ra tên Sheet hiện tại bạn đang làm việc một cách nhanh chóng.
Với VBA bạn còn làm được rất nhiều điều thú vị và hữu ích khác giúp nâng cao hiệu suất trong công việc cũng như đem lại hiệu quả cao hơn khi làm việc với Excel. Tham khảo khoá học VBA101 – Tự động hoá Excel với lập trình VBA cho người mới bắt đầu.
Sử dụng Msgbox trong Excel VBA- hướng dẫn đầy đủ VBA là gì? một số ví dụ ứng dụng VBA trong công việc Một số Code VBA để tìm dòng cuối cùng có dữ liệu trong Excel hướng dẫn cách tạo công cụ tìm kiếm nhanh trong Excel bằng VBA Sử dụng Autofilter hiệu quả hơn với VBA
Lấy Danh Sách Tên Tệp Từ Một Thư Mục Trong Excel (Có Và Không Có Vba)
Tôi đã phải làm theo ba bước sau:
Chọn tập tin và sao chép tên của nó.
Dán tên đó vào một ô trong Excel và nhấn Enter.
Di chuyển đến tập tin tiếp theo và lặp lại bước 1 & 2.
Nghe có vẻ đơn giản phải không? Nó thật sự đơn giản và lãng phí thời gian rất lớn. Thay vì làm tôi mất ba ngày. Thì tôi có thể được thực hiện trong vài phút nếu tôi biết các kỹ thuật phù hợp.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn các cách khác nhau. Để làm cho toàn bộ quá trình này trở nên siêu nhanh và siêu dễ dàng (có và không có VBA). Hướng dẫn này bao gồm:
Dùng chức năng FILES để nhận danh sách tên tệp từ thư mục
Sử dụng VBA Nhận danh sách tất cả tên tệp từ thư mục
Sử dụng VBA Nhận danh sách tất cả các tên tệp với phần mở rộng cụ thể
Hạn chế của các phương thức được hiển thị trong hướng dẫn này:
Với các kỹ thuật được hiển thị sau đây, bạn sẽ chỉ có thể nhận được các tên tệp trong thư mục chính. Bạn sẽ không nhận được tên của các tệp trong các thư mục con trong thư mục chính. Đây là cách để lấy tên tệp từ thư mục và thư mục con bằng Power Query
Dùng chức năng FILES để nhận danh sách tên tệp từ thư mụcMặc dù công thức này không hoạt động trong các ô của bảng tính. Nhưng nó vẫn hoạt động trong các phạm vi được đặt tên. Chúng tôi sẽ sử dụng thực tế này để lấy danh sách tên tệp từ một thư mục được chỉ định.
1. Trong ô A1, nhập địa chỉ đầy đủ của thư mục theo sau là dấu hoa thị (*)Ví dụ: Nếu thư mục của bạn trong ổ C, thì địa chỉ sẽ như sau: C: Users Sumit Desktop Thư mục thử nghiệm *
Nếu bạn không chắc chắn làm thế nào để lấy địa chỉ thư mục, hãy sử dụng phương pháp sau:
Trong thư mục mà bạn muốn lấy tên tệp. Hãy tạo Sổ làm việc Excel mới hoặc mở sổ làm việc hiện có. Trong thư mục và sử dụng công thức:=REPLACE(CELL(“filename”),FIND([“,CELL(“filename”)),LEN(CELL(“filename”)),”*”).
Ttrong bất kỳ ô nào. Công thức này sẽ cung cấp cho bạn địa chỉ thư mục và thêm dấu hoa thị (*) ở cuối. Bây giờ bạn có thể sao chép dán(paste as value). Địa chỉ này vào bất kỳ ô nào (A1 trong ví dụ này) trong sổ làm việc mà bạn muốn tên tệp.
2. Đi tới tab ‘Formulas’ và chọn ‘Define Name’. 3. Trong hộp thoại Tên mới, sử dụng các chi tiết sauTên: FileNameList (thoải mái chọn bất kỳ tên nào bạn thích) Phạm vi: Workbook Tham chiếu đến:=FILES(Sheet1!$A$1)
4. Bây giờ để có được danh sách các tệpchúng tôi sẽ sử dụng phạm vi được đặt tên trong hàm INDEX. Chuyển đến ô A3 (hoặc bất kỳ ô nào bạn muốn danh sách tên bắt đầu) và nhập công thức sau: =IFERROR(INDEX(FileNameList,ROW()-2),””)
5. Kéo xuống và nó sẽ cung cấp cho bạn một danh sách tất cả các tên tệp trong thư mục Bạn muốn giải nén tập tin với một phần mở rộng cụ thể?Nếu bạn muốn nhận tất cả các tệp có phần mở rộng cụ thể. Chỉ cần thay đổi dấu hoa thị với phần mở rộng tệp đó. Ví dụ: nếu bạn chỉ muốn các tệp excel, bạn có thể sử dụng * xls * thay vì *. Khi đó, địa chỉ thư mục bạn cần sử dụng sẽ là: C:UsersSumit DesktopTest Directory*xls*.
Tương tự, đối với các tệp tài liệu từ, sử dụng * doc *
Cách thực hiện hoạt động nàyCông thức FILES lấy tên của tất cả các tệp của tiện ích mở rộng đã chỉ định trong thư mục đã chỉ định.
Trong công thức INDEX, chúng tôi đã đặt tên tệp là mảng và chúng tôi trả về tên tệp thứ 1, 2, 3, v.v. bằng cách sử dụng hàm ROW.
Lưu ý rằng tôi đã sử dụng ROW () – 2, khi chúng tôi bắt đầu từ hàng thứ ba trở đi. Vì vậy, ROW()-2 sẽ là 1 cho trường hợp đầu tiên, 2 cho trường hợp thứ hai khi số hàng là 4, v.v.
Sử dụng VBA nhận danh sách tất cả tên tệp từ thư mụcBây giờ, tôi phải nói rằng phương pháp trên hơi phức tạp (với một số bước). Tuy nhiên, tốt hơn nhiều so với làm điều này bằng tay.
Nhưng nếu bạn có thể sử dụng VBA (hoặc nếu bạn giỏi theo các bước chính xác mà tôi sẽ liệt kê bên dưới). Bạn có thể tạo một chức năng tùy chỉnh (UDF) có thể dễ dàng lấy cho bạn tên của tất cả các tệp.
Lợi ích của việc sử dụng Hàm do người dùng xác định (UDF). Bạn có thể lưu hàm trong sổ làm việc macro cá nhân và sử dụng lại dễ dàng mà không cần lặp đi lặp lại các bước. Bạn cũng có thể tạo một bổ trợ và chia sẻ chức năng này với những người khác.
Bây giờ hãy để tôi cung cấp cho bạn mã VBA. Sẽ tạo một hàm để lấy danh sách tất cả các tên tệp từ một thư mục trong Excel.
Function GetFileNames(ByVal FolderPath As String) As VariantDim Result As VariantDim i As IntegerDim MyFile As ObjectDim MyFSO As ObjectDim MyFolder As ObjectDim MyFiles As ObjectSet MyFSO = CreateObject("Scripting.FileSystemObject")Set MyFolder = MyFSO.GetFolder(FolderPath)Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count)i = 1 For Each MyFile In MyFiles Result(i)= MyFile.Namei = i + 1Next MyFile GetFileNames = Result End FunctionĐoạn mã trên sẽ tạo ra một hàm GetFileNames có thể được sử dụng trong các bảng tính (giống như các hàm thông thường).
Nơi đặt các mã nàyTrong trình chỉnh sửa VB. Bấm chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà bạn đang làm việc. Đi tới Insert và bấm vào Module . Nếu bạn không có thể thấy Project Explorer. Hãy sử dụng phím tắt Control + R (giữ phím điều khiển và nhấn phím ‘R,).
Cách sử dụng chức năng này
Trong bất kỳ ô nào, nhập địa chỉ thư mục của thư mục mà bạn muốn liệt kê tên tệp.
Trong ô nơi bạn muốn có danh sách, hãy nhập công thức sau: =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),””) (Tôi đang nhập nó vào ô A3):
Copy và Paste công thức vào các ô bên dưới để có danh sách tất cả các tệp.
Lưu ý rằng tôi đã nhập vị trí thư mục trong một ô và sau đó sử dụng ô đó trong công thức GetFileNames. Bạn cũng có thể mã cứng địa chỉ thư mục trong công thức như sau: =IFERROR(INDEX(GetFileNames(“C:UsersSumitDesktopTest Folder”),ROW()-2),””)
Trong công thức trên, chúng tôi đã sử dụng ROW()-2 và chúng tôi bắt đầu từ hàng thứ ba trở đi. Điều này đảm bảo rằng khi tôi sao chép công thức trong các ô bên dưới, nó sẽ được tăng thêm 1. Trong trường hợp bạn có thể nhập công thức vào hàng đầu tiên của cột, bạn chỉ cần sử dụng ROW ().
Công thức này sẽ hoạt động như sau:Công thức GetFileNames trả về một mảng chứa tên của tất cả các tệp trong thư mục.
Hàm INDEX được sử dụng để liệt kê một tên tệp cho mỗi ô, bắt đầu từ tên đầu tiên.
Hàm IFERROR được sử dụng để trả về trống thay vì #REF! lỗi được hiển thị khi một công thức được sao chép trong một ô nhưng không có thêm tên tệp để liệt kê.
Sử dụng VBA nhận danh sách tất cả các tên tệp với phần mở rộng cụ thểCông thức trên hoạt động tuyệt vời, khi bạn muốn nhận danh sách tất cả các tên tệp từ một thư mục trong Excel. Nhưng nếu bạn chỉ muốn lấy tên của các tệp video hoặc chỉ các tệp Excel hoặc chỉ các tên tệp có chứa một từ khóa cụ thể.
Đoạn mã trên sẽ tạo ra một hàm ‘GetFileNamesbyExt có thể được sử dụng trong các bảng tính (giống như các hàm thông thường).
Hàm này có hai đối số – vị trí thư mục và từ khóa mở rộng. Nó trả về một mảng các tên tệp khớp với phần mở rộng đã cho. Nếu không có phần mở rộng hoặc từ khóa được chỉ định, nó sẽ trả về tất cả các tên tệp trong thư mục được chỉ định.
Công thức: = GetFileNamesbyExt (Vị trí thư mục tại chỗ, phần mở rộng
Nơi đặt mã
Chuyển đến tab Developer
Nhấp vào nút Visual Basic. Điều này sẽ mở VB Editor.
Trong Trình chỉnh sửa VB, bấm chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà bạn đang làm việc, đi tới Chèn và bấm vào Module. Nếu bạn không có thể thấy Project Explorer, hãy sử dụng phím tắt Control + R (giữ phím điều khiển và nhấn phím ‘R,).
Nhấp đúp chuột vào đối tượng Module và sao chép và dán mã ở trên vào cửa sổ mã module.
Các sử dụng chức năng này
Trong bất kỳ ô nào, nhập địa chỉ thư mục của thư mục mà bạn muốn liệt kê tên tệp. Tôi đã nhập cái này vào ô A1.
Trong một ô, nhập phần mở rộng (hoặc từ khóa) mà bạn muốn tất cả các tên tệp. Tôi đã nhập cái này vào ô B1.
Trong ô nơi bạn muốn có danh sách, hãy nhập công thức sau =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),””) (Tôi đang nhập nó vào ô A3):
Copy and paste the formula in the cells below to get a list of all the files
Bất kỳ thủ thuật Excel nào bạn sử dụng cũng đều giúp cho công việc trong cuộc sống dễ dàng.
Trích nguồn: Trumpexcel.
Hướng Dẫn Cách Đổi Tên Các Sheets Trong Excel
Trong hướng dẫn này, Gitiho không chỉ hướng dẫn các bạn các cách đổi tên các Sheets, từ đơn giản tới tự động (sử dụng mã VBA) trong Excel mà còn chỉ ra các giới hạn cần biết khi đặt hoặc đổi tên các Sheets trong Excel.
Đổi tên trang tính bằng một cú nhấp đúp đơn giảnBước 1: Đặt con trỏ lên tab Sheets mà bạn muốn đổi tên
Bước 2: Bấm đúp vào nó. Thao tác này sẽ đặt tên Sheets ở chế độ chỉnh sửa
Bước 3: Nhập tên của Sheets mà bạn muốn
Bước 4: Nhấn phím Enter (hoặc kích vào bất kỳ đâu trong trang tính)
Nếu bạn muốn đổi tên nhiều Sheets, chỉ bạn cần thực hiện các bước trên cho lần lượt từng Sheets cho đến khi xong.
Ngoài ra, nếu bạn muốn thêm tiền tố/hậu tố vào tên Sheets, chỉ cần nhấn các phím mũi tên để di chuyển con trỏ khi bạn đang ở chế độ chỉnh sửa trong tab. Khi có con trỏ ở nơi bạn muốn thêm văn bản, hãy nhập thủ công.
Cách đổi tên Sheets bằng ShortcutNếu bạn là người thường xuyên sử dụng bàn phím và không muốn chuyển sang chuột chỉ để đổi tên Sheets, bạn cũng có thể sử dụng tổ hợp phím tắt sau (dành cho Windows).
ALT + O + H + R
Bạn cần phải bấm lần lượt các phím này để thực hiện. Mặc dù đây không phải là một trong những phím tắt dễ dàng, nhưng khi đã quen, bạn có thể thấy đây là cách tốt hơn để đổi tên các Sheets trong Excel.
Mẹo: Trong trường hợp bạn muốn chuyển sang Sheets tiếp theo để đổi tên, có thể sử dụng phím tắt Control + PageUp/PageDown (giữ phím Control rồi nhấn phím PageUP để chuyển sang trang tính tiếp theo hoặc phím PageDown để chuyển về trang trước đó).
Đổi tên trang tính bằng tùy chọn trên thanh RibbonBạn cũng có thể truy cập tùy chọn để đổi tên Sheets thông qua tùy chọn trên thanh Ribbon của Excel:
Bước 1: Truy cập vào thẻ Home
Bước 2: Trong nhóm Cell, bấm vào tùy chọn Format
Bước 3: Chọn tùy chọn Rename Sheet. Thao tác này sẽ đưa tên Sheets vào chế độ chỉnh sửa
Bước 4: Nhập tên mới cho Sheets rồi nhấn Enter để kết thúc
Phương pháp này sẽ chỉ cho phép bạn thay đổi tên của Sheets hiện tại. Để thay đổi tên của một số Sheets khác, trước tiên bạn cần kích chọn Sheets đó rồi mới thực hiện lại các bước trên.
Đổi tên trang tính bằng VBAĐôi khi, bạn có thể cần thêm tiền tố hoặc hậu tố vào tất cả các Sheets trong file Excel của mình. Mặc dù có thể thực hiện việc này theo cách thủ công (sử dụng các phương pháp ở trên) nếu bạn chỉ có một vài file với ít các Sheets. Nhưng trong trường hợp bạn có rất nhiều file Excel sẽ rất mất thời gian.
Trong trường hợp này, bạn có thể sử dụng mã VBA đơn giản trong file Excel ngay lập tức để thực hiện việc này trong tích tắc.
For Each sh In Worksheets: chúng tôi = “2023 – ” & sh.Name: Next sh
Bước 1: Kích chuột phải vào bất kỳ tab nào trong Sheets
Bước 2: Chọn tùy chọn View Code. Thao tác này sẽ mở chương trình phụ trợ Visual Basic Editor
Bước 3: Trong cửa sổ VB Editor, chọn tùy chọn ‘View’ trong menu và chọn Immediate Window trong menu xổ xuống (hoặc bấm tổ hợp phím Ctrl+G).
Bước 4: Sao chép mã VBA ở trên và dán dòng mã trên vào cửa sổ này ngay lập tức
Bước 5: Đặt con trỏ ở cuối dòng
Bước 6: Nhấn phím Enter. Ngay lập tức mã được thực thi và nó sẽ thêm tiền tố được chỉ định trong tất cả các tên trang tính.
Lưu ý: Cách này sẽ đổi tên tất cả các Sheets, bao gồm cả các Sheets đang hiển thị cũng như các Sheets ẩn.
For Each sh In Worksheets: chúng tôi = chúng tôi & ” – 2023″: Next sh
Bạn thậm chí có thể tiến thêm một bước nữa và cũng có thể thay đổi tên của một số Sheets cụ thể (giả sử chỉ những trang có văn bản 2023 xuất hiện). Điều này có thể dễ dàng được thực hiện bằng cách chỉnh sửa mã.
Tìm hiểu thêm: Tìm hiểu về cách tự tạo hàm Excel trong VBA
Quy tắc đặt tên Sheets trong ExcelKhi đổi tên các Sheets trong Excel, bạn cần tuân theo một số quy tắc đặt tên:
– Tên Sheets có thể dài tối đa 31 ký tự.
– Không thể để trống tên Sheets.
– Không thể có hai Sheets cùng tên.
– Không thể đặt tên cho Sheets là History (vì nó được Excel sử dụng nội bộ)
– Không thể bắt đầu hoặc kết thúc tên Sheets bằng dấu nháy đơn (‘). Tuy nhiên có thể sử dụng ở giữa hoặc ở đâu đó trong tên Sheets.
– Không thể sử dụng các ký tự sau khi đổi tên Sheets trong Excel: [] /? : *
Điều này có nghĩa là bạn không thể có tên Sheets với ngày sử dụng dấu gạch chéo. Ví dụ: 01/01/2023 không phải là tên Sheets hợp lệ nhưng 01-01-2023 là hợp lệ.
CÓ THỂ BẠN CŨNG QUAN TÂM
Hướng dẫn cách sử dụng Hàm IF trong Excel đơn giản dễ hiểu Hướng dẫn cách thao tác trình bày định dạng bảng dữ liệu trong Excel chuẩn nhất Hướng dẫn cách tạo bảng dữ liệu tiêu chuẩn trên Excel
Hướng Dẫn Cách Lấy Danh Sách Ngẫu Nhiên Trong Excel Đơn Giản Nhất
Trong bài viết này, chúng tôi sẽ hướng dẫn các bạn cách chọn ngẫu nhiên nhiều ô từ một cột có sẵn với sự hỗ trợ của hai hàm cơ bản.
Chọn ngẫu nhiên nhiều ô từ một cột có sẵn bằng cách sử dụng các hàmTrong ví dụ minh họa cho bài viết này, chúng tôi sẽ đưa ra một bảng gồm 10 ô chứa dữ liệu. Việc ta cần làm đó là lấy ngẫu nhiên 5 trong 10 dữ liệu đó ra và điền vào bảng bên cạnh.
Để làm điều này, ta có thể tư duy như sau:
Gắn mỗi tên với 1 con số(ngẫu nhiên hoặc sắp đặt)
Sử dụng hàm lấy giá trị tên theo số (ngẫu nhiên hoặc sắp đặt)
Sử dụng Fill cho các ô còn lại
Để lấy giá trị ngẫu nhiên, một trong hai biến hoặc cả hai đều phải ngẫu nhiên. Từ đó, ta có những cách sau.
Cách 1: Sử dụng VLOOKUP kết hợp RANDBETWEEN cho số thứ tựĐầu tiên, ta sẽ đánh số thứ tự bằng cách tạo thêm cột STT bên trái cột Họ tên:
Sau đó, tại ô C2 ta nhập công thức:
=VLOOKUP(RANDBETWEEN(1,10),$A$2:$B$11,2,0)
Trong đó: Hàm RANDBETWEEN(1,10) sẽ tạo ngẫu nhiên giá trị là số tự nhiên trong khoảng từ 1 đến 10.
Hàm VLOOKUP tìm kiếm giá trị của RANDBETWEEN(1,10) trong bảng A2:B11, đối chiếu sang cột thứ 2 và trả kết quả.
Sử dụng fill cho toàn bộ 4 ô còn lại.
Cách 2: Sử dụng RAND, INDEX và RANKTrong trường hợp này, thay vì cố định số thứ tự, ta sẽ gắn các giá trị họ tên với một con số ngẫu nhiên từ 0 tới 1 bằng hàm RAND(). Nhập =RAND() tại cột số thứ tự và sử dụng fill.
Tiếp theo, tại ô C1, ta sẽ sử dụng công thức:
=INDEX($B$2:$B$11,RANK(A2,$A$2:$A$11))
Hàm RANK sẽ đưa ra xếp hạng của ô A2 trong vùng A2:A11.
Hàm INDEX sẽ trả ra kết quả theo dòng và cột của mảng được nhập vào. VD ô A2 xếp hạng 3, kết quả INDEX trả về sẽ là dòng thứ 3 của cột Họ tên.
Sử dụng Fill và ta có được kết quả.
Hướng Dẫn Các Hàm Lấy Dữ Liệu Có Điều Kiện Trong Excel
Các hàm cơ bản trong excel
=INDEX(A1:D10,6,3)
Hàm INDEX tìm dữ liệu trong vùng A1:D10 và trả lại dữ liệu trong loại thứ 6, cột thứ 3, tức là ô C6. Kết quả của câu lệnh INDEX trên sẽ là Seoul tương tự như trong ô B14
Hàm MATCH kiếm tìm 1 thành quả trong 1 vùng của bảng tính và đưa lại vị trí tương đối của ô chứa thành quả đấy trong vùng tìm kiếm. Cú pháp của hàm MATCH như sau:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])
Khi đọc đến đây, chắc các bạn sẽ tự hỏi là Cái hàm MATCH này hình như k công dụng lắm thì phải. Đúng thế, nếu như chỉ tương tự như vậy thì hàm MATCH không có nhiều tính năng lắm. nhưng ở phần kế tiếp, khi hòa hợp cùng hàm INDEX thì con người hãy xem xem cặp đôi này dùng gì được cho các
Hàm Vlookup với 2 điều kiệnCú Pháp Hàm Vlookup
Lưu ý cú pháp hàm:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) Trong Đó:
lookup_value: giá trị sử dụng để dò tìm.
table_array: Bảng thành quả dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng việc nhấn F4).
col_index_num: Thứ tự của cột cần thu thập dữ liệu trên bảng giá trị dò.
range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
Cách sử dụng hàm INDEX và MATCHHàm INDEX sẽ trả về dữ liệu của một ô trong 1 vùng dựa trên thông số hàng và chỉ số cột của vùng đó. Về căn bản, hàm INDEX có cú pháp như sau:
=INDEX(vùng_dữ_liệu, hàng_thứ_mấy, [cột_thứ_mấy])
Để thử dùng hàm INDEX con người có thể thử trực tiếp câu lệnh sau trên bảng tính phía dưới
=INDEX(A1:D10,6,3)
Hàm INDEX tìm dữ liệu trong vùng A1:D10 và mang lại dữ liệu trong dòng thứ 6, cột thứ 3, tức là ô C6. Kết quả của câu lệnh INDEX trên sẽ là Seoul như trong ô B14
Hàm MATCH tìm kiếm 1 thành quả trong 1 vùng của bảng tính và đưa lại vị trí tương đối của ô chứa giá trị đấy trong vùng tìm kiếm. Cú pháp của hàm MATCH như sau:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])
giá_trị_cần_tìm – thành quả cần được tìm kiếm vị trí trong mảng
mảng_tìm_kiếm – mảng chứa giá trị cần tìm kiếm
kiểu_tìm_kiếm – tìm kiếm thành quả chính xác hay tìm kiếm thành quả gần nhất.
1 – hoặc bỏ qua: tìm thành quả lớn nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. thành quả trong mảng_tìm_kiếm cần được sắp xếp tăng dần
0 – tìm giá trị trong mảng bằng với giá_trị_cần_tìm. đây chính là thành quả mà con người sẽ rất hay dùng khi dùng kết hợpINDEX/MATCH
-1 – tìm thành quả nhỏ nhất mà giá trị đấy lơn hơn hoặc bằng giá_trị_cần_tìm
Khi đọc đến đây, chắc các bạn sẽ tự hỏi là Cái hàm MATCH này hình như không công dụng lắm thì phải. Đúng thế, nếu như chỉ như vậy thì hàm MATCH không có nhiều tác dụng lắm. nhưng ở phần kế tiếp, khi kết hợp cùng hàm INDEX thì con người hãy xem xem cặp đôi này làm gì được cho mọi người.
Sử dụng kết hợp INDEX và MATCH trong excelMình sẽ đi làm ngay một ví dụ trước để các nàng có thể hiểu được sự hòa quyện của hàm INDEX và MATCH trong Excel ra sao. chúng ta có bảng Excel sau đây:
chúng ta muốn đi tìm kiếm coi nước nào có thủ đô là Seoul trong bảng này. công thức để tìm ra điều đấy như sau:
=INDEX(B1:B10,MATCH("Seoul",C1:C10,0)
Trong phương pháp này:
B1:B10 là cột chứa dữ liệu mà con người mong muốn tra cứu, tìm kiếm hoặc trích lọc
MATCH("Seoul",C1:C10,0) sẽ cho chúng ta biết Seoul ở vị trí hàng thứ mấy ở trong bảng tính trên. Kết quả Seoul ở vị trí hàng thứ 6 trong bảng tính. Phương pháp với INDEX trở thành: INDEX(B1:B10,6)
Khi kết hợp 2 công thức này lại và do tính chất của bảng tính, Seoul sẽ phải nằm cùng dòng với nước có thủ đô là Seoul nên ta có khả năng dùng cách này để tìm ra được kết quả là Hàn Quốc
Tổng quát lại thì con người có công thức sau:
=INDEX( cột cần tra cứu giá trị, (MATCH ( giá trị sử dụng để tra cứu, cột chứa thành quả này, 0 ))
Cách dùng và ví dụ hàm VLOOKUP nhiều điều kiệnGiả sử các bạn có bảng dữ liệu sau, các bạn muốn tra cứu thành quả ở cột Điểm tổng kết , dựa vào điều kiện là cột Họ và cột Tên.
Cụ thể các bạn mong muốn tra cứu Điểm tổng kết của người tên Trần Tuấn Anh.
Các nàng có khả năng dùng hàm Vlookup để tra cứu với điều kiện là hai cột bằng cách các bạn sử dụng thêm cột phụ. Cột phụ sẽ chứa nội dung ghép cột Họ và cột Tên. Như vậy lúc này hàm Vlookup 2 điều kiện sẽ trở thành hàm Vlookup 1 điều kiện. các bạn có thể làm theo quá trình cụ thể như sau:
Bước 1: Tạo cột phụ, sau đấy các bạn điền công thức sử dụng toán tử &:
=C6&” “&D6
hoặc dùng hàm CONCATENATE để nối chuỗi trong cột Họ và cột Tên:
=CONCATENATE(C6;” “;D6) Lưu ý: Cột phụ cần phải được tạo phía bên trái cột Điểm tổng kết.
Bước 2: Sao chép phương pháp xuống các dòng phía dưới để hoàn thành thêm cột phụ.
Bước 3: Tiếp tục điền công thức hàm Vlookup =VLOOKUP(C15&” “&D15;E6:F12;2;0) với C15&” “&D15 là điều kiện dò tìm ghép 2 ô Trần Tuấn Anh, E6:F12 là bảng dò tìm, 2 là giá trị trả về trong cột 2, 0 là kiểu dò tìm tương đối.
Lộc Đạt-tổng hợp
Tham khảo( phanmemmienphi, connect, … )
Danh Sách Các Hàm Cơ Bản Trong Excel
Hàm đếm COUNT
Chức năng: Đếm xem có bao nhiêu dữ liệu kiểu số trong phạm vi.
Cú pháp: = COUNT ( Value1, [Value2], [Value3],…..)
Các tham số: Value1, [Value2], [Value3]… là mảng hay dãy dữ liệu, trong đó Value1 là tham số bắt buộc có.
Chức năng: Đếm trong phạm vi có bao nhiêu ô chứa dữ liệu (khác rỗng)
Cú pháp: =COUNTA (Value1, [Value2], [Value3],…)
Các tham số: Value1, [Value2], [Value3] là vùng dữ liệu cần đếm, trong đó Value1 là đối số bắt buộc.
Chức năng: Đếm trong phạm vi có bao nhiêu ô không chứa dữ liệu (rỗng)
Cú pháp: =COUNTBLANK (range)
Các tham số: Range là phạm vi mà từ đó bạn muốn đếm các ô trống.
Chức năng: Đếm phạm trong phạm vi có bao nhiêu ô thỏa mãn điều kiện cần đếm.
Cú pháp: =COUNTIF(range, criteria)
Các tham số:
Chức năng: Đếm trong các phạm vi có bao nhiêu ô thảo mãn các điều kiện cần đếm
Cú pháp: =COUNTIFS (criteria_range1,criteria1,[criteria_range2,criteria2],…)
Các tham số:
Criteria_range1: Bắt buộc. Phạm vi thứ nhất trong đó cần đánh giá điều kiện.
Criteria1: Bắt buộc. Điều kiện dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản để xác định những ô cần đếm.
Criteria_range2, criteria2, …: Tùy chọn. Những phạm vi bổ sung và điều kiện của chúng. Cho phép tối đa 127 cặp criteria_range, criteria.
Chức năng: Tính tổng các số trong một phạm vi ( một nhóm các ô).
Cú pháp: = SUM (number1, [number2],…)
Các tham số:
Chức năng: Tính tổng các giá trị trong một phạm vi đáp ứng điều kiện mà bạn xác định.
Cú pháp: = SUMIF (range, criteria, [sum_range])
Các tham số:
Chức năng: Cộng tất cả các đối số của nó đáp ứng nhiều điều kiện.
Cú pháp: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Các tham số:
Sum_range: Phạm vi ô cần tính tổng
Criteria_range1: Phạm vi được kiểm tra
Criteria1: Điều kiện xác định ô nào trong phạm vi được kiểm tra sẽ được cộng vào.
Criteria_range2, criteria2: Các phạm vi bổ sung và các điều kiện. Bạn có thể nhập tối đa 127 cặp criteria_range, criteria.
Chức năng: Tính tổng sản phẩm của các số tương ứng trong một hoặc nhiều dãy ( nguyên tắc là nhân các con số trong mảng xác định, sau đó trả về tổng của các tích số đó.
Cú pháp: = SUMPRODUCT(array1, [array2], [array3], …)
Các tham số:
Chức năng: Tính trung bình cộng của các đối số.
Cú pháp: =AVERAGE(number1, [number2], …)
Các tham số:
Number1: Số thứ nhất mà bạn muốn tính trung bình (đối số Number1 bắt buộc phải có).
Number2: Các đối số tùy chọn không bắt buộc nằm trong phạm vi từ 2 đến 255 mà bạn muốn tính trung bình.
Hàm IFChức năng: So sánh điều kiện mà bạn muốn có đáp ứng đúng yêu cầu không, nếu đúng thì trả về giá trị đúng TRUE, nếu sai thì sẽ trả về giá trị sai FALSE.
Cú pháp: =IF(logical_test, [value_if_true], [value_if_false])
Các tham số:
Chức năng: Xác định xem liệu rằng tất cả các điều kiện kiểm tra, so sánh có là đúng hay không (TRUE)
Cú pháp: =AND (logical1,[logical2], …)
Các tham số:
Chức năng: xác định liệu rằng các điều kiện đem ra so sánh, kiểm tra có đúng hay không.
Cú pháp: = OR (logical 1, [logical 2], …)
Các tham số
Hàm DAYChức năng: Tách giá trị ngày trong một ngày, tháng, năm cụ thể
Cú pháp: =DAY(serial_number)
Các tham số: Serial_number là giá trị ngày, tháng, năm mà bạn muốn lấy ra ngày.
Chức năng: Tách giá trị tháng trong một ngày cụ thể. Tháng được trả về dưới dạng số nguyên, có giá trị từ 1 đến 12.
Cú pháp: =MONTH(serial_number)
Các tham số: Serial_number là giá trị ngày, tháng, năm mà bạn muốn lấy ra tháng.
Chức năng: Trả về giá trị năm.
Cú pháp: =YEAR(serial_number)
Các tham số: Serial_number là giá trị ngày, tháng, năm mà bạn muốn lấy ra năm.
Chức năng: Tùy theo mục đích công việc mà đôi khi chúng ta phải kết hợp các giá trị ngày, tháng, năm từ các ô lại với nhau thành một ngày hoàn chỉnh hoặc tách nó theo giá trị ngày tháng năm và lưu trên các ô riêng biệt.
Cú pháp: = DATE(year, month, day)
Các tham số:
Year: Đối số năm có thể từ một tới 4 con số (0~9999).
– Nếu năm ở khoảng từ 0 đến 1899, Excel sẽ thêm vào giá trị 1900 để tính năm.
– Nếu năm ở trong khoảng 1900 đến 9999, Excel sử dụng gúa trị trong khoảng đó như một năm.
– Nếu năm nhỏ hơn 0 hay lớn hơn 10000, Excel trả về giá trị lỗi #NUM.
Month:
– Nếu tháng lớn hơn 12, thì số tháng lớn hơn đó sẽ được chuyển thành các tháng đầu tiên trong năm tiếp theo.
Day:
– Nếu ngày lớn hơn số ngày trong tháng, thì ngày đó sẽ chuyển thành các ngày đầu trong tháng tiếp theo.
Chức năng: Hàm trả về số thứ tự của ngày trong tuần.
Cú pháp: =WEEKDAY(serial _number; return_type)
Các tham số:
– Return_type =1: Chủ nhật có thứ tự là 1 (Thứ Bảy là 7)
– Return_type =2: Thứ hai có thứ tự là 1 (Chủ Nhật là 7)
– Return_type =3: Thứ Hai có thứ tự là 0 (Chủ Nhật là 6)
Hàm VLOOKChức năng: Là hàm tìm kiếm giá trị của đối tượng và trả về kết quả theo hàng dọc.
Cú pháp: = VLOOKUP (lookup_value, table_array, col_index_num, [rang_lookup])
Các tham số:
Lookup_value: Giá trị dùng để dò tìm
Table_array: Bảng giá trị dò, nên để ở dạng địa chỉ tuyệt đối (có dấu $ phía trước bằng cách nhấn F4).
Col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
Range_lookup: Phạm vi tìm kiếm, TRUE (1) là dò tìm gần chính xác, FALSE (0) dò tìm chính xác.
Chức năng: Tìm kiếm giá trị của đối tượng trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này.
Cú pháp: =MATCH(Lookup_value,Lookup_array,[Match_type])
Các tham số:
Chức năng: Là hàm trả về giá trị hoặc tham chiếu của 1 ô trong bảng excel giao nhau giữa dòng và cột.
Cú pháp 1: Hàm INDEX dạng mảng
= INDEX (array, row_num, [column_num])
Các tham số:
=INDEX (Reference,Row_num,[Column_num],[Area_num])
Các tham số:
Reference: Vùng tham chiếu, tham số này bắt buộc phải có.
Row_num: Chỉ số hàng và trả về một tham chiếu, tham số này bắt buộc phải có.
Column_num: Chỉ số cột từ đó trả về một tham chiếu, tham số này có hoặc không.
Area_num: Chọn 1 vùng dữ liệu để tham chiếu từ đó trả về sự giao nhau giữa dòng và cột.
Chức năng: Trả về 1 giá trị trong danh sách các giá trị, giá trị đó được xác định theo đối số thứ tự.
Cú pháp: CHOOSE(index_num, value1, [value2]…)
Các tham số:
Nếu index_num nhỏ hơn 1 hoặc lớn hơn số giá trị trong danh sách thì trả về lỗi #Value.
Nếu index_num là phân số, nó bị cắt cụt đến số nguyên thấp nhất trước khi dùng.
Hàm LEFTChức năng: Lấy những kí tự bên trái của một chuỗi.
Cú pháp: = LEFT (text, [num_chars])
Các tham số:
Chức năng: Lấy những kí tự bên phải của một chuỗi.
Cú pháp: = RIGHT (text, [num_chars])
Các tham số:
Cú pháp: = MID (text, start_num, num_chars)
Các tham số:
Chức năng: Đếm độ dài của chuỗi văn bản.
Cú pháp: =LEN (text)
Tham số: Text là chuỗi văn bản
Chức năng: Thay thế một hoặc nhiều kí tự trong một chuỗi văn bản với một kí tự khác hoặc một chuỗi các kí tự.
Cú pháp: = REPLACE (old_text, start_num, num_chars, new_text)
Các tham số:
Old_text: Văn bản gốc mà bạn muốn thay thế một vài kí tự.
Start_num: Vị trí của kí tự đầu tiên trong văn bản gốc mà bạn muốn thay thế.
Num_chars: Số kí tự mà bạn muốn thay thế.
New_text: Văn bản thay thế
Chức năng: Để xóa khoảng trắng thừa trong chuỗi văn bản.
Cú pháp: = TRIM(text)
Tham số: Text là chuỗi văn bản
Cập nhật thông tin chi tiết về Hướng Dẫn Lấy Ra Danh Sách Tên Các Sheet Hiện Có 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!