Giải thích công thức sử dụng hàm Offset

[trungcad]

Mình cũng đang là gà excel,đây là công thức của một cao thủ viết cho mình nhưng mình không hiểu mong mọi người phân tích chi tiết để mình hiểu về nó cảm ơn mọi người!

ct : =OFFSET($B$1,MOD(ROW(A642)-1,641),INT((ROW(A642)-1)/641)-1)

 

[VMH0307]

Mình cũng đang là gà excel,đây là công thức của một cao thủ viết cho mình nhưng mình không hiểu mong mọi người phân tích chi tiết để mình hiểu về nó cảm ơn mọi người!

ct : =OFFSET($B$1,MOD(ROW(A642)-1,641),INT((ROW(A642)-1)/641)-1)

Dựa vào công thức của bạn có thể thấy tác dụng của nó là chuyển 1 bảng dữ liệu có 641 hàng và n cột thành dữ liệu của 1 cột (tức kéo công thức này xuống thì nó sẽ lấy dữ liệu từ trên xuống và thứ tự cột từ trái qua phải)
Phân tích;
thông qua việc copy công thức của bạn xuống dưới
(1) Mod(row(A642)-1,641) cho kết quả từ 0 đến 640
(2) INT((Row(A642)-1)/641)-1 cho kết quả từ 0-n: là phần nguyên, bội số của 641
Vậy với Row(A642) đến Row(A1282) thì (1) cho kết quả từ 0 đến 640, và (2) cho kết quả là 0
đến Row(A1283) thì (1) cho kết quả trở về 0 và (2) nhảy lên 1
....
qua đó cho phép dịch chuyển theo dòng và cột bằng hàm offset
P/s: theo tôi có thể thay bằng công thức sau:

=OFFSET($B$1,MOD(ROW(1:1)-1,641),INT(ROW(1:1)/641))

 

[trungcad]

Dựa vào công thức của bạn có thể thấy tác dụng của nó là chuyển 1 bảng dữ liệu có 641 hàng và n cột thành dữ liệu của 1 cột (tức kéo công thức này xuống thì nó sẽ lấy dữ liệu từ trên xuống và thứ tự cột từ trái qua phải)
Phân tích;
thông qua việc copy công thức của bạn xuống dưới
(1) Mod(row(A642)-1,641) cho kết quả từ 0 đến 640
(2) INT((Row(A642)-1)/641)-1 cho kết quả từ 0-n: là phần nguyên, bội số của 641
Vậy với Row(A642) đến Row(A1282) thì (1) cho kết quả từ 0 đến 640, và (2) cho kết quả là 0
đến Row(A1283) thì (1) cho kết quả trở về 0 và (2) nhảy lên 1
....
qua đó cho phép dịch chuyển theo dòng và cột bằng hàm offset
P/s: theo tôi có thể thay bằng công thức sau:

=OFFSET($B$1,MOD(ROW(1:1)-1,641),INT(ROW(1:1)/641))

Ham offset có công thức là =offset(a,b,c,d,e) .Công thức trên không có d và e thì coi như độ rộng của bảng là n phần tử hả bác

 

[VMH0307]

Ham offset có công thức là =offset(a,b,c,d,e) .Công thức trên không có d và e thì coi như độ rộng của bảng là n phần tử hả bác

trong help có câu:
If height or width is omitted, it is assumed to be the same height or width as reference
thì nếu không có d, e thì kết quả trả về có cùng kích thước với tham chiếu a

 

[trungcad]

trong help có câu:
If height or width is omitted, it is assumed to be the same height or width as reference
thì nếu không có d, e thì kết quả trả về có cùng kích thước với tham chiếu a

Bạn có fb hay yahoo gì không cho mình xin mình muốn hỏi một vài chỗ mà ko hiểu gì cả

 

[VMH0307]

Bạn có fb hay yahoo gì không cho mình xin mình muốn hỏi một vài chỗ mà ko hiểu gì cả

Có gì bạn hỏi luôn trên diễn đàn này nhé!

 

[trungcad]

Theo mình biết thì cái công thức này nó có những đoạn như sau :

=OFFSET($B$1,MOD(ROW(A642)-1,641),INT((ROW(A642)-1)/641)-1)

$B$1 : Là ô bắt đầu

Mod () : là số đông từ chỗ xuất phát

Int () : Số cột từ vùng xuất phát

Có phải không bạn.Bạn giải thích cho mình sao lại dùng hàm mod va int được không ạ

 

