Hàm làm tròn số trong Excel

ndu96081631 · 19/9/08

BNTT đã viết:
@Chuột đồng:

Không phải tôi làm khó bạn. Nhưng bạn có thể nói rõ hơn được không?

Ở đây thì bạn nói:

11=10, 338=337 nghĩa làm làm tròn xuống

Rồi ở đây thì bạn lại nói:

Nghìa là 14=15, 16=17, là làm tròn lên

Vậy khi nào thì lên, khi nào thì xuống?

Bạn phải nói cho rõ nguyên tắc làm tròn, thì mới làm công thức được, hoặc viết VBA được...

Mở được GPE ra đã toát mồ hôi (chậm quá sức), down được bài của bạn xong... ướt cả áo, vì suy nghĩ!

Tôi gửi lại cái bảng tính của bạn. Tôi vẫn chịu thua. Không biết có ai hiểu được, nói giúp, cho tôi hiểu với:

Tớ cũng thế! Đọc 1 hồi "nổi khùng" luôn! LÊN LÊN XUỐNG XUỐNG... tùy điều kiện...
Đuối quá đi mất
Nói thật, tôi không cố ý nói bất cứ ai... nhưng mà khi ta đưa câu hỏi lên cũng nên nói cho rõ ràng 1 chút (hãy đặt mình vào vai trò người đọc)... Hic... Hic...
Đọc lần đầu tưởng đã hiểu... Đọc thêm phát nữa thấy.. hình như chưa hiểu đúng ý tác giã... Hỏi lại cái.. rồi kiên nhẩn đọc thêm phát nữa thấy... NGU luôn
Có phải mất công cho cả đôi bên không?

huuthang_bd · 19/9/08

tigertiger đã viết:
rất hay, nhưng bạn nên thêm tiêu chí là nếu ở giữa như 16.0 thì làm tròn về đâu 15 hay 17 tức là ưu tiên về dưới hay trên , là có thể làm được

các làm là kiểm tra gần mốc nào thì ta cộng / trừ -> rồi dùng hàm ROUND là được,

Trong trường này bạn không thể dùng hàm Round để làm tròn được vì hàm Round chỉ dùng để làm tròn số với bước nhảy 0.001, 0.01, 0.1, 1, 10, 100... Nếu bước nhảy khác dù cộng trừ thế nào bạn cũng không dùng hàm Round đc. Ví dụ làm tròn theo 2, 4, 6, 8... Bạn không thể dùng hàm Round mà phải dùng Ceiling (nếu làm tròn lên), và Floor (nếu làm tròn xuống).

Chuotdong đã viết:
Nếu ở giữa thì làm tròn lên như quy tắc round thông thường thôi: Ví dụ bạn có số 1,255551234 mà thầy giáo toán yêu cầu bạn làm tròn đến 3 chữ số thập phân thì sẽ lấy = 1,256 có phải ko vậy.

Chú ý khoảng giữa các mốc sẽ có thể không đều đâu nhé, có nghĩa là giải mốc có thể là 0; 3; 5,2; 7,85

Còn thực tế mình cần random việc chọn làm tròn lên hay xuống, để làm giả số liệu đo ấy mà

Hàm làm tròn hình như chỉ hỗ trợ khi bước nhảy đều. Nếu các mốc làm tròn của bạn không theo quy luật nào hết như: 0; 3; 5,2; 7,85 thì không áp dụng các hàm làm tròn được. Trong trường hợp của bạn mình đề xuất một giải pháp như sau:
Bạn liệt kê tất cả các mốc làm tròn trong một cột theo thứ tự tăng dần. Copy thành ba cột kế bên nhau. Hai cột đầu ngang hàng nhau và cột thứ ba chếch lên một dòng (dùng để lấy làm tròn lên xuống một cách ngẫu nhiên). Sau đó dùng hàm Vlookup dò tìm tương đối kết hợp với hàm RANDBETWEEN để lấy giá trị ngẫu nhiên. Ví dụ cụ thể như sau:

Mã:

B1=Vlookup(A1,$D$1:$F$10,RANDBETWEEN(2,3),1)

