Nhờ lập công thức giúp ?

[Chuotdong]

Tôi có bài toán sau cần lập công thức để giải, các bạn xem có cách lập công thức một cách đơn giản như thế nào không, càng lập công thức càng thấy phức tạp :

 

[Son2006]

Theo tôi, sử dụng name là cách thức làm cho các bài toán đòi hỏi nhiều điều kiện trở thành đơn giản. Chỉ cần hơi ngăn nắp 1 tí là làm được. Bạn thử xem file đính kèm. Dòng thứ 2 không nhập liệu. Nếu bạn không muốn vậy có thể sửa lại theo công thức cũ của bạn, còn các dòng khác thì chỉ cần diền công thức "=CellPhanBo" vào là ra kết quả

 

[Son2006]

Nếu bạn không thích dùng name vì nó không rõ ràng thì có thể copy 7 páte công thức sau vào ô D3, sau do fill down and right tùy ý
=IF(D$1-SUM(D$22)>$B3,IF($B3=0,D$1-SUM(D$22),$B3),D$1-SUM(D$22))
Để tìm sum phần trên ô hiện hành, không cần dùng offset làm gì cho mệt, chỉ cần không cố định phần dưới của range là fill qua fill lại được hết

 

[Mr Okebab]

Tôi có bài toán sau cần lập công thức để giải, các bạn xem có cách lập công thức một cách đơn giản như thế nào không, càng lập công thức càng thấy phức tạp :

Không biết có đúng ý của bạn không. Bạn xem qua nhé.
Chỉ cần thêm hàng thứ hai, lập công thức tại ô D3. Tất cả các ô khác kéo qua và xuống. Thế là xong.

Thân!

 

[Chuotdong]

Cám ơn các bạn Sơn và Hiếu, cách của các bạn đơn giản ngoài mong đợi tuy rằng phải chèn thêm 1 dòng phụ cho công thức dễ paste


Nhân đây mình còn 1 bài toán sau cũng liên quan đến lập công thức ở cột D, các bạn xem giúp có cách gì giải giùm:
(chỉ cần không được chèn thêm cột phụ nào nữa là được):

 

[Son2006]

=IF(D$1-SUM(D$22)>$B3,IF($B3=0,D$1-SUM(D$22),$B3),D$1-SUM(D$22))
Công thức tôi nêu trên chỉ cần paste vào D3 va fill như đã nói, còn dòng trên giữ nguyên như bạn đã làm. Vậy là không cần thêm dòng phụ nào hết

Bài 2, sau khi mark M ở D9, xet sum(C10) và sum(C10:C11), nhận thấy sum(C10)=90 gần 100 hơn sum(C10:C11)=120; Vậy mà tại sao lại mark ở D11

Không cho bất kỳ cột nào làm trung gian à? Cột B cũng không được hay sao? Vậy thì khó lắm

 

[Mr Okebab]

=IF(D$1-SUM(D$22)>$B3,IF($B3=0,D$1-SUM(D$22),$B3),D$1-SUM(D$22))
Công thức tôi nêu trên chỉ cần paste vào D3 va fill như đã nói, còn dòng trên giữ nguyên như bạn đã làm. Vậy là không cần thêm dòng phụ nào hết

Dòng phụ là dòng 2 đó bạn ạ

 

[Chuotdong]

Không cho bất kỳ cột nào làm trung gian à? Cột B cũng không được hay sao? Vậy thì khó lắm

Cột B đương nhiên phải có
Xin lỗi cột D11 nhầm, phải mark từ D10 vì =90 gần 100 hơn so với D11 =120

 

[Mr Okebab]

Nếu tạo cột phụ thì dùng công thức tốt. Nhưng nếu không có cột phụ thì chắc là được (không dùng VBA). Tuy nhiên khó khăn hơn. Để mình thử xem.

 

[Chuotdong]

Như mình đã nói cái đích cuối cùng của Bài toán của mình cần đạt được là Phân bố lại dữ liệu như ở cột E, và điều này chắc chắn phải dùng VBA rồi.

