Thực đơn
trang chủĐiện thoại

Menu thả xuống trong excel với sự thay thế dữ liệu. Cách tạo danh sách thả xuống với dữ liệu được thêm vào. Sử dụng một phạm vi được đặt tên

Nếu bạn đang điền bảng trong Excel và dữ liệu trong cột đôi khi có thể bị lặp lại, chẳng hạn như tên sản phẩm hoặc tên nhân viên, thì đừng nhập tham số bắt buộc mỗi lần, việc tạo danh sách thả xuống một lần và chọn một giá trị từ đó sẽ đơn giản và dễ dàng hơn.

Trong bài viết này chúng ta sẽ xem xét cách tạo danh sách thả xuống nhiều loại khác nhau trong một bảng Excel.

Tạo một danh sách thả xuống đơn giản

Để thực hiện việc này, trong ô A1:A7, chúng ta nhập dữ liệu sẽ được hiển thị trong danh sách. Bây giờ hãy chọn ô mà chúng ta sẽ tạo danh sách thả xuống - B2.

Chuyển đến tab “Dữ liệu” và nhấp vào nút “Kiểm tra dữ liệu”.

Trên tab “Tham số”, trong trường “Loại dữ liệu”, chọn “Danh sách”. Bạn có thể nhập giá trị vào trường Nguồn theo nhiều cách khác nhau:

1 – nhập các giá trị cho danh sách theo cách thủ công, phân tách bằng dấu chấm phẩy;

2 – cho biết phạm vi ô mà dữ liệu cho danh sách thả xuống được nhập vào;

3 – chọn các ô có tên, nhấp chuột phải vào chúng và chọn “Gán tên” từ menu.

Chọn ô B2 và nhập “=” vào trường “Nguồn”, sau đó viết tên đã tạo.

Vì vậy, chúng tôi đã tạo một danh sách thả xuống đơn giản trong Excel.

Nếu bạn có tiêu đề cho một cột và bạn cần điền các giá trị vào mỗi hàng, thì hãy chọn không phải một ô mà là một phạm vi ô - B2:B9. Sau đó, bạn có thể chọn giá trị mong muốn trong mỗi ô từ danh sách thả xuống.

Thêm giá trị vào danh sách thả xuống - danh sách động

Trong trường hợp này, chúng tôi sẽ thêm các giá trị vào phạm vi được yêu cầu và chúng sẽ tự động được thêm vào danh sách thả xuống.

Chọn phạm vi ô – D1:D8, sau đó trên tab “Trang chủ”, nhấp vào “Định dạng dưới dạng bảng” và chọn bất kỳ kiểu nào.

Xác nhận vị trí của dữ liệu và chọn hộp “Bảng có tiêu đề”.

Ở trên cùng, chúng tôi viết tiêu đề của bảng - “Nhân viên” và điền dữ liệu vào đó.

Chọn ô chứa danh sách thả xuống và nhấp vào nút “Kiểm tra dữ liệu”. Trong cửa sổ tiếp theo, trong trường “Nguồn”, hãy viết như sau: =INDIRECT(“Bảng1”). Tôi có một bảng trên một trang tính, vì vậy tôi viết “Bảng1”, nếu có bảng thứ hai – “Bảng2”, v.v.

Bây giờ hãy thêm tên nhân viên mới vào danh sách của chúng ta: Ira. Nó xuất hiện trong danh sách thả xuống. Nếu chúng ta xóa bất kỳ tên nào khỏi bảng thì tên đó cũng sẽ bị xóa khỏi danh sách.

Danh sách thả xuống với các giá trị từ một trang tính khác

Nếu bảng có danh sách thả xuống nằm trên một trang và dữ liệu cho các danh sách này nằm trên một trang khác thì Chức năng này sẽ giúp chúng tôi rất nhiều.

Trên Trang tính 2, chọn một ô hoặc phạm vi ô, sau đó nhấp vào nút “Xác thực dữ liệu”.

Chuyển đến Trang tính 1, đặt con trỏ vào trường “Nguồn” và chọn phạm vi ô mong muốn.

Bây giờ bạn có thể thêm tên trên Sheet 1, chúng sẽ được thêm vào danh sách thả xuống trên Sheet 2.

Tạo danh sách thả xuống phụ thuộc

Giả sử chúng ta có ba phạm vi: tên, họ và tên đệm của nhân viên. Đối với mỗi, bạn cần phải chỉ định một tên. Chúng tôi chọn các ô của phạm vi này, bạn cũng có thể làm trống các ô - theo thời gian, bạn có thể thêm dữ liệu vào chúng, dữ liệu này sẽ xuất hiện trong danh sách thả xuống. Nhấp chuột phải vào chúng và chọn “Gán tên” từ danh sách.

Chúng ta gọi tên đầu tiên, tên thứ hai – “Họ”, tên thứ ba – “Cha”.