A1 là số cần làm tròn
$D$1:$F$10 là vùng dữ liệu chứa các mốc làm tròn như mình đã trình bày ở trên
RANDBETWEEN(2,3) là hàm dùng để random việc chọn cột trả trị theo yêu cầu của bạn Chuotdong
1 là dò tìm tương đối.
Cách này có lẽ hơi rườm rà nhưng mình nghĩ nó sẽ thực hiện đúng yêu cầu của tác giả. Ai có cách khác hay hơn thì post lên cho mọi người học hỏi nha.

Chuotdong · 20/9/08

@BNTT:
Tôi sẽ giải thích cho bạn ngay:

72,1 tại sao lại không làm tròn lên 72 ư, vậy các mốc đặt ra để làm gì ạ ? Các mốc gần 72,1 nhất là 70 và 73

72,1 - 70 và 73- 72,1 cái nào nhỏ hơn thì làm tròn về phía đó tức là làm tròn thành 73.

Trường hợp tôi nói làm tròn lên khi mà giá trị nằm đúng giữa các mốc ví dụ 71,5 thì làm tròn lên 73 hay xuống 70, thì tôi nói là chọn làm tròn lên trong trường hợp này

Như vậy đang xét các mốc như sau 0;3;5;7;10.

16,6 làm tròn thành 15,2 trong file Excel của tôi là tôi vội nên viết nhầm lúc sửa từ mốc lẻ sang (rất xin lỗi) phải là 17.

BNTT · 20/9/08

OK. Ít ra thì bạn cũng phải nói rõ như vậy (tuy là cũng phải động não một chút).

Sau khi đọc đi đọc lại bài của bạn, tôi hiểu được như sau:

Với các mốc làm tròn 0 - 3 - 5 - 7 (có lẽ không có số 10 đâu, phải không bạn, vì 10 thì giống 0 rồi), với các con số, sau khi "làm tròn", sẽ có con số tận cùng là một trong bốn cái mốc trên?

Có nghĩa là, ví dụ với các con số từ 10.00 đến 19.99, sau khi "làm tròn", sẽ không bao giờ có 11, 12, 14, 16, 18, 19 ? Mà chỉ có 10, 13, 15, và 17 ?

Và nữa:
Nếu con số cần làm tròn, lớn hơn hay bằng điểm giữa của 2 "mốc", thì "làm tròn lên" cho bằng mốc trên? Ngược lại thì "làm tròn xuống" cho bằng mốc dưới?

Tôi làm thử, theo cái hiểu của tôi, bạn cho ý kiến nha:

| A

| B

|
1

| Số cho trước

| Số đã làm tròn theo mốc 0, 3, 5, 7

|
2

|

17.9

|

17

|
3

|

18.51

|

20

|
4

|

18.56

|

20

|
5

|

11.44

|

10

|
6

|

16.58

|

17

|
7

|

19.53

|

20

|
8

|

19.5

|

20

|
9

|

14.26

|

15

|
10

|

19.26

|

20

|
11

|

18.37

|

17

|
12

|

13.59

|

13

|
13

|

13.77

|

13

|
14

|

15.48

|

15

|
15

|

12.1

|

13

|
16

|

13.65

|

13

|
17

|

13.6

|

13

|
18

|

19.76

|

20

|
19

|

14.15

|

15

|
20

|

13.04

|

13

|
21

|

13.71

|

13

|
22

|

14.18

|

15

|
23

|

19.89

|

20

|
24

|

11.02

|

10

|
25

|

10.39

|

10

|
26

|

10.58

|

10

|
27

|

17.21

|

17

|
28

|

10.47

|

10

|

Có đúng với ý bạn không ?

Chuotdong · 20/9/08

Đúng rồi bạn ạ. Bạn có thấy nó là sự mở rộng của hàm ROUND() không ? nhưng dĩ nhiên ko thể dùng hàm round() để xử lý được.

Tôi nghĩ giá trị các mốc (vì hữu hạn) đưa vào list là sử lý được, còn chắc dùng công thức là ổn chứ VBA làm gì, trừ khi cần random kết quả (cái này sẽ trao đổi với các bạn sau).

Sao trang web dạo này vào khó thế, muốn giải thích bạn mà vào mãi mới được.