Còn việc lập công thức bằng cách tự động đánh dấu Mark như ở cột D thì mình nghĩ không cần thêm cột phụ nào nữa (ngoài cột B) là tốt nhất

 

[Chuotdong]

Tiếp tục loạt các bài toán về lập công thức trong Excel mình gửi lên đây Bài 3 về Nội suy 2 chiều (mình đã giải) các bạn xem công thức lập đã gọn tối ưu chưa, yêu cầu vẫn là ko sử dụng thêm ô trung gian

Hiện chỉ còn vấn đề xử lý "số chữ số có nghĩa" ở ô kết quả ? chỉ dùng công thức không lập VBA

 

[Son2006]

Dòng 2 trong nguyên bản cơ mà!. Giử nguyên công thức dòng đó, thêm công thức đã nêu vào D3 rồi fill

Tôi xem file congthuc21 và đã hiểu như sau:Mục dích cuối cùng là phân bố lại, còn mark là để đánh dấu những hàng cần phân bố. Đúng không? Khi đó xảy ra vấn đề: dòng 11, số 30 đáng ra phải mark vì 30 gần 100 hơn so với 30+300. Nhưng bạn không mark số 30; Ngược lại nếu mark số 30 thì thực tế ta không cần phân bố lại số 30. Vậy có nghĩa là cách đặt bài toán mark còn chưa đầy đủ

Vậy mục đích mark để làm gì?

Theo tôi nghĩ có lẽ để phân bố lại, cần mark theo kết quả cột tổng lũy tiến, cứ quá chẵn trăm (100,200,...) là phải mark chứ không cấn tính cái nào gần 100 hơn, lại càng không cần tính dến tổng bắt đầu từ ô mark phía trên... như bạn đã nêu. Nếu vậy thì công thức rất đơn giản: Copy & paste công thức sau vào D4 & filldown
=IF(ROUNDDOWN(B4/100,0)-ROUNDDOWN(B3/100,0)>0,"M","")

Có trường hợp: Nếu tổng lũy tiến chẵn trăm có cần mark không? Rõ ràng con số input đó không cần phân bố lại nên không cần mark. Vậy cần sửa lại công thức 1 tí, cái này chẳng khó

Về file congthuc3 tôi có 1 số ý kiến:
1. Tại sao lại dùng roundup?
2. Tại sao phải dùng function Value trong công thức?
3. Nếu input value bằng cận trên? Nếu input value nhỏ hơn cận dưới?
4. Nếu ma trận có kích thước vượt quá cột AB?
5. Nếu kết quả y cuối cùng của ma trận không bằng 0.004?
Những vấn đề trên cũng đơn giản, nên điều chỉnh để công thức chính xác hơn

Tìm số chữ số thập phân có thể theo công thức sau:
=LEN((TEXT(CellX,"#.#################")))-SEARCH(".",(TEXT(CellX,"#.#################")))
số # có thể dến 30 (Theo như tôi thấy trong phần format cell).
Giá trị mới tìm được sẽ được viết theo mấy chữ số thập phân? Theo tôi thì cần theo số có chữ số thập phân ít hơn. Vì sao? Ví dụ tính tổng 2 phép đo. Phép thứ nhất được 10 m (sai số 1 m); Phép thứ 2 được 20.05m(Sai số 0.01m). Vậy tổng sẽ viết là 30m hay 30.05m?

Công thức biến thành text có hạn chế: Nếu số CellX=15.00 thì bản thân TEXT(CellX,"#.#################") cũng chỉ cho ra là 15.; có nghĩa là phần thập phân bằng 0. Khi đó nếu viết giá trị tìm được theo cách viết này thì quả là sai lầm lớn

 

[Chuotdong]

Tôi xem file congthuc21 và đã hiểu như sau:Mục dích cuối cùng là phân bố lại, còn mark là để đánh dấu những hàng cần phân bố. Đúng không? Khi đó xảy ra vấn đề: dòng 11, số 30 đáng ra phải mark vì 30 gần 100 hơn so với 30+300. Nhưng bạn không mark số 30; Ngược lại nếu mark số 30 thì thực tế ta không cần phân bố lại số 30. Vậy có nghĩa là cách đặt bài toán mark còn chưa đầy đủ