Hãy tạo một phạm vi khác trong đó tên được chỉ định sẽ được viết. Hãy gọi nó là "Nhân viên".

Chúng tôi tạo danh sách thả xuống đầu tiên, danh sách này sẽ bao gồm tên của các phạm vi. Chọn ô E1 và trên tab “Dữ liệu”, chọn “Xác thực dữ liệu”.

Trong trường “Loại dữ liệu”, chọn “Danh sách”; trong trường Nguồn, nhập “=Nhân viên” hoặc chọn một phạm vi ô đã được gán tên.

Danh sách thả xuống đầu tiên đã được tạo. Bây giờ trong ô F2, chúng ta sẽ tạo danh sách thứ hai, danh sách này sẽ phụ thuộc vào danh sách đầu tiên. Nếu chọn “Tên” ở ô đầu tiên thì danh sách tên sẽ hiển thị ở ô thứ hai, nếu chọn “Họ” thì danh sách họ sẽ được hiển thị.

Chọn ô và nhấp vào nút “Kiểm tra dữ liệu”. Trong trường “Loại dữ liệu”, chọn “Danh sách”; trong trường nguồn, nhập thông tin sau: =INDIRECT($E$1). Ở đây E1 là ô có danh sách thả xuống đầu tiên.

Sử dụng nguyên tắc này, bạn có thể tạo danh sách thả xuống phụ thuộc.

Nếu trong tương lai, bạn cần nhập các giá trị vào một phạm vi được đặt tên, ví dụ: “Họ”. Chuyển đến tab Công thức và nhấp vào Trình quản lý tên. Bây giờ, hãy chọn “Họ” trong tên dải ô và bên dưới, thay vì ô cuối cùng là C3, hãy viết C10. Bấm vào dấu kiểm. Sau đó, phạm vi sẽ tăng lên và bạn có thể thêm dữ liệu vào đó, dữ liệu này sẽ tự động xuất hiện trong danh sách thả xuống.

Bây giờ bạn đã biết cách tạo danh sách thả xuống trong Excel.

Cách tạo danh sách thả xuống bao gồm nhiều ô cùng một lúc (ví dụ: để tên có giá trị)

Cảm ơn, tất cả đều hoạt động tốt.

Danh sách thả xuống với các giá trị từ một trang tính khác không hoạt động, vì cửa sổ khi xác minh dữ liệu đang mở không cho phép làm việc với các cửa sổ khác, đặc biệt là với một trang tính khác!

Danh sách thả xuống phụ thuộc cho phép bạn thực hiện một thủ thuật thường được người dùng khen ngợi Mẫu Excel. Một thủ thuật giúp công việc trở nên dễ dàng và nhanh chóng hơn. Một thủ thuật sẽ làm cho đường cong của bạn trở nên thoải mái và dễ chịu.

Ví dụ về tạo danh sách thả xuống phụ thuộc trong ô Excel

Một ví dụ về việc sử dụng danh sách thả xuống phụ thuộc để tạo một biểu mẫu thuận tiện cho việc điền vào các tài liệu mà người bán đặt hàng. Từ toàn bộ chủng loại, họ phải chọn những sản phẩm mà họ sẽ bán.

Trước tiên, mỗi người bán xác định một nhóm sản phẩm, sau đó xác định một sản phẩm cụ thể từ nhóm này. Biểu mẫu phải bao gồm Họ và tên nhóm và chỉ số sản phẩm cụ thể. Vì việc gõ cái này bằng tay sẽ quá tốn thời gian (và khó chịu), tôi đã nghĩ ra một giải pháp rất nhanh chóng và đơn giản - 2 menu thả xuống phụ thuộc.

Đầu tiên là danh sách tất cả các danh mục sản phẩm, thứ hai là danh sách tất cả các sản phẩm trong danh mục đã chọn. Vì vậy, tôi đã tạo một danh sách thả xuống phụ thuộc vào lựa chọn được thực hiện trong danh sách trước đó (ở đây bạn sẽ tìm thấy tài liệu về cách tạo hai danh sách thả xuống phụ thuộc).

Người dùng mẫu muốn nhận được kết quả tương tự ngân sách nhà nơi cần có danh mục và danh mục phụ của chi phí. Một ví dụ về dữ liệu là trong hình dưới đây:

Vì vậy, ví dụ: nếu chúng ta chọn danh mục Giải trí thì danh sách các danh mục phụ sẽ bao gồm: Rạp chiếu phim, Nhà hát, Bể bơi. Rất quyết định nhanh chóng, nếu bạn muốn phân tích thông tin chi tiết hơn về ngân sách gia đình của mình.

Danh sách các danh mục và danh mục con trong danh sách thả xuống phụ thuộc của Excel