ndu96081631 · 20/9/08

Tạm thời làm thử 1 đoạn (dưới), còn đoạn trên vẩn chưa hiểu lắm

Chuotdong · 20/9/08

ndu96081631 đã viết:
Tạm thời làm thử 1 đoạn (dưới), còn đoạn trên vẩn chưa hiểu lắm

Bạn cứ nói chỗ nào chưa hiểu. Tôi cũng chỉ nghĩ nó là sự mở rộng của hàm làm tròn thôi: vì thấy cú pháp là ROUND(số cần làm tròn; số thập phân cần làm tròn), nghĩa là hàm round() thì các mốc (thực ra chỉ có 2 mốc) của nó là 0 và 5 hoặc 0 và 0,5 hoặc 0 và 0,05 .v.v...

Đang xem cách bạn giải nhưng cũng chưa hiểu, có gì sẽ hỏi bạn thêm.

cadafi · 20/9/08

Cột A chứa dữ liệu gốc, Cột B bạn thử công thức này:

B2=INT(A2)-RIGHT(INT(A2),1)+CHOOSE(RIGHT(INT(A2),1)+1,0,0,3,3,5,5,7,7,10,10)

Sau đó kéo fill xuống.
Tuy nhiên, công thức này chưa hoàn chỉnh lắm! Các cao thủ sửa dùm nhé!

ndu96081631 · 20/9/08

Chuotdong đã viết:
Bạn cứ nói chỗ nào chưa hiểu. Tôi cũng chỉ nghĩ nó là sự mở rộng của hàm làm tròn thôi: vì thấy cú pháp là ROUND(số cần làm tròn; số thập phân cần làm tròn), nghĩa là hàm round() thì các mốc (thực ra chỉ có 2 mốc) của nó là 0 và 5 hoặc 0 và 0,5 hoặc 0 và 0,05 .v.v...

Đang xem cách bạn giải nhưng cũng chưa hiểu, có gì sẽ hỏi bạn thêm.

Cách của tôi không dùng ROUND
- Đầu tiên tôi đặt name cho Mốc
- Tách số từ hàng đơn vị trở về sau (ví dụ số 16.8 thì sẽ tách lấy số 6.8) để làm Mẩu so sánh
- Dùng Mẩu này so với Mốc, bằng cách trừ Mốc cho Mẩu, kết quả nào nhỏ nhất (hàm MIN)... thì lấy đó làm vị trí xác định
... Đại khái là thế...

BNTT · 20/9/08

Chưa được, Ca_Dafi ơi.
Theo anh hiểu thì:
Ví dụ con số 18.49, do 8.49 < (7+10)/2 = 8.5, nên nó phải lấy mốc dưới, là "làm tròn xuống" í.
Có nghĩa là 18.49 = 17 chứ không phải bằng 20.

Công thức của Ca_Dafi ra kết quả bằng 20.

Có lẽ phải xem lại cái hàm CHOOSE.

BNTT · 20/9/08

ndu96081631 đã viết:
Cách của tôi không dùng ROUND
- Đầu tiên tôi đặt name cho Mốc
- Tách số từ hàng đơn vị trở về sau (ví dụ số 16.8 thì sẽ tách lấy số 6.8) để làm Mẩu so sánh
- Dùng Mẩu này so với Mốc, bằng cách trừ Mốc cho Mẩu, kết quả nào nhỏ nhất (hàm MIN)... thì lấy đó làm vị trí xác định
... Đại khái là thế...

Em cũng nghĩ như anh.
Đây là một bài toán khá hóc búa.
Do các mốc là 0,3,5,7, trong khi mình lại phải đi so sánh với 10,13,15,17,20,23,27,...,90,93,95,97....890,893,895,897, v.v...
Cho nên phải tách cho bằng được con số nguyên cuối cùng cộng với hai con số lẻ sau dấu thập phân, thì mới làm tiếp được.

Có lẽ chúng ta phải làm từ từ, từng bước 1.
Em xin đề nghị công thức này, để tách ra con số có dạng 0.00:

| A

| B

|
1

| Số cho trước

| =IF(A2<10,A1,A1-10*LEFT(A1,LEN(INT(A1))-1))