Trước hết mình phải khẳng định lại 2 bài toán là hoàn toàn khác nhau, việc Mark là 1 việc cần phải thực hiện còn việc Phân bố lại là việc khác (nhưng nếu tận dụng được kết quả của việc Mark thì rất hay).
Qua góp ý của các bạn mình phải "Tổng quát hóa" bài toán Mark của mình như sau (rất khó để diễn tả bằng lời nên mình đã đánh "M" bằng tay để các bạn hiểu giúp nhưng vẫn bị nhầm: như bạn Sơn phát hiện ra số 30 vẫn phải Mark):

Ví dụ cụ thể trên khi Giá trị cuối cùng cộng dồn là =770 thì như vậy bạn chỉ được 770/100 =7,7 qui tròn= 8 lần "M" (sai số cuối cùng cho phép không quá +1 lần Mark dư) sao cho đều nhất ngay khi có thể (có nghĩa: sai số cho phép sau 1 lần Mark là nhỏ nhất). Nhưng trong thực tế mình đã phát hiện ra sẽ có lúc Bài toán Mark không giải được(nên có sai số): khi mà số liệu đầu vào liên tục vượt quá xa giá trị tiêu chuẩn (100), trong ví dụ của mình là 300. Trong thực tế mình đã và đang xử lý thủ công chỗ này, nghĩa là không nhập vào 300 mà sẽ nhập thành 3 lần 100

Nên mình mới nghĩ rằng bài toán sẽ phải giải triệt để hơn bằng cách thực hiện Phân bố lại ở cột E.

Còn công việc(bài toán) mình cần xử lý hiện tại chỉ cần Mark tự động cho cột D là được rồi.

Về file congthuc3 tôi có 1 số ý kiến:
1. Tại sao lại dùng roundup?
2. Tại sao phải dùng function Value trong công thức?
3. Nếu input value bằng cận trên? Nếu input value nhỏ hơn cận dưới?
4. Nếu ma trận có kích thước vượt quá cột AB?
5. Nếu kết quả y cuối cùng của ma trận không bằng 0.004?
Những vấn đề trên cũng đơn giản, nên điều chỉnh để công thức chính xác hơn

1) có lẽ nếu không round thì vấn đề "số chữ số có nghĩa sau dấu phẩy" sẽ tự giải quyết được
2) xem giải thich ở câu 3)
3) Nếu input bằng hoặc lớn hơn cận trên sẽ tùy bài toán thực tế: hoặc lấy bằng giá trị nội suy cận trên lớn nhất hoặc có cách xử lý riêng(nên mình đặt sẵn Value để có thể them 1 ghi chú "Vượt quá giới hạn !" chẳng hạn)
Giá trị input trong thực tế luôn > 0 nên mình đã đặt cận dưới =0
4) Cái này mình cũng lưỡng lự, vì sợ lấn dữ liệu khác cũng sử dụng trên sheet này.
5) xem giải thích ở câu 3)
Cám ơn bạn, có gì bạn cứ trao đổi vì đây là những bài toán thực tế nên có những phạm vi riêng, nhưng qua đó tổng quát lên thì rất bổ ích.

Còn về lập công thức mình luôn muốn nó có độ linh động cao tránh mắc lỗi khi bổ sung dữ liệu (trường hợp mình ko dùng SUM mà dùng OFFSET trong congthuc1 là một ví dự nhỏ mình học được)

Congthuc3 này cũng là mở đầu cho 1 bài toán mình đã gặp và đã nhờ xử lý trong VBA đã thành công: tình huống là một số thiết bị yêu cầu nhập số các dữ liệu đầu vào là cố định (Ví dụ chỉ cho phép có đúng 500 số liệu đầu vào thì máy mới xử lý) trong khi số liệu của bạn lúc thì chỉ có 222 chẳng hạn lúc lại có tới 700 .v.v... biết rằng các số liệu có quan hệ tuyến tính với nhau; như vậy các bạn sẽ phải xử lý sao cho ra đúng 500 data input mà thôi ....

