Hỏi nhanh - đáp nhanh về công thức (phần 1)

[lamtecco2]

Xin cho hỏi có hàm nào loại 1 ký tự bất kì trong 1 text không nhỉ ??? Cảm ơn nhiều !

 

[hoangdanh282vn]

Xin cho hỏi có hàm nào loại 1 ký tự bất kì trong 1 text không nhỉ ??? Cảm ơn nhiều !

BẠn thử nghiên cứu anh chàng này xem : Substitute(...)
Muốn thay thế bao nhiêu ký tự cũng được cả

 

[chihuahua]

Xin các anh chị Cho em hỏi hàm định mức doanh số bán hàng.

Em có file doanh số bán hàng cấn các anh chị giúp đở.
Em xin trinh bày diều kiện như sau:
Ví dụ: Nhân viên bán hàng Lệ Thủy bán một đơn hàng trị giá là 5.000.000đ.Tiền nhận cọc là: 1.000.000đ , và tiền còn lại là : 4.000.000đ.
Thì nhân viên Lệ Thủy đó được hưởng theo quy dịnh doanh số như sau:
Nhận tiền cọc được ăn 180% số tiến nhận cọc tức là 1.000.000đ là được :1.800.000đ
Số tiền còn lại được ăn 100% số tiền còn lại tức là 4.000.000đ là được :4.000.000đ,với ĐK là phải lấy tiền đúng vào ngày giao hàng (vd: 18/10/08)mới được hưởng số % như vậy.
Nếu nhận tiền sau ngày giao hàng 1 ngày thì số % chỉ được hưởng là 90% số tiền còn lại tức là: 4.000.000đ là được : 3.600.000đ. cứ như vậy mà giảm dần % xuống.
Nếu nhân viên bàn hàng không nhận được số tiền còn lại ở mức 0% thì không được ăn % trăm nào cả. Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X0.5%= 25.000đ.
Nếu không nhận được tiền ở mức sau 0% 1 ngày sẽ bị trứ âm là: -10%,sau 2 ngày là: -20% cứ như vậy nhân viên đó bị trừ dần.
Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X10% trừ . thì nhân viên đó bị âm là: 500.000đ. hoặc bị âm là : 1.000.000đ là tương ứng với -20%.
Nhưng xin anh chị có thể làm giúp em ở ngay cột J và cột T.
Xin Chân Thành Cám Ơn Các Anh Chị.
Mong anh chỉ sớm giúp em......

 

[BNTT]

Em có file doanh số bán hàng cấn các anh chị giúp đở.
Em xin trinh bày diều kiện như sau:
Ví dụ: Nhân viên bán hàng Lệ Thủy bán một đơn hàng trị giá là 5.000.000đ.Tiền nhận cọc là: 1.000.000đ , và tiền còn lại là : 4.000.000đ.
Thì nhân viên Lệ Thủy đó được hưởng theo quy dịnh doanh số như sau:
Nhận tiền cọc được ăn 180% số tiến nhận cọc tức là 1.000.000đ là được :1.800.000đ
Số tiền còn lại được ăn 100% số tiền còn lại tức là 4.000.000đ là được :4.000.000đ,với ĐK là phải lấy tiền đúng vào ngày giao hàng (vd: 18/10/08)mới được hưởng số % như vậy.
Nếu nhận tiền sau ngày giao hàng 1 ngày thì số % chỉ được hưởng là 90% số tiền còn lại tức là: 4.000.000đ là được : 3.600.000đ. cứ như vậy mà giảm dần % xuống.
Nếu nhân viên bàn hàng không nhận được số tiền còn lại ở mức 0% thì không được ăn % trăm nào cả. Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X0.5%= 25.000đ.
Nếu không nhận được tiền ở mức sau 0% 1 ngày sẽ bị trứ âm là: -10%,sau 2 ngày là: -20% cứ như vậy nhân viên đó bị trừ dần.
Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X10% trừ . thì nhân viên đó bị âm là: 500.000đ. hoặc bị âm là : 1.000.000đ là tương ứng với -20%.
Nhưng xin anh chị có thể làm giúp em ở ngay cột J và cột T.
Xin Chân Thành Cám Ơn Các Anh Chị.
Mong anh chỉ sớm giúp em......