Tôi thừa nhận rằng trong phiên bản ngân sách gia đình đề xuất của mình, tôi chỉ giới hạn mình trong một danh mục, vì đối với tôi việc phân chia chi phí như vậy là khá đủ (tên chi phí / thu nhập được coi là một danh mục phụ). Tuy nhiên, nếu bạn cần tách chúng thành các danh mục con thì phương pháp tôi mô tả bên dưới là lý tưởng. Hãy sử dụng nó!

MỘT kết quả cuối cùng như sau:

Danh sách thả xuống phụ thuộc của các danh mục phụ

Để đạt được điều này, chúng ta cần tạo một bảng dữ liệu hơi khác so với khi chúng ta tạo một danh sách thả xuống. Bảng sẽ trông như thế này (phạm vi G2:H15):

Bảng tính nguồn Excel đang hoạt động

Trong bảng này, bạn phải nhập một danh mục và các danh mục con bên cạnh nó. Tên danh mục phải được lặp lại nhiều lần nếu có các danh mục con. Điều rất quan trọng là dữ liệu được sắp xếp theo cột Danh mục. Điều này sẽ cực kỳ quan trọng khi chúng ta viết công thức sau này.

Bạn cũng có thể sử dụng các bảng từ hình ảnh đầu tiên. Tất nhiên, các công thức sẽ khác nhau. Có lần tôi thậm chí còn tìm thấy một giải pháp như vậy trên Internet, nhưng tôi không thích nó vì nó có độ dài danh sách cố định: điều đó có nghĩa là đôi khi danh sách chứa trường trống và đôi khi không hiển thị tất cả các phần tử. Tất nhiên, tôi có thể tránh được hạn chế này, nhưng tôi thừa nhận rằng tôi thích giải pháp của mình hơn nên tôi không bao giờ quay lại giải pháp đó.

Được rồi. Bây giờ, tôi sẽ mô tả từng bước tạo danh sách thả xuống phụ thuộc.

1. Tên dãy ô

Đây là một bước tùy chọn, nếu không có nó chúng ta có thể xử lý việc này mà không gặp vấn đề gì. Tuy nhiên, tôi thích sử dụng tên vì chúng làm cho công thức dễ viết và dễ đọc hơn nhiều.

Hãy gán tên cho hai phạm vi. Danh sách tất cả các danh mục và danh sách các danh mục làm việc. Các phạm vi này sẽ là A3:A5 (danh sách các danh mục trong bảng tính màu xanh lá cây ở hình ảnh đầu tiên) và G3:G15 (danh sách các danh mục lặp lại trong bảng tính màu tím).

Để đặt tên cho danh sách các danh mục:

  1. Chọn phạm vi A3:A5.
  2. Trong hộp Tên (hộp ở bên trái thanh công thức), nhập tên "Danh mục".
  3. Xác nhận bằng phím Enter.

Thực hiện hành động tương tự cho phạm vi danh sách công việc G3:G15 mà bạn có thể gọi là “Work_List”. Chúng ta sẽ sử dụng phạm vi này trong công thức.

2. Tạo danh sách thả xuống cho một danh mục

Nó sẽ đơn giản:

  1. Chọn ô nơi bạn muốn đặt danh sách. Trong trường hợp của tôi nó là A12.
  2. Từ menu DATA, chọn công cụ Xác thực dữ liệu. Cửa sổ “Kiểm tra giá trị đầu vào” xuất hiện.
  3. Chọn Danh sách làm kiểu dữ liệu.
  4. Là nguồn, hãy nhập: =Category (hình ảnh bên dưới).
  5. Xác nhận bằng OK.

Kết quả là như sau:

Danh sách thả xuống cho danh mục.

3. Tạo danh sách thả xuống phụ thuộc cho danh mục con

Bây giờ nó sẽ rất vui. Chúng tôi biết cách tạo danh sách - chúng tôi chỉ làm việc đó cho một danh mục. Chỉ một câu hỏi: “Làm cách nào để yêu cầu Excel chỉ chọn những giá trị dành cho một danh mục cụ thể?” Như bạn có thể đoán, ở đây tôi sẽ sử dụng một bảng tính và tất nhiên cả các công thức.

Hãy bắt đầu với những gì chúng ta đã biết, nghĩa là bằng cách tạo danh sách thả xuống trong ô B12. Vì vậy, hãy chọn ô đó và nhấp vào Xác thực dữ liệu/dữ liệu và Kiểu dữ liệu là Danh sách.

Trong nguồn danh sách, hãy nhập công thức sau:

Xem cửa sổ “Kiểm tra giá trị đầu vào”:

Xác thực các giá trị đầu vào cho một danh mục con trong danh sách thả xuống phụ thuộc

Như bạn có thể thấy, toàn bộ thủ thuật đối với danh sách phụ thuộc là sử dụng hàm OFFSET. Được rồi, gần như tất cả. Các hàm MATCH và COUNTIF giúp ích cho cô ấy. Hàm OFFSET cho phép bạn xác định phạm vi một cách linh hoạt. Đầu tiên, chúng tôi xác định ô mà từ đó sự thay đổi phạm vi sẽ bắt đầu và trong các đối số tiếp theo, chúng tôi xác định kích thước của nó.