Có trường hợp: Nếu tổng lũy tiến chẵn trăm có cần mark không? Rõ ràng con số input đó không cần phân bố lại nên không cần mark. Vậy cần sửa lại công thức 1 tí, cái này chẳng khó

Chắc chắn phải Mark vì đây là mục tiêu lý tưởng của Bài toán. Nghĩ là phải thực hiện Phân bố lại cũng chỉ vì điều này ! và lý do số nhập vào lớn quá so với tiêu chuẩn (100) đã giải thích ở trên

Theo tôi nghĩ có lẽ để phân bố lại, cần mark theo kết quả cột tổng lũy tiến, cứ quá chẵn trăm (100,200,...) là phải mark chứ không cấn tính cái nào gần 100 hơn, lại càng không cần tính dến tổng bắt đầu từ ô mark phía trên... như bạn đã nêu. Nếu vậy thì công thức rất đơn giản: Copy & paste công thức sau vào D4 & filldown
=IF(ROUNDDOWN(B4/100,0)-ROUNDDOWN(B3/100,0)>0,"M","")

Công thức của bạn vẫn chưa giải quyết được:

Đó là chưa kể đến yêu cầu tổng quát mình đã nêu trên, cụ thể là nếu ngay từ đầu ô C4 đã có giá trị = 300 chẳng hạn thì ngay lập tức ô kế tiếp D5, D6 phải được Mark ngay để tiêu thụ hết số "M" bị dư ra mà chưa cần quan tâm đến giá trị các ô C5, C6 bằng bao nhiêu !

Thực tế mình vẫn phải xử lý bằng tay chỗ này hơn nữa số liệu thực tế mình gặp có đến vài chục nghìn và rất may là đều nhỏ hơn nhiều so với tiêu chuẩn; tuy nhiên nếu tiêu chuẩn là một số không tròn trịa (như giả thiết =100) thì các bạn thử tưởng tượng sẽ không thể làm bằng tay mà không bị sai sót được

Giá trị mới tìm được sẽ được viết theo mấy chữ số thập phân? Theo tôi thì cần theo số có chữ số thập phân ít hơn. Vì sao? Ví dụ tính tổng 2 phép đo. Phép thứ nhất được 10 m (sai số 1 m); Phép thứ 2 được 20.05m(Sai số 0.01m). Vậy tổng sẽ viết là 30m hay 30.05m?

Cài này quả thật tôi cũng không biết mặc dù kiến thức rất cơ bản, có bạn nào đang học môn Phương pháp tính trả lời giùm.
Nhưng cứ giả thiết đi bạn, thực tế đây là nội suy tuyến tính ngoài ra còn nội suy loga .... nữa

 

[Son2006]

Tôi đã đọc các bài của ChuotDong. Vì bạn nói mục đích cuối cùng là phân bố lại nên khi xem dữ liệu, tôi đã hiểu rằng những chỗ mark sẽ phải phân bố lại. Mà quả thật là theo công thức tôi mark, những chỗ mark đều phải phân bố lại, còn những chỗ không mark thì không cần. (Chỉ duy nhất cần giải quyết mark hay không khi tổng lũy kế chẵn trăm. Điều này chỉ cần thống nhất cách hiểu về mark là làm được)

Nguyên văn yêu cầu của bài toán mark là ntn? Mục đích mark để làm gì?

Vấn đề round thì tôi không hiểu sao lại dùng roundup mà không dùng round; Còn khi input value bằng cận trên thì công thức hiện lỗi. Ngoài ra bạn cố định giá trị cuối cùng bằng 0.004 là chưa chính xác vì có thể giá trị cuối cùng là những con số khác trong trường hợp nội suy của ma trận khác. Do vậy trong công thức tính không nên để giá trị cụ thể 0.004 mà phải để địa chỉ của ô cuối

 

[Chuotdong]