Tôi nghĩ rằng đâu có cần thiết phải chia chi tiết số ngày ra như vậy?
Nếu theo như bạn trình bày: Số % được hưởng trên số tiền còn lại sau khi đã trừ tiền đặt cọc giảm dần theo số ngày, mỗi ngày giảm 10%, tính từ ngày giao hàng, thì chỉ cần một công thức duy nhất tại cột I mà thôi:

[Doanh số] = [Số tiền còn lại] * (100 - ([ngày nhận tiền]-[ngày giao hàng])*10)%

[I14] =G14*(100-(B14-C14)*10)%​

Để tính doanh số thực, theo như trình bày của bạn, thì tôi thấy có chỗ vô lý.
Bạn nói rằng:

Nếu nhận tiền sau ngày giao hàng 1 ngày thì số % chỉ được hưởng là 90% số tiền còn lại tức là: 4.000.000đ là được : 3.600.000đ. cứ như vậy mà giảm dần % xuống.

Và ở dưới thì bạn lại nói:

Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X0.5%= 25.000đ.

Cái con số 4.000.000 trong công thức ở trên ở đâu mà ra vậy?

Theo tôi hiểu thì phải là như vầy:

• Đúng ngày (ngày nhận tiền = ngày giao hàng)
  = (1.800.000 + 4.000.000*100%) x 0.5% = (1.800.000 + 4.000.000) x 0.5% = 29.000 (chứ không phải 25.000 đâu bạn)
  
  
• Trễ 1 ngày: = (1.800.000 + 4.000.000*90%) x 0.5% = (1.800.000 + 3.600.000) x 0.5%
  
  
• Trễ 2 ngày: = (1.800.000 + 4.000.000*80%) x 0.5% = (1.800.000 + 3.200.000) x 0.5%
  
  
• v.v... (nghĩa là con số tiền phải từ từ giảm xuống)

Như vậy mới đúng chứ? Ở chỗ mà doanh số âm cũng vậy, chỉ khác là nhân với 10% (?). Tại sao trong công thức của bạn vẫn có con số 4.000.000 dính ở trong đó?

Cũng có thể là tôi hiểu sai ý của bạn.

Bạn giải thích lại cách tính doanh số thực đi, rồi tôi mới làm tiếp được.

 

[BNTT]

Thêm một chút nữa:
Bạn giải thích dùm chỗ này:

Nếu không nhận được tiền ở mức sau 0% 1 ngày sẽ bị trứ âm là: -10%,sau 2 ngày là: -20% cứ như vậy nhân viên đó bị trừ dần.
Thế thì số tiền % được tính như sau: (1.800.000+4.000.000)X10% trừ . thì nhân viên đó bị âm là: 500.000đ. hoặc bị âm là : 1.000.000đ là tương ứng với -20%.

Bạn tính như thế nào để ra được 500.000 (10%), hoặc 1.000.000 (20%) vậy ?

 

[Tiger62]

Xin cho hỏi có hàm nào loại 1 ký tự bất kì trong 1 text không nhỉ ??? Cảm ơn nhiều !

Bạn dùng công thức này xem thế nào
Bn=REPLACE(REPT(An,1),FIND("zX",REPT(An,1)),z,"")
Bạn có thể loại bỏ z ký tự ở một vị trí bất kỳ trong chuỗi ở ô An-> trong cả 1 cột.

À quên, nói thêm, nếu trong An có nhiều chuỗi cần xóa "zX" giống nhau thì lại tiếp tục coppy tiếp công thức sang ô Cn, Dn,... hơi thủ công.

Nếu có vài chuỗi "zX" thì có thể lồng công thức :
A1=4ab1ab23
B1=REPLACE(REPT(REPLACE(REPT(A1,1),FIND("ab",REPT(A1,1)),2,""),1),FIND("ab",REPT(REPLACE(REPT(A1,1),FIND("ab",REPT(A1,1)),2,""),1)),2,"")=4123

 