Trong ví dụ của chúng tôi, phạm vi sẽ di chuyển qua cột Danh mục con trong bảng tính (G2:H15). Chúng ta sẽ bắt đầu di chuyển từ ô H2, đây cũng là đối số đầu tiên của hàm. Trong công thức, ô H2 được viết là liên kết tuyệt đối bởi vì tôi cho rằng chúng ta sẽ sử dụng danh sách thả xuống trong nhiều ô.

Vì bảng tính được sắp xếp theo Danh mục nên phạm vi sẽ là nguồn cho danh sách thả xuống sẽ bắt đầu ở nơi danh mục đã chọn xuất hiện lần đầu tiên. Ví dụ: đối với danh mục Thực phẩm, chúng tôi muốn hiển thị phạm vi H6:H11, đối với Giao thông vận tải - phạm vi H12:H15, v.v. Lưu ý rằng chúng tôi luôn di chuyển dọc theo cột H và điều duy nhất thay đổi là phần đầu của phạm vi và chiều cao của nó (nghĩa là số phần tử trong danh sách).

Phần đầu của phạm vi sẽ được di chuyển so với ô H2 bằng số ô xuống dưới (về số lượng) bằng số vị trí của danh mục xuất hiện đầu tiên trong cột Danh mục. Sẽ dễ hiểu hơn bằng một ví dụ: phạm vi cho danh mục Thực phẩm đã được di chuyển xuống 4 ô so với ô H2 (bắt đầu từ 4 ô tính từ H2). Trong ô thứ 4 của cột Danh mục con (không bao gồm tiêu đề, vì Chúng ta đang nói về về một phạm vi có tên Work_List), có từ Dinh dưỡng (xuất hiện lần đầu). Chúng tôi sử dụng thực tế này để thực sự xác định điểm bắt đầu của phạm vi. Hàm MATCH (được giới thiệu là đối số thứ hai của hàm OFFSET) sẽ phục vụ chúng ta cho mục đích này:

Chiều cao của phạm vi được xác định bởi hàm COUNTIF. Cô ấy đếm tất cả các lần lặp lại trong danh mục, tức là từ Dinh dưỡng. Số lần từ này xuất hiện, số lượng vị trí sẽ có trong phạm vi của chúng tôi. Số lượng vị trí trong một phạm vi là chiều cao của nó. Đây là chức năng:

Tất nhiên, cả hai hàm đều đã có trong hàm OFFSET được mô tả ở trên. Ngoài ra, hãy lưu ý rằng trong cả hai hàm MATCH và COUNTIF, đều có tham chiếu đến một phạm vi có tên là WorkList. Như tôi đã đề cập trước đó, bạn không cần phải sử dụng tên dải ô, bạn chỉ cần nhập $H3:$H15. Tuy nhiên, việc sử dụng tên dải ô trong công thức sẽ giúp công thức đơn giản và dễ đọc hơn.

Đó là tất cả:

Tải xuống ví dụ về danh sách thả xuống phụ thuộc trong Excel

Một công thức, tuy không đơn giản nhưng nó giúp công việc trở nên dễ dàng hơn và bảo vệ khỏi sai sót khi nhập dữ liệu!

Trong bài này chúng ta sẽ xem xét cách tạo danh sách thả xuống trong excel 2007 Lấy một ví dụ khi chúng ta cần chọn trong một ô đặt giá trị từ 1 đến 5 từ danh sách thả xuống. Chúng tôi tự tạo danh sách và chọn nó bằng nút chuột trái. Nhấp chuột phải vào vùng đã chọn và chọn Tên phạm vi.

Trong trường Tên mở ra, nhập tên danh sách của chúng tôi, hãy gọi nó Nghĩa. Trong trường Khu vực, chọn từ danh sách thả xuống Sách(hoặc số Sheet mà bạn muốn áp dụng danh sách). Bấm vào đồng ý.

Ngoài ra, để đặt tên cho danh sách, bạn nên chọn danh sách hiện có và nhập tên ô vào trường tên ô và gán tên danh sách. Nhìn vào hình ảnh dưới đây.

Danh sách đã được tạo. Bây giờ áp dụng danh sách này cho ô.

Chọn ô mà danh sách sẽ được đính kèm. Trong dải băng, đi tới tab Dữ liệu và trong nhóm Làm việc với dữ liệu Bấm vào nút Kiểm tra dữ liệu. Trong cửa sổ tiếp theo, trong tab Tham số, trong trường Kiểu dữ liệu, chọn mục từ danh sách Danh sách.

Trong trường Nguồn, đặt dấu bằng và viết tên mà bạn đã gán cho danh sách. Danh sách này được gọi là "Giá trị". Theo đó, mục nhập phải như trong hình bên dưới.