|
2

|

76.39

|

6.39

|
3

|

25.76

|

5.76

|
4

|

17.88

|

7.88

|
5

|

70.71

|

0.71

|
6

|

52.05

|

2.05

|
7

|

92.48

|

2.48

|
8

|

49.49

|

9.49

|
9

|

42.29

|

2.29

|
10

|

696.04

|

6.04

|
11

|

721.96

|

1.96

|
12

|

765.21

|

5.21

|
13

|

625.92

|

5.92

|
14

|

272.14

|

2.14

|
15

|

129.21

|

9.21

|
16

|

468.36

|

8.36

|
17

|

926.47

|

6.47

|
18

|

981.03

|

1.03

|
19

|

809.09

|

9.09

|
20

|

138.25

|

8.25

|
21

|

2469.99

|

9.99

|
22

|

9281.2

|

1.20

|
23

|

9510.14

|

0.14

|
24

|

5479.43

|

9.43

|
25

|

195.97

|

5.97

|
26

|

9041.68

|

1.68

|
27

|

6669.56

|

9.56

|
28

|

9952.5

|

2.50

|

ndu96081631 · 20/9/08

BNTT đã viết:
Em cũng nghĩ như anh.
Đây là một bài toán khá hóc búa.
Do các mốc là 0,3,5,7, trong khi mình lại phải đi so sánh với 10,13,15,17,20,23,27,...,90,93,95,97....890,893,895,897, v.v...
Cho nên phải tách cho bằng được con số nguyên cuối cùng cộng với hai con số lẻ sau dấu thập phân, thì mới làm tiếp được.

Có lẽ chúng ta phải làm từ từ, từng bước 1.
Em xin đề nghị công thức này, để tách ra con số có dạng 0.00:

Thì bạn xem file tôi gữi ở trên đi... Đã làm theo hướng này đấy
Dùng mãng... Gần gần giống với bài về tham chiếu của bạn đấy... cũng lấy Mẩu so sánh với cả vùng rồi thêm vài "mẹo" nữa để lấy ra kết quả

BNTT · 20/9/08

Xong! Đúng nhưng chưa ưng ý lắm. Nhờ các bạn xem dùm.

| A

| B

| C

| D

| E

| F

| G

|
1

| Số cho trước

| {=DATA3*10+DATA2}

| Các mốc

|

| Các Name

| Công thức

| Mô tả

|
2

|

13.34

|

13

|

0

| | | | |
3

|

49.57

|

50

|

3

| |MAU|=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1,1)|Các mốc dùng để làm tròn|
4

|

96.67

|

97

|

5.2

| | | | |
5

|

47.7

|

47

|

7

|

103.38

|

103

|

10

| | | | |
7

|

24.02

|

23

|

86.33

|

87

| | | | | |
9

|

73.38

|

73

|

45.19

|

45.2

| | | | | |
11

|

363.7

|

363

|

637.94

|

637

| | | | | |
13

|

531.82

|

533

| | |MD|=VLOOKUP(DATA1,MAU,1)|Mốc dưới (nhỏ hơn DATA1)|
14

|

882.16

|

883

| | | | | |
15

|

259.11

|

260

| | |MT|=INDEX(MAU,MATCH(VLOOKUP(DATA1,MAU,1),MAU,0)+1,1)|Mốc trên (lớn hơn DATA1)|
16

|

688.97

|

690

| | | | | |
17

|

804.41

|

805.2

| | | | | |
18

|

346.48

|

347

| | | | | |
19

|

383.75

|

383

| | | | | |
20

|

11.17

|

10

| | | | | |
21

|

341.24

|

340

| | | | | |
22

|

8104.69

|

8105.2

| | | | | |
23

|

9221.81

|

9223

| | | | | |
24

|

4177.96

|

4177

| | | | | |
25

|

8885.89

|

8885.2

| | | | | |
26

|

8450.1

|

8450

| | | | | |
27

|

6847.42

|

6847

| | | | | |
28

|

7148.41

|

7147

| | | | | |

Với cách này, tha hồ bổ sung thêm các mốc vào (dĩ nhiên là nằm trong khoảng từ 0 đến 10 thôi, phải xếp theo thứ tự từ nhỏ đến lớn, và bắt phải có ít nhất 2 con số: 0 và 10)