[ThuNghi]

Bạn dùng công thức này xem thế nào
Bn=REPLACE(REPT(An,1),FIND("zX",REPT(An,1)),z,"")
Bạn có thể loại bỏ z ký tự ở một vị trí bất kỳ trong chuỗi ở ô An-> trong cả 1 cột.

À quên, nói thêm, nếu trong An có nhiều chuỗi cần xóa "zX" giống nhau thì lại tiếp tục coppy tiếp công thức sang ô Cn, Dn,... hơi thủ công.

Nếu có vài chuỗi "zX" thì có thể lồng công thức :
A1=4ab1ab23
B1=REPLACE(REPT(REPLACE(REPT(A1,1),FIND("ab",REPT(A1,1)),2,""),1),FIND("ab",REPT(REPLACE(REPT(A1,1),FIND("ab",REPT(A1,1)),2,""),1)),2,"")=4123

Sao Bác không dùng hàm substitute() nó sẽ thay thế tất cả An thành ""

 

[SonMaritime]

Tôi nghĩ rằng đâu có cần thiết phải chia chi tiết số ngày ra như vậy?
Nếu theo như bạn trình bày: Số % được hưởng trên số tiền còn lại sau khi đã trừ tiền đặt cọc giảm dần theo số ngày, mỗi ngày giảm 10%, tính từ ngày giao hàng, thì chỉ cần một công thức duy nhất tại cột I mà thôi:

[Doanh số] = [Số tiền còn lại] * (100 - ([ngày nhận tiền]-[ngày giao hàng])*10)%

[I14] =G14*(100-(B14-C14)*10)%​

Yêu cầu đúng là hơi khó hiểu, Đúng như bạn BNTT nói thì chỉ cần một công thức duy nhất cho bài toán này.
Nếu để tính đến ngày %=0 thì chỉ đơn giản là chậm 10 ngày (mỗi ngày 10%). Còn từ ngày 11 trở đi thì trừ âm mỗi ngày 10%. Công thức sẽ là:
Cột +: =IF(B14-C14<10;G14*(100-(B14-C14)*10)%;0)
Cột -: =IF(B14-C14>10;G14*(100-(B14-C14)*10)%;0)

Hoặc kết hợp cả 2 cột lại chỉ còn 1 cột duy nhất:
=(C14+10-B14)*G14*10%

Bạn thử xem đã đúng ý bạn chưa, phần đặt cọc và tính hoa hồng 5% bạn có thể tự áp dụng.
Hoặc bạn có thể nói chi tiết hơn phần tính doanh số của mình thì mọi người trên diễn đàn mới có thể giúp đúng ý bạn được

 

[vhthuy]

Minh muốn hỏi 1 công thức.

VD: Nếu đạt doanh số 100% thì =0, đạt 110%=10, đạt 120%=20. đạt 130%=30....300%=300

Nếu dùng lệnh if bình thường sẽ rất dài, liệu có lệnh điều kiện nào khác ngắn hơn không ?
Xin cảm ơn.

 

[BNTT]

Minh muốn hỏi 1 công thức.

VD: Nếu đạt doanh số 100% thì =0, đạt 110%=10, đạt 120%=20. đạt 130%=30....300%=300

Nếu dùng lệnh if bình thường sẽ rất dài, liệu có lệnh điều kiện nào khác ngắn hơn không ?
Xin cảm ơn.

Quy tắc của bạn:

100% = 100 - 100 = 0
110% = 110 - 100 = 10
120% = 120 - 100 = 20
130% = 130 - 100 = 30
v.v...
300% = 300 - 100 = 200​

Nếu theo cái quy tắc bạn đưa ra đó thì đạt 300% phải là 200, chứ không thể là 300. Bạn xem lại nhé.

Nếu đúng như tôi vừa nêu, thì:

Trường hợp doanh số không có dấu % ở sau, công thức tính là: =[Doanh số]-100