Bấm OK và bây giờ bạn có một ô có danh sách thả xuống. Hãy xem nó trông như thế nào trong hình dưới đây. Khi bấm vào một ô, chúng ta sẽ thấy một hình vuông có hình tam giác hướng xuống ở bên phải ô đó. Bấm vào hình vuông và từ đó mở danh sách.

Bạn có thể tạo mà không cần chỉ định tên danh sách. Đó là:

  1. tự tạo danh sách;
  2. đi tới tab Dữ liệu trong Dải băng, nhấp vào nút Kiểm tra dữ liệu;
  3. trong cửa sổ mở ra, trong tab Tham số, trong trường Kiểu dữ liệu, chọn Danh sách;
  4. trong trường Nguồn, nhấp chuột trái để kích hoạt của lĩnh vực này. Tiếp theo, chọn các ô tạo thành danh sách;
  5. Bấm vào đồng ý.

Tất cả, danh sách thả xuống trong excel 2007 sẵn sàng.

Phần tử danh sách quen thuộc với chúng ta từ các biểu mẫu trên trang web. Thật thuận tiện để chọn các giá trị làm sẵn. Ví dụ: không ai nhập tháng theo cách thủ công; nó được lấy từ danh sách như vậy. Bạn có thể điền vào danh sách thả xuống trong Excel bằng nhiều công cụ khác nhau. Trong bài viết này chúng ta sẽ xem xét từng cái một.

Cách tạo danh sách thả xuống trong Excel

Làm cách nào để tạo danh sách thả xuống trong Excel 2010 hoặc 2016 bằng một lệnh trên thanh công cụ? Trên tab “Dữ liệu”, trong phần “Làm việc với dữ liệu”, hãy tìm nút “Xác thực dữ liệu”. Bấm vào nó và chọn mục đầu tiên.

Một cửa sổ sẽ mở ra. Trong tab “Tùy chọn”, trong phần thả xuống “Loại dữ liệu”, hãy chọn “Danh sách”.


Một dòng sẽ xuất hiện ở phía dưới để chỉ nguồn.


Bạn có thể cung cấp thông tin theo nhiều cách khác nhau.

Đầu tiên chúng ta hãy đặt một cái tên. Để thực hiện việc này, hãy tạo một bảng như vậy trên bất kỳ trang tính nào.

Chọn nó và nhấp vào nút bên phải chuột. Bấm vào lệnh “Gán tên”.

Nhập tên của bạn vào dòng trên.

Gọi cửa sổ “Kiểm tra dữ liệu” và trong trường “Nguồn”, chỉ định tên bằng cách đặt dấu “=” ở phía trước nó.


Trong bất kỳ trường hợp nào trong ba trường hợp, bạn sẽ thấy phần tử mong muốn. Chọn một giá trị từ danh sách thả xuống danh sách excel xảy ra với con chuột. Nhấp vào nó và một danh sách dữ liệu được chỉ định sẽ xuất hiện.

Bạn đã học cách tạo danh sách thả xuống trong một ô Excel. Nhưng nhiều hơn nữa có thể được thực hiện.

Thay thế dữ liệu Excel động

Nếu bạn thêm một số giá trị vào phạm vi dữ liệu được chèn vào danh sách thì sẽ không có thay đổi nào xảy ra trong đó cho đến khi địa chỉ mới được chỉ định theo cách thủ công. Để liên kết một phạm vi và yếu tố hoạt động, bạn cần định dạng cái đầu tiên dưới dạng bảng. Tạo một mảng như thế này.

Chọn nó và trên tab “Trang chủ”, chọn bất kỳ kiểu bảng nào.


Hãy chắc chắn kiểm tra hộp bên dưới.

Bạn sẽ nhận được thiết kế này.

Tạo một phần tử hoạt động như mô tả ở trên. Đối với nguồn, nhập công thức

=INDIRECT("Bảng1[Thành phố]")

Để tìm ra tên bảng, hãy chuyển đến tab Thiết kế và xem nó. Bạn có thể đổi tên thành bất kỳ tên nào khác.


Hàm INDIRECT tạo tham chiếu đến một ô hoặc dải ô. Bây giờ phần tử của bạn trong ô được liên kết với mảng dữ liệu.

Hãy cố gắng tăng số lượng thành phố.


Quy trình ngược lại là thay thế dữ liệu từ danh sách thả xuống vào bảng tính Excel, hoạt động rất đơn giản. Trong ô nơi bạn muốn chèn giá trị đã chọn từ bảng, hãy nhập công thức:

Địa chỉ ô

Ví dụ: nếu danh sách dữ liệu ở ô D1 thì tại ô hiển thị kết quả đã chọn, hãy nhập công thức

Cách loại bỏ (xóa) danh sách thả xuống trong Excel

Mở cửa sổ cài đặt danh sách thả xuống và chọn "Bất kỳ giá trị nào" trong phần "Loại dữ liệu".



