Hỏi cách dùng hàm Offset (1 người xem)

[feelingyes]

Em tạo một file và cần file dùng nhiều đến Offset

Vì trong file có dung lượng lớn, và có xài hàm sumproduct nên em đã dùng ý tưởng của AnhTuan 1006 để loại bớt một name Date (nhưng file của bác AnhTuan1006 chỉ áp dụng cho những ngày trong tháng không lặp lại)

Ở file của em Ngày trong tháng có thể lặp lại nhiều lần (ở sheet Tracking)
Các cao thủ xem file và mong có ý kiến trợ giúp.

XIn cám ơn các cao thủ

 

[feelingyes]

Cám ơn các bác
Em đã làm được rồi, He he chỉ cần dùng thêm hàm match (với type 0 cho From, và type 1 cho To để tính độ trượt cho range)
He he Tự làm được vui quá

Các Bác xem thử em làm như này có được không?

 

[anhtuan1066]

Hay lắm... vừa tính làm thì bạn đã làm xong, cách bạn làm cũng gần gần giống với cách tôi đang làm trong topic này, bài #10:
http://www.giaiphapexcel.com/forum/showthread.php?t=8458
Tốc độ tính toán đáng nể ko? he.. he..
Vẫn còn có thể làm cho nó nhanh thêm nữa đấy... Bạn tính toán và định vị thật chính xác vùng dử liệu thì chẳng sợ gì số dòng của nó dài đến bao nhiêu
ANH TUẤN

 

[feelingyes]

Vẫn còn có thể làm cho nó nhanh thêm nữa đấy...
ANH TUẤN

Anh Có thể hé mở cho em một chút không?

 

[anhtuan1066]

Anh Có thể hé mở cho em một chút không?

Tôi vừa post lên giãi pháp nhanh nhất đó.. bài số #12 .. Bạn tham khảo đi... Mấu chốt là mấy hàm dùng đễ định vị range thôi, gần giống cách bạn đang làm..
Bạn tải file về... Unhide cột ẩn AA, AB, AC, AD và AE đễ xem công thức nhé!
Mến
ANH TUẤN

 

[feelingyes]

File tuyệt thật AnhTuan
Em cố gắng học hỏi, có gì không hiểu sẽ hỏi anh sau
Cám ơn anh

 

[anhtuan1066]

Tôi nghĩ bạn hoàn toàn có thể hiểu dc file tôi.. vì công thức quá đơn giản... Toàn là MATCH... hi.. hi..
Mấu chốt vấn đề ở đây là bắt buộc phải sắp xếp dử liệu theo trật tự nhất định ở 1 cột nào đó mà ta đang quan tâm... Như trong file tôi, tôi cần trích theo ngày tháng nên CSDL của tôi đã dc sắp theo ngày tháng tăng dần...
Còn trong file của bạn cũng nên làm vậy... mục đích cuối cùng là loại bỏ bớt các điều kiện trong SUMPRODUCT (vì mình đã định vị range rồi)... đễ đi đến bước cuối cùng chỉ còn duy nhất 1 điều kiện.. Lúc đo ta hoàn toàn có thể bỏ luôn hàm SUMPRODUCT đễ chuyển sang xài SUMIF...
Vì theo nhận xét của các cao thủ thì SUMIF luôn luôn nhanh hơn SUMPRODUCT khi lọc cùng 1 CSDL
Bạn cố gắng lên, ko ai hiểu file của bạn bằng chính bạn.. nên tôi chắc bạn sẽ thành công trong việc tối ưu file của mình!
Mến
ANH TUẤN

 

[feelingyes]

Sau một tiếng đồng hồ "nghiên cứu" công thức của anh. Em mới vỡ vạc được ít nhiều
Anh cho em hỏi một chút nhé
2. Anh cho em hỏi công thức sau với, Vì không thấy ở trong sách

{=MATCH(TRUE,(Detail!$D$1:$D$60000>=DATE($B$5,$AA$8,1)),0)}
True ở đây đại diện hay được hiểu như thế nào ạ.

Nói tóm lại, Để làm được các công thức như trong file của anh thì đòi hỏi một sự sáng tạo thật tuyệt vời

 

[anhtuan1066]