[VMH0307]

Theo mình biết thì cái công thức này nó có những đoạn như sau :

=OFFSET($B$1,MOD(ROW(A642)-1,641),INT((ROW(A642)-1)/641)-1)

$B$1 : Là ô bắt đầu

Mod () : là số đông từ chỗ xuất phát

Int () : Số cột từ vùng xuất phát

Có phải không bạn.Bạn giải thích cho mình sao lại dùng hàm mod va int được không ạ

Đó là cú pháp của hàm Offset
còn sử dụng mod và int là việc áp dụng cho vào mục đích của riêng bạn
Bạn đọc lại #2 bên trên tôi có giải thích rồi đó
Mục đích và ý nghĩa của công thức này có đúng như thế không?

 

[trungcad]

Đó là cú pháp của hàm Offset
còn sử dụng mod và int là việc áp dụng cho vào mục đích của riêng bạn
Bạn đọc lại #2 bên trên tôi có giải thích rồi đó
Mục đích và ý nghĩa của công thức này có đúng như thế không?

Công thức này hoàn toàn đúng trong bài của mình! Nhưng mà mình mới học excel nên đọc mà không hiểu lắm...

 

[VMH0307]

Công thức này hoàn toàn đúng trong bài của mình! Nhưng mà mình mới học excel nên đọc mà không hiểu lắm...

Vậy thì bạn cần tìm hiểu từng bước 1 nhé, hãy tìm hiều về các hàm có trong công thức đó, offset, mod, int trong diễn đàn này hoặc trong help, rồi kết hợp lại.

 

[leonguyenz]

Theo mình biết thì cái công thức này nó có những đoạn như sau :

=OFFSET($B$1,MOD(ROW(A642)-1,641),INT((ROW(A642)-1)/641)-1)
$B$1 : Là ô bắt đầu
Mod () : là số đông từ chỗ xuất phát
Int () : Số cột từ vùng xuất phát
Có phải không bạn.Bạn giải thích cho mình sao lại dùng hàm mod va int được không ạ

Lấy số lớn quá làm bạn khó hiểu, vậy lấy thử 1 số nhỏ và bạn thực hành luôn thử xem.
Tại A1 bạn gõ công thức: =mod(row(a1)-1,4), bạn kéo xuống A20 thử xem. Kết quả có phải là 0,1,2,3,0,1,2,3,... hay không.
Tương tự bạn gõ công thức tại B1: =Int((row(a1)-1)/4), bạn kéo xuống B20. Kết quả có phải là 0,0,0,0,1,1,1,1,2,2,2,2, ... hay không?
Bây giờ là công thức Offset, tại C1 bạn gõ: =Offset($D$1,mod(row(A1)-1,4),Int((row(A1)-1)/4)). Công thức này tuơng đương với:
Tại C1: =Offset($D$1,0,0) = D1
Tại C2: =Offset($D$1,1,0) = D2
Tại C3: =Offset($D$1,2,0) = D3
Tại C4: =Offset($D$1,3,0) = D4
Tại C5: =Offset($D$1,0,1) = E1
Tại C6: =Offset($D$1,1,1) = E2
...
Từ đây chắc là bạn hiểu rồi đúng không?

 

[VetMini]

Hàm OFFSET, theo đúng tên của nó, là hàm dùng để dời tham chiếu từ vùng Range1 đến vùng Range2.

Tham số đầu tiên của nó là Range1.
2 tham số kế tiếp của nó là số dòng và số cột cần dời để tiến đến Range2

Nếu chỉ có thế thì dạng (cố cột và số dòng) của Range2 sẽ in hệt như Range1

Tuy nhiên, hàm này cũng cho phép thay đổi dạng của Range2. Đó là nhiệm vụ của 2 tham số sau cùng.

Công thức của bạn hình như có mục đích chuyển các phần tử của mảng 2 chiều 640 dòng và n cột thành ra mảng 1 chiều.

MOD(ROW(...)...) : dựa vào dòng của ô A642 (tức là 642), định vị dòng của mảng 2 chiều --> kq là 0, tức là dòng đầu tiên
INT(ROW(...).../...) : dựa vào dòng của ô A642 (tức là 642), định vị cột của mảng 2 chiều --> kq cũng là 0, tức là cột đầu tiên
Rốt cuộc lại, kết quả chung của công thức trên là B1
Nếu bạn kéo triển khai công thức này từ trên xuông dưới, A642 sẽ tự động đổi thành A643, A644, ... tức là B2, B3,...; Cho đến A1283, thì MOD(ROW(...)...) = 0 và INT(ROW(...).../...)= 1 ; và công thức của bạn sẽ đưa về "ô cách B1 0 dòng và 1 cột" => C1; nói cách khác là dòng 1, cột 2 của mảng 2 chiều.