Yếu tố không cần thiết sẽ biến mất.

Mục phụ thuộc

Đôi khi trong Excel cần phải tạo nhiều danh sách khi danh sách này phụ thuộc vào danh sách kia. Ví dụ: mỗi thành phố có một số địa chỉ. Khi chọn cái đầu tiên, chúng ta chỉ lấy địa chỉ của cái đã chọn giải quyết.


Trong trường hợp này, hãy đặt tên cho mỗi cột. Chọn không có ô đầu tiên (tiêu đề) và nhấp chuột phải. Chọn "Tên".

Đây sẽ là tên của thành phố.


Khi đặt tên St. Petersburg và Nizhny Novgorod Bạn sẽ gặp lỗi vì tên không được chứa dấu cách, dấu gạch dưới, ký tự đặc biệt vân vân.


Vì vậy, chúng tôi sẽ đổi tên những thành phố này bằng dấu gạch dưới.


Chúng ta tạo phần tử đầu tiên trong ô A9 theo cách thông thường.


Và trong lần thứ hai chúng ta viết công thức:

GIÁN TIẾP(A9)


Đầu tiên bạn sẽ thấy một thông báo lỗi. Đồng ý.

Vấn đề là không có giá trị được chọn. Ngay sau khi một thành phố được chọn trong danh sách đầu tiên, thành phố thứ hai sẽ hoạt động.

Cách thiết lập danh sách thả xuống phụ thuộc trong Excel bằng tìm kiếm

Bạn có thể sử dụng phạm vi dữ liệu động cho phần tử thứ hai. Điều này thuận tiện hơn nếu số lượng địa chỉ tăng lên.
Hãy tạo một danh sách thả xuống các thành phố. Phạm vi được đặt tên được đánh dấu bằng màu cam.


Đối với danh sách thứ hai bạn cần nhập công thức:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH trả về số ô có thành phố được chọn trong danh sách đầu tiên (E6) trong vùng được chỉ định SA:$A.
COUNTIF đếm số lượng kết quả trùng khớp trong một phạm vi có giá trị trong ô được chỉ định (E6).


Chúng tôi đã nhận được danh sách thả xuống được liên kết trong Excel với điều kiện khớp và phạm vi tìm kiếm cho nó.

Chọn nhiều lần

Thông thường chúng ta cần lấy nhiều giá trị từ một tập dữ liệu. Bạn có thể hiển thị chúng trong các tế bào khác nhau hoặc có thể kết hợp thành một. Trong mọi trường hợp, macro là cần thiết.
Nhấp chuột phải vào nhãn trang tính ở dưới cùng và chọn Xem mã.


Cửa sổ nhà phát triển sẽ mở ra. Bạn cần chèn thuật toán sau vào nó.

Bảng tính phụ riêng tư_Change(ByVal Target As Range) Khi xảy ra lỗi Tiếp tục tiếp theo Nếu không giao nhau(Target, Range("C2:F2")) Không có gì và Target.Cells.Count = 1 Thì Application.EnableEvents = Sai Nếu Len(Target.Offset (1, 0)) = 0 Sau đó Target.Offset(1, 0) = Target Khác Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub


Xin lưu ý rằng trong dòng

Nếu không giao nhau (Target, Range ("E7")) là không có gì và Target.Cells.Count = 1 thì

Bạn nên nhập địa chỉ của ô có danh sách. Đối với chúng tôi nó sẽ là E7.

Trở lại bảng tính Excel và tạo một danh sách trong ô E7.

Khi được chọn, các giá trị sẽ xuất hiện bên dưới nó.

Đoạn mã sau sẽ cho phép bạn tích lũy các giá trị trong một ô.

Bảng tính phụ riêng tư_Change(ByVal Target As Range) Khi xảy ra lỗi Tiếp tục tiếp theo Nếu không giao nhau(Target, Range("E7")) Không có gì và Target.Cells.Count = 1 Sau đó Application.EnableEvents = Sai newVal = Target Application.Undo oldval = Mục tiêu Nếu Len(oldval)<>0 Và oldval<>newVal Sau đó Target = Target & "," & newVal Else Target = newVal End Nếu Nếu Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End Nếu End Sub

Ngay khi bạn di chuyển con trỏ đến một ô khác, bạn sẽ thấy danh sách các thành phố đã chọn. Để đọc bài viết này.


Chúng tôi đã hướng dẫn bạn cách thêm và thay đổi danh sách thả xuống trong ô Excel. Chúng tôi hy vọng thông tin này sẽ giúp bạn.

Có một ngày tuyệt vời!

Trình chỉnh sửa bảng từ Microsoftđược hưởng sự phổ biến rất lớn. Thành công này đạt được nhờ chức năng mở rộng nhất trong số tất cả các trình soạn thảo trên thị trường phần mềm. Microsoft Excel là chức năng nhất trong số chúng, nhưng đồng thời nó cũng khá dễ sử dụng.

