Hỏi về bổ sung địa chỉ (tên hàng, tên cột) (5 người xem)

[meoconnghichchuot]

Có một bài thi Excel ngay dòng đầu tiên người ta đã yêu cầu thế này:

Dựa vào Tên và Ngày sinh, bổ sung địa chỉ (tên hàng, tên cột) trong bài thi Excel:
Lấy ký tự đầu của Tên (người dự thi) đặt tên cột, lấy ngày sinh để đặt tên hàng. Ví dụ: Lê Xuân Hoàng sinh 15/08/87 thì bổ sung địa chỉ sau:

Mình ngẫm nghĩ mãi mà không hiểu "bổ sung" ở đây có ý muốn yêu cầu mình làm gì? Chèn thêm ô hay bắt đầu nhập dữ liệu từ ô có địa chỉ như yêu cầu, hoặc là làm cho cột và hàng yêu cầu trở thành cột, hàng đầu tiên trong sheet.

Các bạn! Ai đọc hiểu câu trên xin vui lòng giải thích giúp mình nhé!

Cảm ơn nhiều!

 

[chibi]

Mình cũng không hiểu. Nếu tên là "Đức" thì điền vào cột nào?

 

[cadafi]

Có một bài thi Excel ngay dòng đầu tiên người ta đã yêu cầu thế này:

Dựa vào Tên và Ngày sinh, bổ sung địa chỉ (tên hàng, tên cột) trong bài thi Excel:
Lấy ký tự đầu của Tên (người dự thi) đặt tên cột, lấy ngày sinh để đặt tên hàng. Ví dụ: Lê Xuân Hoàng sinh 15/08/87 thì bổ sung địa chỉ sau:

Mình ngẫm nghĩ mãi mà không hiểu "bổ sung" ở đây có ý muốn yêu cầu mình làm gì? Chèn thêm ô hay bắt đầu nhập dữ liệu từ ô có địa chỉ như yêu cầu, hoặc là làm cho cột và hàng yêu cầu trở thành cột, hàng đầu tiên trong sheet.

Các bạn! Ai đọc hiểu câu trên xin vui lòng giải thích giúp mình nhé!

Đã đến nước này thì bạn đưa luôn đề bài bằng excel lên để mọi người ngâm cứu nhé! Chứ như mình, thực sư chưa hiểu gì hết!

 

[meoconnghichchuot]

Đã đến nước này thì bạn đưa luôn đề bài bằng excel lên để mọi người ngâm cứu nhé! Chứ như mình, thực sư chưa hiểu gì hết!

Cảm ơn bạn Ca_dafi!
Nhưng bài thi này cho trả lời trên giấy (thi viết). không phải thi trên máy tính nên file mình có chỉ là file *.doc chứ không phải *.xls. Tuy nhiên mình cũng đưa nó lên cho mọi người cùng xem!

 

[nokiano258vn]

Đề thi của bạn thật khó hiểu, nhưng theo mình nghĩ thì là bạn lấy tên và ngày sinh của mình để làm địa chỉ (vì có thấy nói đến tên người dự thi - chắc để dễ nhận ra là bài của ai khi đã dọc phách).
Bài thi viết ra giấy thì chắc chỉ cần trả lời ví dụ: H15

 

[ndu96081631]

Đừng trách bạn ấy, vì bạn ấy chỉ là thí sinh!
Đại ca nào ra đề này (nhất là câu hỏi 1) chứng tỏ trình độ của đại ca cao thật, đọc mà hỏng hiểu đại ca muốn nói cái gì?
Hay!

 

[cadafi]

Thế mới nói, người ra đề không biết có đặt vị trí mình là thí sinh hay không? Dùng toàn từ cao siêu!