-------------------------

@Anh Ndu, tại sao em thay cái công thức trích ra một số hàng đơn vị + 2 con số lẻ của anh thay thế cho cái công thức của em (cho cái DATA1):

Công thức của anh: =RIGHT(INT(DATA),1)+MOD(DATA,1)

Công thức của em: =ROUND(IF(DATA<10,DATA,DATA-10*LEFT(DATA,LEN(INT(DATA))-1)),2)

Cả hai đều ra kết quả giống nhau.

Nhưng nếu dùng cái của anh thì DATA3 của em báo lỗi #VALUE! ?

(DATA3) =LEFT(DATA,LEN(DATA)-LEN(DATA1))

Chuotdong · 20/9/08

BNTT đã viết:
Em cũng nghĩ như anh.
Đây là một bài toán khá hóc búa.
Do các mốc là 0,3,5,7, trong khi mình lại phải đi so sánh với 10,13,15,17,20,23,27,...,90,93,95,97....890,893,895,897, v.v...
Cho nên phải tách cho bằng được con số nguyên cuối cùng cộng với hai con số lẻ sau dấu thập phân, thì mới làm tiếp được.

Có lẽ chúng ta phải làm từ từ, từng bước 1.

Đọc lại cách giải của các bạn mình thấy đúng là phức tạp không ngờ. Thực tế bài toán mình phải giải quyết không như vậy, có nghĩa là các mốc nếu chỉ là số nguyên (VDụ: 0;3;5;8 chẳng hạn) thì giá trị dữ liệu cần xử lý cũng luôn chỉ là những số nguyên !, tức là không có phần lẻ: trong dữ liệu cần phải quy tròn không có những số kiểu như 22,5; 30,62; hay 102,4 .....v....v....

Hy vọng bài toán đơn giản đi nhiều và thực tế hơn.

lypt · 20/9/08

Giả sử có 4 mốc 2, 3, 5, 7 (trong pham vi 10, có thể có mốc 0, các mốc có thể trùng, các mốc có thể lẻ thập phân)
Viết 4 mốc theo thứ tự tăng dần 2, 3, 5, 7 vào A1, B1, C1, D1
Bổ sung thêm một mốc phụ (thực chất là mốc tiếp theo) E1 = A1+10
Số trước khi làm tròn được cho trước tại F1 (và F2, F3, …)
Số sau khi làm tròn (cần tính) nằm tại G1 (và G2, G3, …). Công thức tại G1 :
=INT(F1/10)*10+IF(F1-INT(F1/10)*10<($A$1+$B$1)/2,$A$1,IF(F1-INT(F1/10)*10<($B$1+$C$1)/2,$B$1,IF(F1-INT(F1/10)*10<($C$1+$D$1)/2,$C$1,IF(F1-INT(F1/10)*10<($D$1+$E$1)/2,$D$1,$E$1))))
Copy G1 sang G2, G3, …
Công thức trên đang theo nguyên tắc làm tròn lên.
Nếu làm tròn xuống thì thêm dấu = sau các dấu <; còn nếu không muốn viết dấu = thì viết dấu > nhưng thứ tự tính ngược lại.

lypt · 22/9/08