Trường hợp doanh số có dấu % ở sau, thì công thức = [Doanh số]-1 (bởi vì 100%=1, 120%=1.2, v.v...)

Chẳng cần IF gì hết.

 

[vhthuy]

Cảm ơn bạn, đúng là 200%. Còn con số thực ra là ví dụ chung thôi.

Mình làm bảng lương khoán sản phẩm.

Nếu đạt 100% thì mức thưởng =0
Đạt <=110% thì mức thưởng = Lương cố định * 10%
đạt <=120% ...............................................20%
<=300........................................................200%
Ý mình là dùng lệnh if sẽ rất dài và chỉ dùng đến 8 lệnh lồng được. Liệu có lệnh nào ngắn hơn không ? hoặc có cách khác tối ưu hơn ?

 

[bangviet]

Các bác cho em hỏi một vấn đề về làm đổi màu chữ trong excel như sau ạ!
Em có một bảng excel giờ em muốn ô bất kỳ trong bảng đấy nếu giá trị mình nhập thì ô đó tự đổi sang màu xanh còn nếu ô đó chứa công thức hoặc tham chiếu từ một bảng khác thì chữ sẽ tự động chuyển sang màu đỏ.
Bác nào biết thì chỉ em với ạ, em cám ơn trước!

 

[Po_Pikachu]

Trước tiên bạn phải quét chọn vùng cần đổi màu chữ. Rồi vào Format -> Conditional Formatting -> ở khung bên trái bạn chọn Formula Is, ở khung bên phải bạn gõ =<ô đầu tiên của vùng quét chọn><>""
Phần màu đỏ là công thức. Phần màu xanh là giá trị để bạn nhập vào!
Tiếp theo, bạn nhấn vào nút Format rồi chọn định dạng Format mà bạn cần -> Tiếp theo là nhấn nút Add để thêm 1 tính năng khác!
Ở trên mình đã chỉ bạn các bước đơn giản để bạn làm quen với lệnh này! Nếu muốn biết thêm để hoàn tất yêu cầu hai thì bạn xem link bên dưới. Sẽ có những kiến thức cần cho bạn để hoàn tất nó và học được nhiều điều hơn!
http://www.giaiphapexcel.com/forum/showthread.php?t=7883&page=2
Thân.

 

[bangviet]

Cám ơn bác Po_Pikachu nhưng ví dụ của bác chưa đáp ứng được ý của em đưa ra ạ, đường link bác gửi cho rất bổ ích nhưng chưa có phần nào giải quyết mục đích của em.
Để em giải thích qua ý của em:
Ví dụ của bác chỉ làm được một yêu cầu đó là đổi màu ô nếu ô đó có giá trị còn em hỏi có hai điều kiện (phức tạp hơn một chút) em muốn trong bảng đó nếu ô nào mình đánh giá trị vào thì ô đấy tự động chuyển sang màng xanh còn nếu ô đó mình tham chiếu từ một ô khác hoặc bảng khác (tức là có công thức đấy ạ) thì chữ sẽ chuyển sang màu đỏ!
Bác nào biết thì giúp em với ạ!

 

[Po_Pikachu]

Thí chẳng phải ô C8 là tham chiếu từ 1 ô khác đó sau!? Và dấu bằng kia chứng tỏ rằng nó là 1 công thức còn gì!
Bạn có thể viết 1 vài số liệu ở sheet khác rồi viết 1 hàm sum trong ô C10 lấy dữ liệu từ ô ở sheet khác xem chúng có đổi màu không!
Vấn đề định dạng màu sắc thì bạn đã biết cách làm rồi đúng không!
Còn về cách để xác định ô đó có công thức thì nó nằm mục #11 cũa link trên ngay ở trang đầu luôn để bạn đọc rồi!
Nếu bạn nhập 1 giá trị vào trong khoảng từ C7:C12 sẽ thấy có sự thay đổi màu!
Nếu nhập công thức trong khoảng đó thì sẽ có màu khác!