Theo em nghĩ bài này như sau:
Nếu tên thí sinh là Lê Xuân Hoàng, sinh ngày 15/08/1987 thì sẽ dùng công thức nào cho ra địa chỉ là ký tự đầu của tên và ngày sinh, trong trường hợp này là: $H$15
Tương tự:
Võ Tuấn Kiệt 21/06/1980 --> $K$21
Nguyễn Thị Bưởi 13/12/1978 ==> $B$13
Nguyễn Văn Sơn 28/12/1967 ---> $S$28

Như vậy, có thể dùng công thức sau:

=CELL("Address",INDIRECT(LEFT(TRIM(RIGHT(SUBSTITUTE("Lê Xuân Hoàng"," ",REPT(" ",15)),15)),1)&DAY("15/08/1987")))

Tuy nhiên, người ra đề quên mất một điều là, người Việt Nam còn có tên như sau:

Nguyễn Mang Ơn 12/11/1979, chẳng lẽ cho địa chỉ là $Ơ$12 ????
Lê Hồng Ân 22/11/1986, chẳng lẽ cho địa chỉ $Â$22 ???

Thiệt chịu không nổi người ra đề!

 

[BNTT]

Theo tôi hiểu thì cái mà bạn Mèo con nghịch chuột trích dẫn ở bài 1 không phải là đề thi. Mà nó chỉ là phần hướng dẫn đặt mã số cho bài thi thì đúng hơn. Còn phần đề thi thì nằm ở dưới (trong đề thi bạn ấy đã gửi lên). Và hình như câu trả lời của nokiano258vn là đúng (?)

Tuy nhiên, cho dù không phải là đề thi, thì đoạn này cũng rất là khó hiểu! Và chắc người ra đề đã không lường trước nhiều tình huống rắc rối sẽ xảy ra. Ngoài những cái Ca_Dafi đã nêu, còn có những trường hợp như, trong trường hợp dùng để đánh mã số bài chẳng hạn, thì những người có tên bắt đầu bằng chữ H (ở VN thì nhiều lắm lắm) và sinh ngày 15 đều là H15 ???

Nhưng thôi, tôi nghĩ, trừ phi có cách lý giải nào khác của bạn Mèo con nghịch chuột, chúng ta chẳng cần mổ xẻ thêm vấn đề này làm gì nữa, vì theo tôi thấy thì chẳng có ích lợi gì hết (cũng giống như ngồi đây mà bàn chuyện chừng nào VN hết bị cúp điện!). Nếu như bạn ấy không có nhu cầu giải dùm cái đề thi thật sự, thì có lẽ topic này sẽ được đóng cửa.

Đây là cái đề thi thật sự (nằm dưới cái đoạn trích đã nói ở trên). Tôi để luôn ở đây dành cho bạn nào thích ngâm cứu. Đề thi này, ngược hẳn với cái đoạn trích trên, thuộc loại dễ.

 

[meoconnghichchuot]

Các bạn ! Nếu giải câu 1 của đề thi trên mình phải viết công thức thế nào ?

 

[BNTT]

Các bạn ! Nếu giải câu 1 của đề thi trên mình phải viết công thức thế nào ?

Hướng dẫn thôi nhé:

Dùng hàm LEFT để trích ra ký tự đầu tiên của Mã hàng.
Rồi lấy ký tự trích ra được đó, dùng hàm VLOOKUP, đi dò với bảng Danh mục máy nông nghiệp, nếu tìm ra thì lấy tên hàng ở cột thứ 2 (cột TGENLK).

Bạn có thể tham khảo các hàm ở đây: hàm LEFT, hàm VLOOKUP

 

[cadafi]

Các bạn ! Nếu giải câu 1 của đề thi trên mình phải viết công thức thế nào ?

Bạn xem file đính kèm nhé! Toàn bộ câu trả lời nằm trong file này! Chúng ta sẽ thảo luận tiếp!

 

[meoconnghichchuot]

Hỏi Về Bổ Sung Địa Chỉ (tên hàng,tên cột)

