Hỏi về công thức index dữ liệu bảng (1 người xem)

[thanhvnu]

Chào các anh chị,

Em có bảng dữ liệu như đính kèm. Không biết dùng công thức gì để lấy ra dữ liệu như mong muốn ở cột X.

Mong anh chị giúp đỡ. Em cảm ơn!

 

[huonglien1901]

Chào các anh chị,

Em có bảng dữ liệu như đính kèm. Không biết dùng công thức gì để lấy ra dữ liệu như mong muốn ở cột X.

Mong anh chị giúp đỡ. Em cảm ơn!

Bạn giải thích lại đi. Đọc bài bạn mà chẳng hiểu gì cả à?

 

[thanhvnu]

Bạn giải thích lại đi. Đọc bài bạn mà chẳng hiểu gì cả à?

Bác xem file đính kèm chưa ạ?

 

[quocgiacan]

Chào các anh chị,

Em có bảng dữ liệu như đính kèm. Không biết dùng công thức gì để lấy ra dữ liệu như mong muốn ở cột X.

Mong anh chị giúp đỡ. Em cảm ơn!

Bạn sửa tiêu đề chỗ "ACE" là gì đi, rồi bạn sẽ có tức khắc kết quả.

Chúc bạn ngày vui.

 

[VetMini]

Bạn sửa tiêu đề chỗ "ACE" là gì đi, rồi bạn sẽ có tức khắc kết quả.

Chúc bạn ngày vui.

Sửa hết cái tiêu đề bạn ơi.
Công thức nào lại chẳng lấy dữ liệu? Và đã lên đây hỏi thì cái nào lại chẳng khong biết dùng hàm gì?

 

[quocgiacan]

Chào các anh chị,

Em có bảng dữ liệu như đính kèm. Không biết dùng công thức gì để lấy ra dữ liệu như mong muốn ở cột X.

Mong anh chị giúp đỡ. Em cảm ơn!

Bạn tham khảo file đính kèm.
Nhớ vào File\Options\Formulas chỉnh chọn ô Enable iterative calculation

Chúc bạn ngày vui.

 

[thanhvnu]

Bạn tham khảo file đính kèm.
Nhớ vào File\Options\Formulas chỉnh chọn ô Enable iterative calculation

Chúc bạn ngày vui.

Em cảm ơn bác quocgiacan, vi diệu quá. Em đang soi công thức của bác mà vẫn thấy vi diệu quá. Bác có thể gợi ý cho e mấy cái gạch đầu dòng để em tự learn và chế biến được ko ạ? Em cảm ơn bác nhiều!

 

[quocgiacan]

Em cảm ơn bác quocgiacan, vi diệu quá. Em đang soi công thức của bác mà vẫn thấy vi diệu quá. Bác có thể gợi ý cho e mấy cái gạch đầu dòng để em tự learn và chế biến được ko ạ? Em cảm ơn bác nhiều!

Bây giờ tôi sắp về nhà rồi!

Tối tôi sẽ ghi cho bạn theo những gì khả năng tôi hiểu về công thức, vì công thức này tôi học từ anh @huuthang_bd.

Chúc bạn ngày vui.

 

[quocgiacan]

Em cảm ơn bác quocgiacan, vi diệu quá. Em đang soi công thức của bác mà vẫn thấy vi diệu quá. Bác có thể gợi ý cho e mấy cái gạch đầu dòng để em tự learn và chế biến được ko ạ? Em cảm ơn bác nhiều!

Giải thuật:

1. Tìm Vùng (từ cột C đến R) với mã tương ứng cần tìm.
   • Ví dụ: Mã "C0001" thì phải lấy được Vùng C2:R2
2. Xét Vùng vừa nhận, các ô (cột) nào có ký tự "x", thì lấy ra giá trị tiêu đề phía trên hàng 1, xong tính và giữ chỉ 2 giá trị cuối của các ô(cột) thỏa điều kiện.
   • Ví dụ: Mã "C0001", trong Vùng C2:R2 có cột D,E,F,G,H có ký tự "x", tương ứng lấy ra các giá trị: 102,103,104,105,106, rồi chỉ giữ 2 chữ số cuối như: 02,03,04,05,06.
3. Dựa vào các số vừa tìm được, và cũng trùng hợp 2 chữ số cuối của các mã lần lượt từ 1->16, nên sẽ lấy được "các" tên nguyên liệu trong bảng T1:U17.
   • Như Vd trên sẽ lấy được các tên NVL: {Nhôm;Đồng;Muối;Cát;Đá}
4. Cùng lúc với bước 3, dùng một "Vòng lặp" (chỉ là cách gọi cho dễ hiểu), như Vd trên thì công thức chạy và nối từ NVL đầu: "Nhôm", xong nhảy đến NVL kế: "Đồng" kết nối vào chữ "Nhôm" trước, và cứ tiếp tục như thế cho đến NVL cuối cùng.

Công thức: (số thứ tự dưới đây tương ứng với stt của giải thuật trên)
X2=IFERROR(IF($W2="","",IF($Y$1=1, OFFSET($U$1,AGGREGATE(15,6,MOD($C$1:$R$1,100)/(VungData="x"),1),),

X2 & IF($Y$1>COUNTIF(VungData,"x"),"", ", "&OFFSET($U$1,AGGREGATE(15,6,MOD($C$1:$R$1,100)/(VungData="x"),$Y$1),) )) ),"")​