Đây chỉ là ví dụ để bạn biết cách làm thôi! Nếu muốn làm đúng theo ý thì hoặc bạn làm hoặc bạn gửi file để mình làm. Vậy thôi!
Thân.

 

[SonMaritime]

Cảm ơn bạn, đúng là 200%. Còn con số thực ra là ví dụ chung thôi.

Mình làm bảng lương khoán sản phẩm.

Nếu đạt 100% thì mức thưởng =0
Đạt <=110% thì mức thưởng = Lương cố định * 10%
đạt <=120% ...............................................20%
<=300........................................................200%
Ý mình là dùng lệnh if sẽ rất dài và chỉ dùng đến 8 lệnh lồng được. Liệu có lệnh nào ngắn hơn không ? hoặc có cách khác tối ưu hơn ?

Bạn dùng công thức này xem:
Giả sử Doanh số đạt bạn đặt ở cell A1
LCD: Lương cố định

Thuong=LCD*SUMPRODUCT((A1>90%+10%*ROW($1:$20))*10%)

Công thức này nếu doanh số đạt trên 300% thì thưởng vẫn chỉ là 200%. Không biết đã đúng ý bạn chưa.

 

[vhthuy]

Mình k gửi file lên được. Bạn xem giúp mình .

http://img407.imageshack.us/my.php?image=luongkdnc2.jpg

 

[BNTT]

Mình k gửi file lên được. Bạn xem giúp mình .

http://img407.imageshack.us/my.php?image=luongkdnc2.jpg

Nếu như quy tắc để tính % thưởng của bạn giống như bài đã hỏi ở trên (và tôi đã trả lời), thì công thức trong cột D là:

[D4] = C4*(B4-1)​

 

[chihuahua]

Cám ơn bạn SonMaritime đã giúp mình công thức dưới.
Cột +: =IF(B14-C14<10;G14*(100-(B14-C14)*10)%;0)
Cột -: =IF(B14-C14>10;G14*(100-(B14-C14)*10)%;0)

Hoặc kết hợp cả 2 cột lại chỉ còn 1 cột duy nhất:
=(C14+10-B14)*G14*10%

Nhưng mình sẽ trình bày lại đk như sau:
Nếu B14=C14 thì I14*10% . Còn B14>C14 là 1 . Thì I14*0.9%,Còn B14>C14 là 2 . Thì I14*0.8%,Còn B14>C14 là 3 . Thì I14*0.7% NHư vậy cho đến I14*0.1%.
Còn số âm là:
Nếu B14>C14 là 10 ngày Thì I14*(-10%)
Còn B14>C14 là 11 ngày Thì I14*(-20%)
Còn B14>C14 là 12 ngày Thì I14*(-30%).
Mong bạn giúp đở cho.
Mính thành thật cám ơn.

 

[bangviet]

Đổi màu tự động!

Thí chẳng phải ô C8 là tham chiếu từ 1 ô khác đó sau!? Và dấu bằng kia chứng tỏ rằng nó là 1 công thức còn gì!
Bạn có thể viết 1 vài số liệu ở sheet khác rồi viết 1 hàm sum trong ô C10 lấy dữ liệu từ ô ở sheet khác xem chúng có đổi màu không!
Vấn đề định dạng màu sắc thì bạn đã biết cách làm rồi đúng không!
Còn về cách để xác định ô đó có công thức thì nó nằm mục #11 cũa link trên ngay ở trang đầu luôn để bạn đọc rồi!
Nếu bạn nhập 1 giá trị vào trong khoảng từ C7:C12 sẽ thấy có sự thay đổi màu!
Nếu nhập công thức trong khoảng đó thì sẽ có màu khác!

Đây chỉ là ví dụ để bạn biết cách làm thôi! Nếu muốn làm đúng theo ý thì hoặc bạn làm hoặc bạn gửi file để mình làm. Vậy thôi!
Thân.

Em gửi file đính kèm và yêu cầu nhờ bác làm giúp em ạ, rất mong bác chỉ giáo thêm!