Bạn xem file đính kèm nhé! Toàn bộ câu trả lời nằm trong file này! Chúng ta sẽ thảo luận tiếp!

Cảm ơn bạn đã bỏ công sức và thời gian để giải bài thi. Mình đã xem bài giải của bạn. Mình cũng đã thử làm nhưng mình không biết dùng hàm SUMPRODUCT như bạn nên mình đã dùng hàm khác. Bạn xem thử rồi cho mình biết nhận xét của bạn.
Và nếu tiện, bạn viết cú pháp hàm SUMPRODUCT nhé!
Mà này! Giả sử mình tên Chi và ngày sinh của mình là 09, mình viết lên giấy những công thức thế này được không:
Câu 1 : D22=VLOOKUP(LEFT(C22,1),C$11$13,2,0)
Câu 2 : G22=VLOOKUP(D22,D$11:F$13,IF(MID(C22,2,1)="N",2,3),0)*IF(RIGHT(C22,1)="X",109%,1)
.......

 

[BNTT]

Bạn xem file đính kèm nhé! Toàn bộ câu trả lời nằm trong file này! Chúng ta sẽ thảo luận tiếp!

Cái công thức tính số máy cày bán được trong tháng 8 và tháng 9, Kiệt nên gộp 2 điều kiện thành 1, khỏi cần làm 2 cái rồi cộng lại, để công thức ngắn hơn:

=SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)*(K2:K11)*(LEFT(H2:H11,1)="S"))

Cái của Kiệt:

=SUMPRODUCT(--(MONTH(J2:J11)=8),--(LEFT(H2:H11,1)="S"),K2:K11)+SUMPRODUCT(--(MONTH(J2:J11)=9),--(LEFT(H2:H11,1)="S"),K2:K11)

 

[cadafi]

Cảm ơn bạn đã bỏ công sức và thời gian để giải bài thi. Mình đã xem bài giải của bạn. Mình cũng đã thử làm nhưng mình không biết dùng hàm SUMPRODUCT như bạn nên mình đã dùng hàm khác. Bạn xem thử rồi cho mình biết nhận xét của bạn.
Và nếu tiện, bạn viết cú pháp hàm SUMPRODUCT nhé!
Mà này! Giả sử mình tên Chi và ngày sinh của mình là 09, mình viết lên giấy những công thức thế này được không:
Câu 1 : D22=VLOOKUP(LEFT(C22,1),C$11$13,2,0)
Câu 2 : G22=VLOOKUP(D22,D$11:F$13,IF(MID(C22,2,1)="N",2,3),0)*IF(RIGHT(C22,1)="X",109%,1)
.......
View attachment 13447

Chào bạn,
Bạn khá vững về excel đấy chứ hỉ! Thật đấy! Mong được chia sẻ nhiều hơn nữa!

 

[meoconnghichchuot]

Cái công thức tính số máy cày bán được trong tháng 8 và tháng 9, Kiệt nên gộp 2 điều kiện thành 1, khỏi cần làm 2 cái rồi cộng lại, để công thức ngắn hơn:

=SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)* (K2:K11)*(LEFT(H2:H11,1)="S"))

Bạn Bùi Nguyễn Triệu Tương ơi!
Như mình đã nói ở trên, mình chưa dùng hàm SUMPRODUCT lần nào, nên mình không hiểu.
Bạn vui lòng giải thích công thức : =SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)* (K2:K11)*(LEFT(H2:H11,1)="S")) giùm mình nha!

Cảm ơn

 

[BNTT]

Bạn Bùi Nguyễn Triệu Tương ơi!
Như mình đã nói ở trên, mình chưa dùng hàm SUMPRODUCT lần nào, nên mình không hiểu.
Bạn vui lòng giải thích công thức : =SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)* (K2:K11)*(LEFT(H2:H11,1)="S")) giùm mình nha!

Cảm ơn

Giái thích theo lý thuyết thì bạn khó hiểu lắm. Tôi sẽ nói theo kiểu bình dân nha:

Trong công thức đó có 4 phần, nối với nhau bằng dấu (*), là một phép nhân. Vì là một phép nhân nên muốn sắp cái nào trước cái nào sau cũng được:

1. (MONTH(J2:J11)>=8) : Tháng của những giá trị trong dãy J2:J11 lớn hơn hoặc bằng 8
2. (MONTH(J2:J11)<=9) : Tháng của những giá trị trong dãy J2:J11 nhỏ hơn hoặc bằng 9
3. (K2:K11) : Những giá trị trong K2:K11
4. (LEFT(H2:H11,1)="S") : Ký tự đầu của những ô trong dãy H2:H11 là S
​

Do cái thứ 3 không phải là công thức, nên nó được dùng để tính tổng của các giá trị trong đó mà thỏa mãn những giá trị còn lại: 1, 2 và 4 (và có thể nhiều hơn nữa).

Đại khái, công thức trên nói rằng, tính tổng của những giá trị trong K2:K11, mà giá trị cùng hàng với chúng ở bên dãy J2:J11 (là một dãy ngày tháng) có số tháng là 8 hoặc 9, và giá trị nằm trong cùng hàng với chúng ở bên dãy H2:H11 có ký tự đầu tiên là chữ S.

Theo bài của bạn, nó sẽ tìm những số lượng máy cày bán ra trong tháng 8 và tháng 9.

Chả biết nói vậy bạn có hiểu không. Nhưng nếu để tính tổng theo nhiều điều kiện, bạn cứ đưa hết những điều kiện (là những công thức logic, đúng hay sai) vào trong công thức của hàm SUMPRODUCT, bỏ mỗi cái điều kiện trong một cặp dấu (), và nối tất cả lại với nhau bằng dấu *. Và nhớ phải có một thành phần dùng để tính tổng, thành phần này chỉ là một dãy mà không phải là công thức (chính là cái K2:K11 trong ví dụ trên), và thành phần này cũng nằm trong một cặp dấu (), được nối với những thành phần "điều kiện" bằng dấu *.

Làm riết rồi quen à bạn ơi.

Bạn đọc thêm ở đây nhé: Hàm SUMPRODUCT

 

[cadafi]

............... mình chưa dùng hàm SUMPRODUCT lần nào, nên mình không hiểu.
Bạn vui lòng giải thích công thức : =SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)* (K2:K11)*(LEFT(H2:H11,1)="S")) giùm mình nha!

SUMPRODUCT() mục đích để tính tổng của tích các phần tử trong các mảng có cùng số phần tử. Ví dụ:
=Sumproduct({1,2},{3,4})=(1*3)+(2*4) = 11
------------------------------------------------------------------------------
Bạn thấy, trong công thức bên dưới:

=SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)*(K2:K11)*(LEFT(H2:H11,1)="S"))

Chúng ta có 4 mảng con có cùng số phần tử giao nhau bởi dấu *:
Trong đó:

Array1: (MONTH(J2:J11)>=8) bao gồm các phần tử có giá trị là True/False

Array2: (MONTH(J2:J11)<=9) bao gồm các phần tử có giá trị là True/False

Array3: (K2:K11) bao gồm các phần tử mang giá trị số

Array4: (LEFT(H2:H11,1)="S") bao gồm các phần tử có giá trị là True/False

Hàm Sumproduct sẽ cộng tích từng phần tử lần lượt trong 4 mảng này như sau:

Sumproduct = Array1(1)*Array2(1)*Array3(1)*Array4(1)
+ Array1(2)*Array2(2)*Array3(2)*Array4(2)
+ Array1(3)*Array2(3)*Array3(3)*Array4(3)
+ .......................................................
+ Array1(n)*Array2(n)*Array3(n)*Array4(n)

Bạn xem thêm diễn giải tại file đính kèm nữa sẽ rõ.