Thông thường thì để tính thương số, người ta dùng hàm QUOTIENT chứ không đem chia rồi lấy INT; từ Quotient dịch ra tiếng Việt là thương số nguyên, nó đi đôi với MOD là con toán lấy số dư của phép chia.

 

[trungcad]

Lấy số lớn quá làm bạn khó hiểu, vậy lấy thử 1 số nhỏ và bạn thực hành luôn thử xem.
Tại A1 bạn gõ công thức: =mod(row(a1)-1,4), bạn kéo xuống A20 thử xem. Kết quả có phải là 0,1,2,3,0,1,2,3,... hay không.
Tương tự bạn gõ công thức tại B1: =Int((row(a1)-1)/4), bạn kéo xuống B20. Kết quả có phải là 0,0,0,0,1,1,1,1,2,2,2,2, ... hay không?
Bây giờ là công thức Offset, tại C1 bạn gõ: =Offset($D$1,mod(row(A1)-1,4),Int((row(A1)-1)/4)). Công thức này tuơng đương với:
Tại C1: =Offset($D$1,0,0) = D1
Tại C2: =Offset($D$1,1,0) = D2
Tại C3: =Offset($D$1,2,0) = D3
Tại C4: =Offset($D$1,3,0) = D4
Tại C5: =Offset($D$1,0,1) = E1
Tại C6: =Offset($D$1,1,1) = E2
...
Từ đây chắc là bạn hiểu rồi đúng không?

Ô thế mà từ trước giờ em cứ hiểu mod (m,n) = số dư của phép chia m/n nên em thấy khó hiểu.Mod (row(a1)-1,4) nó lại khác với ct mod(m,n) bác nhỉ?

 

[VMH0307]

Ô thế mà từ trước giờ em cứ hiểu mod (m,n) = số dư của phép chia m/n nên em thấy khó hiểu.Mod (row(a1)-1,4) nó lại khác với ct mod(m,n) bác nhỉ?

Đâu có khác gì đâu, vẫn là hàm lấy phần dư mà
chỉ có là người ta lợi dụng hàm Row(tham chiếu) (trả về chỉ số dòng của tham chiếu) để khi copy ra các dòng khác thực hiện gia tăng/ thay đổi giá trị thôi!
Row(A1) =1.....Row(A642)=642

 

[trungcad]

Đâu có khác gì đâu, vẫn là hàm lấy phần dư mà
chỉ có là người ta lợi dụng hàm Row(tham chiếu) (trả về chỉ số dòng của tham chiếu) để khi copy ra các dòng khác thực hiện gia tăng/ thay đổi giá trị thôi!
Row(A1) =1.....Row(A642)=642

Bạn giải thích cho mình tại sao lại có kết quả này và hết 4 nó lại quay lại 0 với ạ.Em mới học nên nhiều khi hỏi ngô nghê các bác thông cảm ^^
Mod(row(a1)-1,4) =mod(0,4)= 0
Mod(row(a2)-1,4) =mod(1,4)= 1
Mod(row(a3)-1,4) =mod(2,4)= 2
Mod(row(a4)-1,4) =mod(3,4)= 3
Mod(row(a5)-1,4) =mod(0,4)= 0

 

[VetMini]

Bạn giải thích cho mình tại sao lại có kết quả này và hết 4 nó lại quay lại 0 với ạ.Em mới học nên nhiều khi hỏi ngô nghê các bác thông cảm ^^
Mod(row(a1)-1,4) =mod(0,4)= 0
Mod(row(a2)-1,4) =mod(1,4)= 1
Mod(row(a3)-1,4) =mod(2,4)= 2
Mod(row(a4)-1,4) =mod(3,4)= 3
Mod(row(a5)-1,4) =mod(0,4)= 0

Có 2 điều quan trọng mà lúc dạy Excel, các thầy cô khong bao giờ chỉ (hoặc không biết để chỉ ?)
1. Nếu chỉ học bảng tính: Cách debug worksheet formula (công thức)
2. Nếu có học VBA: Cách debug code và sử dụng Immediate Window