Trong mọi trường hợp, tuy nhiên, hầu hết người dùng thậm chí không sử dụng một phần mười toàn bộ chức năng. Hôm nay chúng tôi muốn kể cho bạn nghe về một cơ hội thú vị– về việc tạo danh sách thả xuống trong Excel. Thông thường, chúng được yêu cầu để duy trì cơ sở dữ liệu của các doanh nghiệp nhỏ, vì vậy nếu bạn muốn tiết kiệm dịch vụ của một thư ký am hiểu chương trình văn phòng- nhớ!

Tạo danh sách thả xuống trong Excel: phương pháp một

TRONG trong trường hợp này chúng tôi sẽ sử dụng công cụ Xác thực dữ liệu để thực hiện một số công việc tạo danh sách cho chúng tôi. Vậy hãy bắt đầu:

Chú ý cách sử dụng dễ dàng chương trình Excel– mọi vấn đề thực sự có thể được giải quyết chỉ trong vài cú nhấp chuột; chỉ cần dành khoảng vài phút để nghiên cứu chức năng là đủ. Chính vì lý do này mà MS Office, với tư cách là một gói phần mềm văn phòng, là gói phần mềm văn phòng tiên tiến và phổ biến nhất trên thị trường.

Có nhiều đường tắt Tuy nhiên, việc tạo danh sách thả xuống có một nhược điểm nhỏ - danh sách như vậy có thể chỉ nằm bên dưới các thành phần sẽ được hiển thị trong đó.

Tạo danh sách thả xuống trong Excel: phương pháp hai

Nếu bạn không bối rối với những điều trên, đặc biệt là vị trí của danh sách bên dưới các thành phần, thì bạn có thể tiết kiệm một vài phút khi tạo nó. Cần hoàn thành những hành động sau:

Như bạn có thể thấy, mọi thứ ở đây đơn giản hơn nhiều, nhưng có một số hạn chế và bạn sẽ phải giải quyết chúng, vì vậy nếu danh sách chức năng quan trọng đối với bạn, thì tốt nhất bạn nên sử dụng tùy chọn đầu tiên - đó là tiến bộ hơn nhiều.

Xin chúc mừng, hôm nay bạn đã học cách tạo danh sách thả xuống trong Excel bằng hai phương pháp tiêu chuẩn, mà không cần kết nối các mô-đun bổ sung.

Danh sách thả xuống đề cập đến nội dung của một số giá trị trong một ô. Khi người dùng nhấn vào mũi tên bên phải sẽ xuất hiện một danh sách cụ thể. Bạn có thể chọn một cái cụ thể.

Rất công cụ tiện dụng Excel để kiểm tra dữ liệu đã nhập. Khả năng của danh sách thả xuống cho phép bạn tăng sự thoải mái khi làm việc với dữ liệu: thay thế dữ liệu, hiển thị dữ liệu từ trang tính hoặc tệp khác, sự hiện diện của chức năng tìm kiếm và các phụ thuộc.

Tạo danh sách thả xuống

Đường dẫn: Menu dữ liệu - Công cụ xác thực dữ liệu - Tab Tùy chọn. Kiểu dữ liệu – “Danh sách”.

Bạn có thể nhập các giá trị mà danh sách thả xuống sẽ được tạo theo nhiều cách khác nhau:

Bất kỳ tùy chọn nào cũng sẽ cho kết quả tương tự.



Danh sách thả xuống trong Excel có thay thế dữ liệu

Cần tạo danh sách thả xuống với các giá trị từ phạm vi năng động. Nếu thay đổi được thực hiện đối với phạm vi hiện có (dữ liệu được thêm hoặc xóa), chúng sẽ tự động được phản ánh trong danh sách thả xuống.


Hãy kiểm tra nó. Đây là bảng của chúng tôi với danh sách trên một tờ:

Hãy thêm một giá trị mới “Cây Giáng sinh” vào bảng.

Bây giờ hãy loại bỏ giá trị “bạch dương”.

Chiếc “bàn thông minh” dễ dàng “mở rộng” và thay đổi đã giúp chúng tôi hiện thực hóa kế hoạch của mình.

Bây giờ, hãy tạo khả năng nhập trực tiếp các giá trị mới vào ô có danh sách này. Và dữ liệu đã được tự động thêm vào phạm vi.


Khi chúng ta nhập tên mới vào ô trống của danh sách thả xuống, một thông báo sẽ xuất hiện: “Thêm tên baobab đã nhập vào danh sách thả xuống?”

Nhấp vào “Có” và thêm một dòng khác có giá trị “baobab”.

Danh sách thả xuống trong Excel với dữ liệu từ trang tính/tệp khác