Vì đây là công thức mảng nên phải dùng sumproduct. Nếu bạn không ngại phải Ctrl+Shift+Enter thỉ vẫn dùng hàm SUM bình thường vẫn được.

{=SUM((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)*(K2:K11)*(LEFT(H2:H11,1)="S"))}

 

[BNTT]

Giải thích thêm tí về tại sao dùng cái dấu *

Dấu * chính là phép tính nhân. Và tại sao nó nhân các phần tử công thức lại với nhau được?
Là do những công thức như MONTH(J2:J11)>=8 sẽ trả về TRUE nếu đúng và FALSE nếu sai.
Và theo quy ước của Excel thì TRUE có giá trị là 1, và FALSE có giá trị là 0.

Vậy, Hàm SUMPRODUCT sẽ lần lượt nhân (PRODUCT) từng "bộ" các phần tử lại với nhau. Hễ "bộ" nào có một điều kiện trả về FALSE, thì "bộ" đó sẽ bằng 0, còn nếu như các điều kiện đều đúng, thì "bộ" đó sẽ bằng giá trị của phần tử không-phải-là-công-thức (K2:K11 chẳng hạn) nhân với những con số 1, và đương nhiên là bằng chính con số đó. Sau khi đã nhân xong, hàm sẽ cộng (SUM) tất cả các kết quả lại. Và dĩ nhiên, nó chỉ cộng những "bộ" nào có kết quả khác 0, là những "bộ" mà thỏa mãn hết cả các điều kiện.

Bạn hiểu SUMPRODUCT rồi chứ? Trong tiếng Anh, SUM = cộng (tổng), PRODUCT = nhân (tích)
Do đó, SUMPRODUCT = tổng của các tích số.

 

[meoconnghichchuot]

=SUMPRODUCT((MONTH(J2:J11)>=8)*(MONTH(J2:J11)<=9)* (K2:K11)*(LEFT(H2:H11,1)="S"))

Cảm ơn Hai bạn!
Các bạn đã giải thích thật chi tiết cộng thêm Link hướng dẫn, thế là mình đã hiểu, đã học được thêm một hàm mới.
Tuy nhiên còn tí tẹo vướng mắc nữa.
Đó là trong công thức trên vì sao không phải là :
month(ngay)=8 * month(ngay)=9
Mà là :
month(ngay)>=8 * month(ngay)<=9

Và có thể thay dấu (*) bằng dấu (,) như trong cú pháp được không?

 

[BNTT]

...Tuy nhiên còn tí tẹo vướng mắc nữa.
Đó là trong công thức trên vì sao không phải là :
month(ngay)=8 * month(ngay)=9
Mà là :
month(ngay)>=8 * month(ngay)<=9

Và có thể thay dấu (*) bằng dấu (,) như trong cú pháp được không?

Không được!

1. Cái dấu này: <= bạn đọc như thế nào? Nhỏ hơn HOẶC bằng, phải không? Và cái >= cũng rứa. Được hay không là ở cái chữ HOẶC đấy bạn à!
   
   Nếu bạn dùng dấu =, thì sẽ không có ngày nào trong cột đó vừa bằng 8 vừa bằng 9 cả, do đó kết quả sẽ là 0. Còn nếu dùng <= và >= như của tôi, thì nó sẽ tìm ngày tháng nào đó hoặc là bẳng 8, hoặc là bằng 9. Bạn hiểu chứ.
   
   
2. Còn thay dấu nhân (*) bằng dấu phẩy (,) thì kết quả cũng sẽ là 0. Theo tôi hiểu thì, chỉ sử dụng dấu phẩy (,) như trong cú pháp, khi nào các đối số là những mảng xác định (bạn xem lại ví dụ trong bài Hàm SUMPRODUCT); còn với những mảng tạo ra bởi một công thức, thì không được.
   
   Không biết có ai có thể giải thích rõ hơn chuyện này không...