Bạn click vào ô có công thức. Lên thanh công cụ formula, bôi đen từng phần và nhấn F9. Cứ làm vậy nhiều lần thì sẽ biết cách diễn giải công thức. (nhớ là mỗi lần F9 và đọc kết quả xong thì nhấn Esc để nó trở về nguyên dạng)

Trong công thức trên " Mod(row(a5)-1,4) ", nếu bạn bôi đen " row(a5) " thì F9 sẽ cho ra 5; bôi đen " row(a5)-1 " thì F9 sẽ cho ra 4. Tuần tự như vậy, bôi đen " Mod(row(a5)-1,4) " thì F9 sẽ cho ra 0

hết 4 nó lại quay lại 0 : bạn diễn giải sai rồi, Mod(row(a5)-1,4) =mod(0,4) là không đúng; Mod(row(a5)-1,4) =mod(4,4) mới đúng.
Sô dư của 4 chia cho 4 là 0

 

[trungcad]

Có 2 điều quan trọng mà lúc dạy Excel, các thầy cô khong bao giờ chỉ (hoặc không biết để chỉ ?)
1. Nếu chỉ học bảng tính: Cách debug worksheet formula (công thức)
2. Nếu có học VBA: Cách debug code và sử dụng Immediate Window

Bạn click vào ô có công thức. Lên thanh công cụ formula, bôi đen từng phần và nhấn F9. Cứ làm vậy nhiều lần thì sẽ biết cách diễn giải công thức. (nhớ là mỗi lần F9 và đọc kết quả xong thì nhấn Esc để nó trở về nguyên dạng)

Trong công thức trên " Mod(row(a5)-1,4) ", nếu bạn bôi đen " row(a5) " thì F9 sẽ cho ra 5; bôi đen " row(a5)-1 " thì F9 sẽ cho ra 4. Tuần tự như vậy, bôi đen " Mod(row(a5)-1,4) " thì F9 sẽ cho ra 0

hết 4 nó lại quay lại 0 : bạn diễn giải sai rồi, Mod(row(a5)-1,4) =mod(0,4) là không đúng; Mod(row(a5)-1,4) =mod(4,4) mới đúng.
Sô dư của 4 chia cho 4 là 0

Bạn nói rất đúng,mình bị nhầm chỗ dòng cuối.Sao mod(4,4)=mod(0,4)=0 ? và mod(1,4)=1 mình chưa hiểu cái này.Cảm ơn bạn

 

[VetMini]

0 / 4 : số thương là 0, số dư là 0
1 / 4 : số thương là 0, số dư là 1
2 / 4 : số thương là 0, số dư là 2
3 / 4 : số thương là 0, số dư là 3
4 / 4 : số thương là 1, số dư là 0
5 / 4 : số thương là 1, số dư là 1
6 / 4 : số thương là 1, số dư là 2
7 / 4 : số thương là 1, số dư là 3
8 / 4 : số thương là 2, số dư là 0
9 / 4 : số thương là 2, số dư là 1
...

Định nghĩa của phép chia, số thương và số dư là vậy. Căn bản số học lớp vỡ lòng.

=== * ===

Bài toán ban đầu của bạn là bài toán chuyển đổi mảng 1 chiều thành 2 chiều. Để có thể chuyển dòng của mảng 1 chiều thành dòng+cột của mảng 2 chiều, bạn cần phải nắm vững kỹ thuật dùng MOD (số dư -> cột) và QUOTIENT (số thương -> dòng)

 

[trungcad]

0 / 4 : số thương là 0, số dư là 0
1 / 4 : số thương là 0, số dư là 1
2 / 4 : số thương là 0, số dư là 2
3 / 4 : số thương là 0, số dư là 3
4 / 4 : số thương là 1, số dư là 0
5 / 4 : số thương là 1, số dư là 1
6 / 4 : số thương là 1, số dư là 2
7 / 4 : số thương là 1, số dư là 3
8 / 4 : số thương là 2, số dư là 0
9 / 4 : số thương là 2, số dư là 1
...

Định nghĩa của phép chia, số thương và số dư là vậy. Căn bản số học lớp vỡ lòng.

=== * ===

Bài toán ban đầu của bạn là bài toán chuyển đổi mảng 1 chiều thành 2 chiều. Để có thể chuyển dòng của mảng 1 chiều thành dòng+cột của mảng 2 chiều, bạn cần phải nắm vững kỹ thuật dùng MOD (số dư -> cột) và QUOTIENT (số thương -> dòng)

Ôi trời quên mất đấy,cảm ơn bạn mình lại nghĩ đâu đâu