Oh... hô... dễ hiểu mà...
Này nhé: mãng Detail!$D$1:$D$60000>=DATE($B$5,$AA$ 8,1) sẽ cho ta 1 mãng với các phần tử TRUE, FALSE, đại khái nó lấy từng em trong Detail!$D$1:$D$60000 so sánh với DATE($B$5,$AA$ 8,1) cái nào so sánh là đúng thì = TRUE, ngược lại thì =FALSE...
Ở đây tôi giã sử Detail!$D$1:$D$60000 là 1 range ngày tháng ko liên tục (ko liên tục chứ ko phải lộn xộn)... tức có thể có ngày 2 tây nhưng lại ko có ngày 1 tây.. có thể trong tháng ta nhập liệu từ 6 tây trở đi.. vân vân (do ngày nghĩ ko làm việc chẳng hạn)... Vậy thì nhiệm vụ của ta là cần tìm em ngày tháng nào nhỏ nhất trong tháng đang chọn... Ví dụ tôi chọn lọc tháng 2, vậy trong range Detail!$D$1:$D$60000 em nào đúng là tháng 2 và nhỏ nhất thì lấy... Hàm MATCH ko có vụ dò tim này nên đành phải dùng công thức mãng...
{=MATCH(TRUE,(Detail!$D$1:$D$60000>=DATE($B$5,$AA$ 8,1)),0)}
nghĩa là tìm em TRUE đầu tiên trong phần so sánh Detail!$D$1:$D$60000>=DATE($B$5,$AA$ 8,1)
Nếu trong file của bạn, cột ngày tháng luôn liên tục, ko thiếu ngày nào thì tất nhiên ko cần dùng hàm này rồi... chỉ cần MATCH với tham số 0 là xong
Bạn hiểu chứ
ANH TUẤN

 

[quean]

Chao Anh Tuan
Khi dung hàm offset để lấy giá trị từ file excel khác minh hay gap loi VALUE, vi khong mo file do ra, co cach nao de khac phuc loi VALUE khong ?
Hoac de tranh vi loi nay minh co cach dung nao khac thay the ko? de lay gia tri chay theo dong va cot khac nhau

Cam on ban

 

[chienlh]

Các ACE cao thủ của GPE ơi, giải thích giúp mình cái công thức trên được không. Mình đọc mà không hiểu chổ $B$3-$B$2+1
còn các chổ khác thì mình đã hiểu hết. Mình cám ơn ACE cao thủ rất nhiều.

 

[khuongvietphong]

View attachment 145355
Các ACE cao thủ của GPE ơi, giải thích giúp mình cái công thức trên được không. Mình đọc mà không hiểu chổ $B$3-$B$2+1
còn các chổ khác thì mình đã hiểu hết. Mình cám ơn ACE cao thủ rất nhiều.

B3-B2=4, cộng thêm 1 là bằng 5. Tức là vùng trả về gồm 1 hàng và 5 cột

 

[chienlh]

B3-B2=4, cộng thêm 1 là bằng 5. Tức là vùng trả về gồm 1 hàng và 5 cột

Mình đã hiểu rồi, cám ơn khuongvietphong rất nhiều nhé.

 

[nhunhu88]

Bạn giải thích khá chi tiết, Thanks bạn nhiều nhé

 

[kbw1]

Hàm này mình không hiểu nổi.
ko thể hiểu được hàm quái quỷ này.
Hiểu thì hiểu nguyên lý như vậy. Nhưng khi áp dụng thì ko thể được.
Muốn chết vì cái hàm này quá.

 

[leo1286]

Kính các anh chị,

Cùng với nội dung như thế này nhưng em lại có 1 yêu cầu khác. Đó là dùng công thức để liệt kê tất cả các ngày phát sinh chi phí cho mỗi công việc. Kết quả sẽ hiển thị đối với Công việc 1 theo cấu trúc: 12/8/2015, 13/8/2015, 14/8/2015

Mong anh chị giải đáp giúp với. Em cảm ơn!

QUOTE=chienlh;666606]View attachment 145355
Các ACE cao thủ của GPE ơi, giải thích giúp mình cái công thức trên được không. Mình đọc mà không hiểu chổ $B$3-$B$2+1
còn các chổ khác thì mình đã hiểu hết. Mình cám ơn ACE cao thủ rất nhiều.[/QUOTE]

 

[lehuong1409]

Kính các anh chị,

Cùng với nội dung như thế này nhưng em lại có 1 yêu cầu khác. Đó là dùng công thức để liệt kê tất cả các ngày phát sinh chi phí cho mỗi công việc. Kết quả sẽ hiển thị đối với Công việc 1 theo cấu trúc: 12/8/2015, 13/8/2015, 14/8/2015

Mong anh chị giải đáp giúp với. Em cảm ơn!