Tôi đã đọc các bài của ChuotDong. Vì bạn nói mục đích cuối cùng là phân bố lại nên khi xem dữ liệu, tôi đã hiểu rằng những chỗ mark sẽ phải phân bố lại. Mà quả thật là theo công thức tôi mark, những chỗ mark đều phải phân bố lại, còn những chỗ không mark thì không cần. (Chỉ duy nhất cần giải quyết mark hay không khi tổng lũy kế chẵn trăm. Điều này chỉ cần thống nhất cách hiểu về mark là làm được)

Tôi đã nói rất rõ bài toán Mark rồi , còn xin lỗi đã khẳng định "mục đích cuối cùng là phân phối lại" ở trên. Đấy hoàn toàn do tôi đặt ra vì cho rằng không thể giải bài toán trong mọi trường hợp (như đã giải thích rõ ràng ở trên) nên tự đề ra hướng giải như vậy mà thôi

Vấn đề round thì tôi không hiểu sao lại dùng roundup mà không dùng round; Còn khi input value bằng cận trên thì công thức hiện lỗi. Ngoài ra bạn cố định giá trị cuối cùng bằng 0.004 là chưa chính xác vì có thể giá trị cuối cùng là những con số khác trong trường hợp nội suy của ma trận khác. Do vậy trong công thức tính không nên để giá trị cụ thể 0.004 mà phải để địa chỉ của ô cuối