1. Dùng Name "VungData"=OFFSET(Sheet1!$C$1:$R$1,MATCH(Sheet1!$W17,Sheet1!$B$2:$B$24,0),)
2. Dùng công thức =AGGREGATE(15,6,MOD($C$1:$R$1,100)/(VungData="x"),1). Với số 1 (màu đỏ) tức tìm giá trị cột đầu tiên thỏa (có ký tự "x"), như Vd trên là 102, dùng MOD(...,100) sẽ giữ lại 2 chữ số cuối: 02.
   
   1. Dùng OFFSET($U$1,AGGREGATE(15,6,MOD($C$1:$R$1,100)/(VungData="x"),1),) để trả ra NVL sắp hàng thứ 2 trong bảng (102) là: "Nhôm".
   • Tương tự, dùng: OFFSET($U$1,AGGREGATE(15,6,MOD($C$1:$R$1,100)/(VungData="x"),$Y$1),) trong công thức trên, để tính từ vị trí ô (cột) thứ 2 thỏa điều kiện trở đi, đồng thời trả về tên NVL nhờ "Vòng lặp" tại Y1. Như Vd trên: trả ra các NVL: {Đồng;Muối;Cát;Đá}
3. Công thức tại ô Y1=MOD(Y1,50)+1, bạn có thể hình dung như một "Vòng lặp" (giá trị cung cấp và giá trị kết quả cùng phụ thuộc vào 1 ô: Y1), Ví dụ: ô Y1 có giá trị đầu =1, thì MOD(1 ,50)+1=> bằng 2, nhưng do nó đang đứng tại Y1, nên Y1 nhận giá trị 2 vừa mới tìm được lại đưa tiếp vào MOD(2 ,50)+1=> bằng 3,.... cứ tiếp tục như thế đến giá trị 50, thì Y1=MOD(50, 50)+1=0+1=1 lại trả về giá trị 1. Trở lại từ đầu và cứ tiếp tục lặp lại như thế.....Để làm được điều này bạn phải nhấn chọn Enable iterative calculation trong Menu: File\Options\Formulas, nếu không hàm thực hiện không đúng và hiện cảnh báo 'Circular...' góc dưới bên phải.
4. Cuối cùng, các công thức điều kiện IF(....) dùng để phân vùng:
   • Đầu tiên nếu Y1=1, thì lấy NVL xuất hiện đầu tiên
   • Từ Y1=2 trở đi, thì lấy NVL thứ hai kế tiếp, cho đến khi Y1 đạt giá trị bằng tổng số ô (cột) có ký tự "x" trong "VungData", nếu Y1 lớn hơn sẽ trả về trị rỗng không nối vào nữa (công thức: $Y$1>COUNTIF(VungData,"x").
   • Bạn để ý sẽ thấy tại từng ô từ X2 (ô bạn muốn ra kết quả) trở xuống đều cũng là 1 "Vòng lặp", để nối các kết quả tìm được -->
     • X2=....X2& IF('Kết quả NVL thứ hai trở đi' )

Chúc bạn ngày vui.

 

[nguoi_ham_hoc]

Mình không phải chủ của topic này. Tuy nhiên, cho mình cám ơn bạn quocgiacan vì bạn đã rất tận tình, nhiệt tình giúp đỡ bạn chủ của topic này. Và mình cũng cám ơn anh HieuCD, Hoang2013, các Thầy, Cô, Anh, Chị trong GPE forum này đã giúp đỡ các bạn khi các bạn cần được giúp đỡ.
Mặc dù ngày nhà giáo Việt Nam 20/11 đã qua, trong diễn đàn này, có một số là các Thầy, Cô đang dạy trong các trường, một số đang đi làm, nhưng tất cả các Thầy, Cô, Anh, Chị, bạn đóng góp, quản lý forum GPE này đã cống hiến những bài giảng dạy về excel, những sự giúp đỡ, chỉ dẫn nhiệt tình .....Xin phép, cho tôi gửi lời chúc mừng tới các Thầy, Cô, Anh, Chị, bạn và gia đình luôn luôn mạnh khỏe, vui vẻ, hạnh phúc và thành công trong cuộc sống nhân ngày nhà giáo Việt Nam 20/11 nhé.
Trân trọng.

 

[thanhvnu]

Giải thuật:

1. Tìm Vùng (từ cột C đến R) với mã tương ứng cần tìm.
   • Ví dụ: Mã "C0001" thì phải lấy được Vùng C2:R2
   ........................

1. • Bạn để ý sẽ thấy tại từng ô từ X2 (ô bạn muốn ra kết quả) trở xuống đều cũng là 1 "Vòng lặp", để nối các kết quả tìm được -->
     • X2=....X2& IF('Kết quả NVL thứ hai trở đi' )

Chúc bạn ngày vui.

Em cảm ơn bác nhiều lắm, bác giúp tận tình quá. Chúc bác sức khỏe!!!