QUOTE=chienlh;666606]View attachment 145355
Các ACE cao thủ của GPE ơi, giải thích giúp mình cái công thức trên được không. Mình đọc mà không hiểu chổ $B$3-$B$2+1
còn các chổ khác thì mình đã hiểu hết. Mình cám ơn ACE cao thủ rất nhiều.

[/QUOTE]
Bạn gửi file của bạn lên hoặc chí ít là file giả định và kết quả trả về mong muốn mọi người sẽ dễ giúp bạn hơn. Chứ tự mò biết đâu lại hiểu sai ý bạn?

 

[leo1286]

Chào @lehuong1409,

Tôi đã tạo một file mới với đề bài sử dụng công thức để hiển thị dữ liệu như trong ô Kết quả. Rất mong được @lehuong1409 và các anh chị trên diễn đàn hỗ trợ. Cảm ơn!

 

[lehuong1409]

Chào @lehuong1409,

Tôi đã tạo một file mới với đề bài sử dụng công thức để hiển thị dữ liệu như trong ô Kết quả. Rất mong được @lehuong1409 và các anh chị trên diễn đàn hỗ trợ. Cảm ơn!

Trong lúc nghĩ công thức mình làm tạm cho bạn cái hàm tự tạo này nhé. Bạn dùng thử xem.
Công thức là Kq(rg,col)
rg là vùng mà bạn muốn xét
col là dữ liệu tiêu đề cần diễn giải nằm ở dòng thứ mấy nhé. Bạn xem file chi tiết
Nhớ Enable this content trước khi sử dụng file nhé

 

[leo1286]

Cảm ơn @lehuong1409 đã nhiệt tình hỗ trợ nhé. Chúng ta cùng thử ngồi nghĩ xem có cách nào sử dụng công thức được hay không? Hy vọng bạn sớm cho ra kết quả

 

[lehuong1409]

Cảm ơn @lehuong1409 đã nhiệt tình hỗ trợ nhé. Chúng ta cùng thử ngồi nghĩ xem có cách nào sử dụng công thức được hay không? Hy vọng bạn sớm cho ra kết quả

Tại phát sinh nhiều điều kiện kèm nên công thức khá dài thôi. Mình vẫn chưa tìm đc phương án tối ưu, bài kiểu này m thấy dùng VBA là nhanh nhất đấy

 

[ndu96081631]

Cảm ơn @lehuong1409 đã nhiệt tình hỗ trợ nhé. Chúng ta cùng thử ngồi nghĩ xem có cách nào sử dụng công thức được hay không? Hy vọng bạn sớm cho ra kết quả

Có 5 cột thì làm 5 cái IF nối lại với nhau là được rồi

 

[Let'GâuGâu]

Cảm ơn @lehuong1409 đã nhiệt tình hỗ trợ nhé. Chúng ta cùng thử ngồi nghĩ xem có cách nào sử dụng công thức được hay không? Hy vọng bạn sớm cho ra kết quả

mấy cái dzụ nối nhau bằng phẩy phẩy này chỉ có xài vba thôi
nếu số liệu (các cột) của bạn không nhiệu thì xài if giống như anh NDU nói

 

[leo1286]

Có 5 cột thì làm 5 cái IF nối lại với nhau là được rồi

Cảm ơn thầy đã tư vấn, đúng là cách đơn giản nhất là dùng hàm If nhưng mà vẫn mắc cái dấu "," ở cuối kết quả. Thế nên em mới đang xem có cách nào dùng công thức cho ra kết quả tối ưu hay không?

 

[lehuong1409]

Cảm ơn thầy đã tư vấn, đúng là cách đơn giản nhất là dùng hàm If nhưng mà vẫn mắc cái dấu "," ở cuối kết quả. Thế nên em mới đang xem có cách nào dùng công thức cho ra kết quả tối ưu hay không?

Chỉ ngại nó quá dài thôi . B xem file đính kèm nhé

 

[leo1286]

Cảm ơn bạn nhiều. Như vậy là bài toán đã được giải đáp

 

[tieuthubuongbinh]

Chỉ ngại nó quá dài thôi . B xem file đính kèm nhé

Giả sử số cột dài hơn lần IF có thể, thì phải làm sao ạ?

 

[lehuong1409]

Giả sử số cột dài hơn lần IF có thể, thì phải làm sao ạ?

mình thấy từ office 2007 thì k bị giới hạn 7 lần if nữa đâu
Còn về lâu dài hay nhiều cột hay ô hơn b có thể tìm hiểu thêm về hàm VBA ấy, nó cho tuỳ chọn thoải mái hơn, vùng quét cũng linh động