Bài toán được giải tổng quát hơn như sau :
Chu kỳ không nhất thiết là 10 mà có thể lớn hơn (hàng trăm, ngàn, …) hay nhỏ hơn (thuộc hàng số lẻ thập phân thứ 2, 3, …), không cần phải số nguyên.
Số lượng mốc thì chưa biết cách tùy ý. Lần này tôi chỉ mở rộng thêm một mốc nữa là 5 mốc. Muốn nhiều mốc hơn thì phải viết thêm. Muốn xài chỉ 4 mốc thì cứ chọn 1 mốc nào đó viết thành 2 mốc giống nhau.
Cụ thể :
Phạm vi (chu kỳ) là 250, viết vào G1 (trước đây là 10 và ngầm hiểu, không viết vào đâu cả; thực ra thì có thể thấy nó trong E1).
5 mốc 10, 30, 70, 140, 180 (yêu cầu các mốc là từ 0 đến 250 (có thể bằng 0, nhưng phải nhỏ hơn 250), các mốc có thể trùng nhau tùy ý (thậm chí cả 5 mốc bằng nhau cũng được), các mốc có thể lẻ thập phân tùy ý).
Viết 5 mốc trên theo thứ tự tăng dần 10, 30, 70, 140, 180 vào A1, B1, C1, D1, E1.
Bổ sung thêm một mốc phụ (thực chất là mốc tiếp theo) F1 = A1+G1
Số trước khi làm tròn cho trước tại H1 (và H2, H3, …).
Số sau khi làm tròn (cần tính) nằm tại I1 (và I2, I3, …). Công thức tại I1 :
=(H1-MOD(H1,$G$1))+IF(MOD(H1,$G$1)<($A$1+$B$1)/2,$A$1,IF(MOD(H1,$G$1)<($B$1+$C$1)/2,$B$1,IF(MOD(H1,$G$1)<($C$1+$D$1)/2,$C$1,IF(MOD(H1,$G$1)<($D$1+$E$1)/2,$D$1,IF(MOD(H1,$G$1)<($E$1+$F$1)/2,$E$1,$F$1)))))
Copy I1 sang I2, I3, ...

So với trước thì công thức lần này sử dụng “MOD(H1,$G$1)” thay vì “F1-INT(F1/10)*10”, và thay “INT(F1/10)*10” thành “H1-MOD(H1,$G$1)”

cadafi · 22/9/08

Chuotdong đã viết:
Đọc lại cách giải của các bạn mình thấy đúng là phức tạp không ngờ. Thực tế bài toán mình phải giải quyết không như vậy, có nghĩa là các mốc nếu chỉ là số nguyên (VDụ: 0;3;5;8 chẳng hạn) thì giá trị dữ liệu cần xử lý cũng luôn chỉ là những số nguyên !, tức là không có phần lẻ: trong dữ liệu cần phải quy tròn không có những số kiểu như 22,5; 30,62; hay 102,4 .....v....v....

Hy vọng bài toán đơn giản đi nhiều và thực tế hơn.

Với điều kiện sô nguyên như thế bạn có thể dùng công thức của mình cho đơn giản. Ước gì bạn nói rõ từ đầu.

B2=A2-RIGHT(A2,1)+CHOOSE(RIGHT(A2,1)+1,0,0,3,3 ,5,5,7,7,10,10)

huuthang_bd · 23/9/08

Làm tròn số thập phân với các mốc 0, 3, 5, 7, 10, ... Dùng If kết hợp với Floor. Các bạn xem file đính kèm nha.

Chuotdong · 23/9/08

ca_dafi đã viết:
Với điều kiện sô nguyên như thế bạn có thể dùng công thức của mình cho đơn giản. Ước gì bạn nói rõ từ đầu.

Nếu là thập phân thì mình nghĩ cũng chỉ việc nhân dữ liệu với 10^n lần rồi sử lý là được chứ nhỉ ?

huuthang_bd · 24/9/08

Chuotdong đã viết:
Nếu là thập phân thì mình nghĩ cũng chỉ việc nhân dữ liệu với 10^n lần rồi sử lý là được chứ nhỉ ?

Khác nhau chứ bạn. Ví dụ như muốn làm tròn số 2.33 bạn nhân 10^2 = 233. Làm sao có thể dùng Right với Choose để làm tròn theo cách của ca_dafi.
Gửi bạn thêm cách dùng bảng phụ.

Hàm làm tròn số trong Excel

Huyền thoại GPE

Chuyên gia GPE

Thành viên thường trực

File đính kèm

Bùi Nguyễn Triệu Tường

Thành viên thường trực

Huyền thoại GPE

File đính kèm

Thành viên thường trực

Thành viên gạo cội

File đính kèm

Huyền thoại GPE

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Huyền thoại GPE

Bùi Nguyễn Triệu Tường

Thành viên thường trực

Thành viên chính thức

Thành viên chính thức

Thành viên gạo cội

Chuyên gia GPE

File đính kèm

Thành viên thường trực

Chuyên gia GPE

File đính kèm

Facebook

Group