Round hay roundup là do chủ ý của cá nhân tôi thôi(tôi muốn thử điều khiển "số chữ số sau dấu phẩy", như đã nói ở trên có thể bỏ đi cũng được.

2 ý kiến còn lại của bạn hoàn toàn chính xác, trường hợp bằng gía trị cận trên thì tôi cũng có cách xử lý rồi ko biết các bạn có cách nào đơn giản hơn cách của tôi thì cùng chia sẻ

Trước hết mình phải khẳng định lại 2 bài toán là hoàn toàn khác nhau, việc Mark là 1 việc cần phải thực hiện còn việc Phân bố lại là việc khác (nhưng nếu tận dụng được kết quả của việc Mark thì cang tot).
Qua góp ý của các bạn mình phải "Tổng quát hóa" bài toán Mark của mình như sau (rất khó để diễn tả bằng lời nên mình đã đánh "M" bằng tay để các bạn hiểu giúp nhưng vẫn bị nhầm: như bạn Sơn phát hiện ra số 30 vẫn phải Mark):
Ví dụ cụ thể trên khi Giá trị cuối cùng cộng dồn là =770 thì như vậy bạn chỉ được 770/100 =7,7 qui tròn= 8 lần "M" (sai số cuối cùng cho phép không quá +1 lần Mark dư) sao cho đều nhất ngay khi có thể (có nghĩa: sai số cho phép sau 1 lần Mark là nhỏ nhất). Nhưng trong thực tế mình đã phát hiện ra sẽ có lúc Bài toán Mark không giải được (nên chấp nhận tồn tại sai số): khi mà số liệu đầu vào liên tục vượt quá xa giá trị tiêu chuẩn (100), trong ví dụ của mình là 300. Trong thực tế mình đã và đang xử lý thủ công chỗ này, nghĩa là không nhập vào 300 mà sẽ nhập thành 3 lần 100

Nên mình mới nghĩ rằng bài toán sẽ phải giải triệt để hơn bằng cách thực hiện Phân bố lại ở cột E.

Còn công việc(bài toán) mình cần xử lý hiện tại chỉ cần Mark tự động cho cột D là được rồi.

Giữa Bài toán và Công việc khác nhau ở chỗ trong Bài toán cần luôn có lời giải đúng còn Công việc thì phải chấp nhận có sai số nào đó (sau đó điều chỉnh bằng tay vậy ).

Mà quả thật là theo công thức tôi mark, những chỗ mark đều phải phân bố lại, còn những chỗ không mark thì không cần.

Đúng vậy, nhưng như vậy bạn chưa giải quyết được bài toán Mark của tôi và cũng chỉ là bước chuẩn bị để giải bài toán Phân bố lại, nó ko có ý nghĩa với tôi.
Thanks

Nguyên văn yêu cầu của bài toán mark là ntn? Mục đích mark để làm gì?

Nguyên văn yêu cầu của bài toán này là định vị và phát hiện vị trí bạn ạ (mình không thể nói rõ hơn vì đã quá nhiều lời rồi): một người ở vị trí được Mark sẽ biết mình ở cách một Mark khác bao xa (sai số không quá 100/2=50m chẳng hạn) theo 1 lộ trình đã định trước.

 

[Son2006]

Bài toán lý thú đấy. Nhưng quả thật giải với công cụ excel lại không cho dùng thêm row hoặc column khác thì khá khó. Khó nhất là tìm tổng từ vị trí hiện tại tới điểm mark gần nhất phía trên.

 

[Chuotdong]

Chào các bạn, thực ra việc thêm 1 cột thì cũng không quá bó buộc tuy nhiên vì sẽ phá vỡ các cột khác trong cùng sheet nên như vậy thì lập luôn bằng VBA có hơn không ?!
Có lẽ để giải quyết các bạn thêm cột phụ cũng được (mình sẽ tự điều chỉnh các cột dữ liệu khác để có chỗ cho cột phụ)

Các bạn giúp mình càng sớm càng tốt, hiện nay nhiều dữ liệu quá mà xử lý bằng tay thì dễ sai lắm

 

[Son2006]

Tôi định lập công thức bằng VBA, trong quá trình lập tự nhiên nghĩ ra 1 giải pháp lập công thức cho ChuotDong mà không cần thêm cột. Khi đó tất cả dữ liệu đều sử lý trên cột D, chỉ có điều những dữ liệu không cần thiết (VD ô không cần mark) thì ta dùng conditional formating che đi . Ngoài cần nói thêm: Nếu không dùng name, index thì công thức sẽ cực kỳ phức tạp. Để các bạn tiện theo dõi, tôi copy các công thức của name vào worksheet. ChuotDong cần xử lý lại địa chỉ các công thức trong file của bạn.
Tôi chưa thể test nhiều. Nếu có lỗi thông báo cho tôi biết. Chỉ sợ tuần tới lại bận không thể vào mạng hoặc có thời gian suy nghĩ trả lời bạn. Happy New Year!

 

[Son2006]

- Bạn có thể thay tiêu chuẩn lọc khác mà công thức vẫn đúng
- Ô cuối cùng luôn được mark
- Ô dữ liệu quá lớn cũng được mark
Do không edit lại bài trên được nên tôi phải viết bài mới. MOD làm ơn gộp lại hộ nhé. Thanks

 

[Chuotdong]

- Bạn có thể thay tiêu chuẩn lọc khác mà công thức vẫn đúng
- Ô cuối cùng luôn được mark
- Ô dữ liệu quá lớn cũng được mark
Do không edit lại bài trên được nên tôi phải viết bài mới. MOD làm ơn gộp lại hộ nhé. Thanks

- Cảm ơn bạn Sơn nhiều. Qua cách giải của bạn chứng tỏ sức mạnh của Excel nói chung cũng như khả năng xử lý các số liệu dạng Matrix là không giới hạn Công việc của tôi ứng dụng BÀI TOÁN này đã phải làm xong trước Tết bằng tay rồi, tuy nhiên mình sẽ kiểm tra và ứng dụng vào các số liệu phải xử lý, có vấn đề gì sẽ báo cho bạn.
Hy vọng ra Tết ngày rộng tháng dài sẽ được xem cách lập VBA của bạn, vì mình mong muốn giải quyết triệt để bài toán: ô dữ liệu lớn quá lớn có thể được Mark "hơn 1 lần" và sắp lại dữ liệu sao cho bằng đúng Tiêu chuẩn luôn, chứ ko phải chỉ sát gần tiêu chuẩn nữa.
Mình cũng hy vọng qua việc giải bài toán này sẽ là hướng gợi mở để xử lý các vấn đề tương tự khác một cách nhanh chóng và đơn giản trên Excel

Năm mới chúc bạn Sơn và các bạn ngày một thành công trên con đường của mình