Khi các giá trị cho danh sách thả xuống nằm trên một trang tính khác hoặc trong một sổ làm việc khác, cách tiêu chuẩn không hoạt động. Bạn có thể giải quyết vấn đề bằng hàm INDIRECT: nó sẽ tạo ra liên kết chính xác TRÊN nguồn bên ngoài thông tin.

  1. Chúng tôi kích hoạt ô nơi chúng tôi muốn đặt danh sách thả xuống.
  2. Mở tùy chọn xác minh dữ liệu. Trong trường “Nguồn”, nhập công thức: =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”).

Tên của tệp chứa thông tin về danh sách được đặt trong dấu ngoặc vuông. Tập tin này phải được mở. Nếu cuốn sách có các giá trị được yêu cầu nằm trong một thư mục khác, bạn cần chỉ định đường dẫn đầy đủ.

Cách tạo danh sách thả xuống phụ thuộc

Hãy lấy ba phạm vi được đặt tên:

Cái này điều kiện bắt buộc. Ở trên mô tả cách làm danh sách thường xuyên phạm vi được đặt tên (sử dụng Trình quản lý tên). Hãy nhớ rằng tên không được chứa dấu cách hoặc dấu chấm câu.

  1. Hãy tạo danh sách thả xuống đầu tiên, danh sách này sẽ bao gồm tên của các phạm vi.
  2. Khi bạn đã đặt con trỏ vào trường “Nguồn”, hãy đi tới trang tính và chọn từng ô được yêu cầu.

  3. Bây giờ hãy tạo danh sách thả xuống thứ hai. Nó sẽ phản ánh những từ tương ứng với tên được chọn trong danh sách đầu tiên. Nếu là “Cây”, thì “sừng”, “sồi”, v.v. Nhập vào trường “Nguồn” một hàm có dạng =INDIRECT(E3). E3 – ô có tên của phạm vi đầu tiên.

    4. Chọn nhiều giá trị từ danh sách thả xuống Excel

    Nó xảy ra khi bạn cần chọn nhiều mục từ danh sách thả xuống cùng một lúc. Hãy xem xét các cách để thực hiện nhiệm vụ.

    1. Chúng tôi tạo ra danh sách tiêu chuẩn bằng cách sử dụng công cụ Xác thực dữ liệu. Thêm vào nguồn tờ giấy macro sẵn sàng. Làm thế nào để làm điều này được mô tả ở trên. Với sự trợ giúp của nó, các giá trị đã chọn sẽ được thêm vào bên phải danh sách thả xuống.
      2. Bảng tính phụ riêng tư_Change(ByVal Target As Range) Khi xảy ra lỗi Tiếp tục tiếp theo Nếu không giao nhau(Target, Range("E2:E9")) Không có gì và Target.Cells.Count = 1 Thì Application.EnableEvents = Sai Nếu Len(Target.Offset (0, 1)) = 0 Sau đó Target.Offset(0, 1) = Target Khác Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
    2. Để làm cho các giá trị đã chọn xuất hiện bên dưới, chúng ta chèn một mã xử lý khác.
      3. Bảng tính phụ riêng tư_Change(ByVal Target As Range) Khi xảy ra lỗi Tiếp tục tiếp theo Nếu không giao nhau(Target, Range("H2:K2")) Không có gì và Target.Cells.Count = 1 Thì Application.EnableEvents = Sai Nếu Len(Target.Offset (1, 0)) = 0 Sau đó Target.Offset(1, 0) = Target Khác Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
    3. Để hiển thị các giá trị đã chọn trong một ô, được phân tách bằng bất kỳ dấu chấm câu nào, hãy sử dụng mô-đun sau.

      4. Bảng tính phụ riêng tư_Change (Mục tiêu ByVal dưới dạng phạm vi)
      Khi xảy ra lỗi Tiếp tục tiếp theo
      Nếu không giao nhau(Target, Range("C2:C5")) là không có gì và Target.Cells.Count = 1 thì
      Application.EnableEvents = Sai
      newVal = Mục tiêu
      Ứng dụng.Hoàn tác
      oldval = Mục tiêu
      Nếu Len(oldval)<>0 Và oldval<>mớiValSau đó
      Mục tiêu = Mục tiêu & "," & newVal
      Khác
      Mục tiêu = newVal
      Kết thúc nếu
      Nếu Len(newVal) = 0 thì Target.ClearContents
      Application.EnableEvents = Đúng
      Kết thúc nếu
      Kết thúc phụ

    Đừng quên thay đổi phạm vi thành “của riêng bạn”. Chúng tôi tạo danh sách theo cách cổ điển. Và macro sẽ thực hiện phần còn lại của công việc.

    Danh sách thả xuống với tìm kiếm

    Khi bạn nhập các chữ cái đầu tiên trên bàn phím, các phần tử phù hợp sẽ được tô sáng. Và đây không phải là tất cả những khoảnh khắc dễ chịu của nhạc cụ này. Tại đây bạn có thể cấu hình đại diện trực quan thông tin, chỉ ra hai cột cùng một lúc làm nguồn.