Speedometer chart: Biểu đồ dạng đồng hồ đo (2 người xem)

[OverAC]

Không biết chúng ta có thể vẻ được biểu đồ dạng đồng hồ như thế này trên Excel. Nhờ mọi người giúp đở.

Thân Cảm ơn

 

[OverAC]

Vừa mới sưu tầm được file này, cũng khá hay nhưng phức tạp quá.
Mời các anh chị thưởng lãm.

 

[ptm0412]

Phương pháp vẽ đồ thị dạng Speedometer

SPEEDOMETER
Phương pháp vẽ đồ thị dạng Speedometer
Speedometer chart dùng để vẽ biểu đồ cho những dạng đồng hồ đo: tốc độ, nhiệt độ, Ampere kế, Volt kế.
Vẽ Speedometer đòi hỏi sử dụng những hàm lượng giác mà ở đây sẽ không nhắc lại.

1. Bước 1: Xác định các thông số của đồng hồ đo:

- Min Angle: góc của kim đồng hồ ở mức thấp nhất tính bằng độ
- Max Angle: góc của kim đồng hồ ở mức cao nhất tính bằng độ
- Min Value: Giá trị đo thấp nhất
- Max Value: Giá trị đo cao nhất
- Angel Gap = Max angle – Min Angle (số đo vùng chia độ tính bằng độ)
- Value Gap = Max Value – Min Value (số đo vùng giá trị)
- Current Angle = Min Angel + (Current Value - Min Value) x Angel Gap/ Value Gap
- Ticks Number: Số khoảng chia trên mặt đồng hồ
- Tick angle Gap = Angle Gap / Ticks Number
- Outer Radius: Bán kính ngoài của vạch chia nhỏ
- Outer Radius 2: Bán kính ngoài của vạch chia lớn
- Inner Radius: Bán kính trong của vạch chia.
- Needle length: Chiều dài kim

Outer Radius|

1,9​

|
Outer Radius 2|

2​

|
Inner Radius|

1,65​

|
Needle length |

1,8​

| |
Min Angle|

-45​

|
Max Angle|

45​

|
Min Value Used|

0​

|
Max Value Used|

100​

|
Angle Gap|

90​

|
Value Gap|

100​

|
Angle|

45​

|
Tick number|

20​

|
Tick Gap|

4,5​

|

2. Bước 2: Tính toán các giá trị để vẽ biểu đồ:

a. Serie 1: Các vạch chia

Mỗi vạch chia có 2 điểm đầu và cuối:
X1 value = Outer Radius x Cos (góc của vạch chia)
Y1 value = Outer Radius x Sin (góc của vạch chia)
X2 value = Inner Radius x Cos (góc của vạch chia)
Y2 value = Inner Radius x Sin (góc của vạch chia)

Đó là vạch chia nhỏ, vạch chia lớn thì sử dụng Outer Radius 2 thay cho outer Radius.
Góc của vạch chia thứ n = Min Angle + n X Tick angle Gap
Hoặc
Góc của vạch chia thứ n = Góc của vạch chia thứ n-1 + Tick angle Gap
Số vạch chia = Số khoảng chia + 1

b. Serie 2: Kim đồng hồ: gồm có gốc và ngọn:
X1 value = 0
Y1 value = 0
X2 value = Needle length x Cos (góc của giá trị hiện thời)
Y2 value = Needle length x Sin (góc của giá trị hiện thời)
Góc của gía trị hiện thời là Current Angle trong bảng trên

 

[ptm0412]

Phương pháp vẽ đồ thị dạng Speedometer (tiếp theo)

3. Bước 3: Lập bảng dữ liệu nguồn cho biểu đồ:

a. Serie 1: Các vạch chia ngắn:

- Cột thứ nhất: Góc chia của vạch từ A29 đến A49
Ô thứ nhất: Max Angle
Ô thứ 2 đến hết: = Ô kề trên - Tick angle Gap
Ô cuối là ô vừa bằng Min Angle

- Cột thứ 2: X1 value: B29 =B$5*COS(RADIANS(A29)), Fill down
- Cột thứ 3: X2 value: D29 =B$6*COS(RADIANS(A29)), Fill down
- Cột thứ 4: Y1 value: C29 =B$5*SIN(RADIANS(A29)), Fill down
- Cột thứ 5: Y2 value: E29 = B$6*SIN(RADIANS(A29)), Fill down

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

28​

​

|

Angles​

​

|

X1​

​

|

X2​

​

|

Y1​

​

|

Y2​

​

|

29​

​

|

45​

|

1,4142135623731​

|

1,1667261889578​

|

1,41421356237309​

|

1,1667261889578​

|

30​

​

|

40,5​

|

1,52081193120006​

|

1,25466984324005​

|

1,29889609666037​

|

1,0715892797448​

|

31​

​

|

36​

|

1,61803398874989​

|

1,33487804071866​

|

1,17557050458495​

|

0,96984566628258​

|

32​

​

|

31,5​

|

1,70528032870818​

|

1,40685627118425​

|

1,0449971294319​

|

0,86212263178132​

|

33​

​

|

27​

|

1,78201304837674​

|

1,47016076491081​

|

0,90798099947909​

|

0,74908432457025​

|

34​

​

|

22,5​

|

1,84775906502257​

|

1,52440122864362​

|

0,7653668647302​

|

0,63142766340240​

|

35​

​

|

18​

|

1,90211303259031​

|

1,569243251887​

|

0,61803398874990​

|

0,50987804071866​

|

36​

​

|

13,5​

|

1,94473984079535​

|

1,60441036865617​

|

0,46689072771181​

|

0,38518485036224​

|

37​

​

|

9​

|

1,97537668119028​

|

1,62968576198198​

|

0,31286893008046​

|

0,25811686731638​

|

38​

​

|

4,5​

|

1,99383466746626​

|

1,64491360065966​

|

0,1569181914557​

|

0,12945750795094​

|

39​

​

|

0​

|

2​

|

1,65​

|

0​

|

0​

|

40​

​

|

-4,5​

|

1,99383466746626​

|

1,64491360065966​

|

-0,1569181914557​

|

-0,12945750795094​

|

41​

​

|

-9​

|

1,97537668119028​

|

1,62968576198198​

|

-0,31286893008046​

|

-0,25811686731638​

|

42​

​

|

-13,5​

|

1,94473984079535​

|

1,60441036865617​

|

-0,46689072771181​

|

-0,38518485036224​

|

43​

​

|

-18​

|

1,90211303259031​

|

1,569243251887​

|

-0,61803398874990​

|

-0,50987804071866​

|

44​

​

|

-22,5​

|

1,84775906502257​

|

1,52440122864362​

|

-0,7653668647302​

|

-0,63142766340240​

|

45​

​

|

-27​

|

1,78201304837674​

|

1,47016076491081​

|

-0,90798099947909​

|

-0,74908432457025​

|

46​

​

|

-31,5​

|

1,70528032870818​

|

1,40685627118425​

|

-1,0449971294319​

|

-0,86212263178132​

|

47​

​

|

-36​

|

1,61803398874989​

|

1,33487804071866​

|

-1,17557050458495​

|

-0,96984566628258​

|

48​

​

|

-40,5​

|

1,52081193120006​

|

1,25466984324005​

|

-1,29889609666037​

|

-1,0715892797448​

|

49​

​

|

-45​

|

1,4142135623731​

|

1,1667261889578​

|

-1,41421356237309​

|

-1,1667261889578​

|

Dùng 1 thủ thuật biến 2 chiều thành 1 chiều, lần lượt cho X1, X2 và Y1, Y2 ta có:

|

G​

​

|

H​

​

|

28​

​

|

X​

​

|

Y​

​

|

29​

​

|

1,4142135623731​

|

1,41421356237309​

|

30​

​

|

1,1667261889578​

|

1,1667261889578​

|

31​

​

|

1,52081193120006​

|

1,29889609666037​

|

32​

​

|

1,25466984324005​

|

1,0715892797448​

|

33​

​

|

1,61803398874989​

|

1,17557050458495​

|

34​

​

|

1,33487804071866​

|

0,96984566628258​

|

35​

​

|

1,70528032870818​

|

1,0449971294319​

|

36​

​

|

1,40685627118425​

|

0,86212263178132​

|

37​

​

|

1,78201304837674​

|

0,90798099947909​

|

38​

​

|

1,47016076491081​

|

0,74908432457025​

|

39​

​

|

1,84775906502257​

|

0,7653668647302​

|

40​

​

|

1,52440122864362​

|

0,63142766340240​

|

41​

​

|

1,90211303259031​

|

0,61803398874990​

|

42​

​

|

1,569243251887​

|

0,50987804071866​

|

43​

​

|

1,94473984079535​

|

0,46689072771181​

|

44​

​

|

1,60441036865617​

|

0,38518485036224​

|

45​

​

|

1,97537668119028​

|

0,31286893008046​

|

46​

​

|

1,62968576198198​

|

0,25811686731638​

|

47​

​

|

1,99383466746626​

|

0,1569181914557​

|

48​

​

|

1,64491360065966​

|

0,12945750795094​

|

49​

​

|

2​

|

0​

|

50​

​

|

1,65​

|

0​

|

51​

​

|

1,99383466746626​

|

-0,1569181914557​

|

52​

​

|

1,64491360065966​

|

-0,12945750795094​

|

53​

​

|

1,97537668119028​

|

-0,31286893008046​

|

54​

​

|

1,62968576198198​

|

-0,25811686731638​

|

55​

​

|

1,94473984079535​

|

-0,46689072771181​

|

56​

​

|

1,60441036865617​

|

-0,38518485036224​

|

57​

​

|

1,90211303259031​

|

-0,61803398874990​

|

58​

​

|

1,569243251887​

|

-0,50987804071866​

|

59​

​

|

1,84775906502257​

|

-0,7653668647302​

|

60​

​

|

1,52440122864362​

|

-0,63142766340240​

|

61​

​

|

1,78201304837674​

|

-0,90798099947909​

|

62​

​

|

1,47016076491081​

|

-0,74908432457025​

|

63​

​

|

1,70528032870818​

|

-1,0449971294319​

|

64​

​

|

1,40685627118425​

|

-0,86212263178132​

|

65​

​

|

1,61803398874989​

|

-1,17557050458495​

|

66​

​

|

1,33487804071866​

|

-0,96984566628258​

|

67​

​

|

1,52081193120006​

|

-1,29889609666037​

|

68​

​

|

1,25466984324005​

|

-1,0715892797448​

|

69​

​

|

1,4142135623731​

|

-1,41421356237309​

|

70​

​

|

1,1667261889578​

|

-1,1667261889578​

|

 

[ptm0412]

Phương pháp vẽ đồ thị dạng Speedometer (tiếp theo)

4. Bước 4 vẽ biểu đồ:

Nếu dùng ngay bảng dữ liệu trên để vẽ biểu đồ, ta sẽ làm tuần tự như sau:

- Tạo biểu đồ XY Scatter loại thẳng, không tick mark, không tickmark label
- Sửa X Axis - Scale: Min = -2.2, Max = 2.2, Cross at = 2.2, Partern: Lines = None
- Sửa Y Axis - Scale: Min = -2.2, Max = 2.2, Cross at = 2.2, Partern: Lines = None
(Dùng 2.2 để sau này còn vẽ 1 vòng tròn bao quanh, bán kính 2.2, lớn hơn mép ngoài của vạch chia lớn)
- Sửa Plot Area: Border = None, Fill Area = None

Kết quả:

Ta phải làm thêm 1 bước nữa: 2 cột dữ liệu, cách 2 dòng chèn 1 dòng trắng cho đến hết.

|

G​

​

|

H​

​

|

I​

​

|

28​

​

|

​

​

|

X​

​

|

Y​

​

|

29​

​

|

1​

|

1,4142135623731​

|

1,41421356237309​

|

30​

​

|

2​

|

1,1667261889578​

|

1,1667261889578​

|

31​

​

| | | |

32​

​

|

3​

|

1,52081193120006​

|

1,29889609666037​

|

33​

​

|

4​

|

1,25466984324005​

|

1,0715892797448​

|

34​

​

| | | |

35​

​

|

5​

|

1,61803398874989​

|

1,17557050458495​

|

36​

​

|

6​

|

1,33487804071866​

|

0,96984566628258​

|

37​

​

| | | |

38​

​

|

7​

|

1,70528032870818​

|

1,0449971294319​

|

39​

​

|

8​

|

1,40685627118425​

|

0,86212263178132​

|

40​

​

| | | |

41​

​

|

9​

|

1,78201304837674​

|

0,90798099947909​

|

42​

​

|

10​

|

1,47016076491081​

|

0,74908432457025​

|

43​

​

| | | |

44​

​

|

11​

|

1,84775906502257​

|

0,7653668647302​

|

45​

​

|

12​

|

1,52440122864362​

|

0,63142766340240​

|

46​

​

| | | |

47​

​

|

13​

|

1,90211303259031​

|

0,61803398874990​

|

48​

​

|

14​

|

1,569243251887​

|

0,50987804071866​

|

49​

​

| | | |
......|... |... | ...|

Kết quả:

b. Làm tiếp theo tương tự cho serie 2: Các vạch chia lớn.
Dự định các vạch chia chẵn 5 sẽ dài và đậm hơn các vạch còn lại, nó sẽ ở các vị trí 1, 6, 11, 16, 21.
Nhớ lại: công thức tính giống công thức tính X, Y cho vạch chia ngắn, nhưng thay Outer Radius 2 bằng Outer Radius.
Cũng dùng thủ thuật đổi 2 chiều thành 1 chiều, và chèn dòng, các 2 dòng chèn 1 đến hết.

|

J​

​

|

K​

​

|

L​

​

|

M​

​

|

N​

​

|

O​

​

|

P​

​

|

28​

​

|

Xd1​

​

|

Xd2​

​

|

Yd1​

​

|

Yd2​

​

|

​

​

|

Xd​

​

|

Yd​

​

|

29​

​

|

1,4142135623731​

|

1,1667261889578​

|

1,41421356237309​

|

1,1667261889578​

|

1​

|

1,4142135623731​

|

1,41421356237309​

|

30​

​

| | | | |

2​

|

1,1667261889578​

|

1,1667261889578​

|

31​

​

| | | | | | | |

32​

​

| | | | |

3​

|

0​

|

0​

|

33​

​

| | | | |

4​

|

0​

|

0​

|

34​

​

|

1,84775906502257​

|

1,52440122864362​

|

0,7653668647302​

|

0,63142766340240​

| | | |

35​

​

| | | | |

5​

|

0​

|

0​

|

36​

​

| | | | |

6​

|

0​

|

0​

|

37​

​

| | | | | | | |

38​

​

| | | | |

7​

|

0​

|

0​

|

39​

​

|

2​

|

1,65​

|

0​

|

0​

|

8​

|

0​

|

0​

|

40​

​

| | | | | | | |

41​

​

| | | | |

9​

|

0​

|

0​

|

42​

​

| | | | |

10​

|

0​

|

0​

|

43​

​

| | | | | | | |

44​

​

|

1,84775906502257​

|

1,52440122864362​

|

-0,7653668647302​

|

-0,63142766340240​

|

11​

|

1,84775906502257​

|

0,7653668647302​

|

45​

​

| | | | |

12​

|

1,52440122864362​

|

0,63142766340240​

|

46​

​

| | | | | | | |

47​

​

| | | | |

13​

|

0​

|

0​

|

48​

​

| | | | |

14​

|

0​

|

0​

|

49​

​

|

1,4142135623731​

|

1,1667261889578​

|

-1,41421356237309​

|

-1,1667261889578​

| | | |

50​

​

| | | | |

15​

|

0​

|

0​

|

51​

​

| | | | |

16​

|

0​

|

0​

|
...| ...| ...| ...|... |...|

Add 2 cột này vào đồ thị, sửa lại vì Excel tự thêm 2 serie:
- Xóa bớt 1 serie
- Sửa Datasource của serie còn lại cho đúng Xd và Yd
- Định dạng line đậm hơn

Kết quả:

 

[ptm0412]

Phương pháp vẽ đồ thị dạng Speedometer (tiếp theo)

4. Bước 4 vẽ biểu đồ: (tiếp theo)

c. Serie 3: Needle (cây kim)

Kim đồng hồ: gồm có gốc và ngọn:
X1 value = 0
Y1 value = 0
X2 value = Needle length x Cos (góc của giá trị hiện thời)
Y2 value = Needle length x Sin (góc của giá trị hiện thời)
Góc của gía trị hiện thời là Current Angle trong bảng trên bài 1

- Add vào đồ thị, sửa data source cho đúng X value và Y value, xóa 1 serie thừa.
- Định dạng lại màu đỏ và đường kẻ đậm.
- Vẽ 1 hình tròn nhỏ hoặc 1 cái 16-point star nhỏ, tô màu đỏ, copy và paste vào cái gốc kim. Nhớ chọn riêng 1 point của serie nếu không, sẽ có 2 hình ở 2 đầu của kim.

Nếu muốn vẽ khung tròn cho biểu đồ, ta có thể vẽ 1 hình tròn (shape) bao quanh, nhưng như vậ ysẽ phải group cái shape với cái biểu đồ lại để 2 cái không bị chạy lệch đi.
Ta cũng có thể vẽ 1 biểu đồ tròn cũng XY scatter, với các giá trị X là Cos (góc i), các giá trị y là Sin(góc i), với i là các góc từ 0 đến 360 độ, cách nhau khoảng 10 độ là vừa.

d. Tạo label (số đo của vạch):
Ta chỉ tạo số hiển thị cho các vạch chia lớn, tức serie Yd. Dùng công cụ XY Chart Labeler, add label và dùng dữ liệu Q29:Q90.

Ghi chú: Khi thay đổi giá trị Min Angle và Max angle ta có thể xoay biểu đồ theo ý muốn như các hình sau:

Hình 1: Min -45, max 45

Hình 2: Min 0, max 90

Hình 3: min 45, max 135

Hình 4: Min 135 max 225

Hình 5: Min -45, Max 225

Đây là điều mà file gốc của Bình OverAC không làm được. Khà khà!

Nếu muốn tô màu xanh đỏ cho những vùng khác nhau, chẳng hạn khi vượt quá 80 thì màu đỏ, từ 0 đến 20 thì màu xanh, chẳng hạn, ta tạo 1 vùng dữ liệu tại A20:B23, công thức liên quan đến góc như sau:

|

A​

​

|

B​

​

|

C​

​

|

18​

​

|

RedValue​

​

|

10​

​

|

​

​

|

19​

​

|Blue Value|

15​

| |

20​

​

|Bottom|

270​

|360-B21-B22-B23|

21​

​

|Lower|

18​

|RedValue*Angle Gap/ Value Gap|

22​

​

|Middle|

45​

|B13-B21-B23|

23​

​

|Upper|

27​

|BlueValue*Angle Gap/ Value Gap|

Vùng dữ liệu này tạo 1 serie biểu đồ dạng Doughnut, góc first slice angle 135, hole 75%
Hai số này điều chỉnh bằng tay cho phù hợp.

Kết quả:

Không những có thể thay đổi góc xoay của biểu đồ, ta còn có thể thay giá trị Min và Max value như hình sau: Min 50, Max 100

 

[ptm0412]

Phương pháp vẽ đồ thị dạng Speedometer
(tiếp theo và hết)

Trình bày report có kèm Speedometer:

Hãy tạo report ở 1 sheet mới, trình bày theo ý mình, với đầy đủ dữ liệu của báo cáo, với con số tính toán cuối cùng cần thể hiện trên biểu đồ, giả sử H20.
Quay lại sheet gauge-right, tại ô B1 gõ = Report!H20
Copy nguyên cái biểu đồ, sang sheet Report paste vào.
Dùng thêm các công cụ Drawing, trang trí, tô màu, ... cho đẹp.

Bây giờ khi dữ liệu báo cáo thay đổi, sẽ bắn sang sheet biểu đồ, dẫn đến dữ liệu nguồn của biểu đồ thay đổi, bắn ngược lại biểu đồ trên báo cáo. Một màn trình diễn tuyệt đẹp!
(Đạo diễn bởi lão cheettit old man!)

 

[SA_DQ]

Hồi xưa, trong quyễn vỡ của mình có đề cập đến cái đo tốc này; Nhưng Cái đo tốc của PTM0412 quả là quá đẹp!; (Hay nó là E2K7 mới vậy?)

 

[ptm0412]

(Hay nó là E2K7 mới vậy?)

Cái này làm hoàn toàn trên Excel 2003 Bác ạ. Cải tiến từ file gốc của Bình trong bài 2, cải tiến lại 1 chút vì trong file đó, người ta phải dùng 3 bộ công thức riêng cho từng loại biểu đồ trái, phải, và trên. Còn tôi dùng công thức và phương pháp gán số kiểu khác nên có thể dùng chỉ 1 bộ công thức cho mọi loại trái phải trên dưới.
Ngoài ra cái file đó:
- Không tương thích với Min Value <> 0
- Các vạch chia chẵn 5 và các vạch chia khác dài bằng nhau, định dạng đường kẻ đậm bằng tay và từng vạch một. Ở đây tôi dùng 1 serie riêng cho vạch đậm và cho nó dài hơn
- Label của các vạch chia đậm nói trên cũng add bằng tay và sửa số bằng tay, còn tôi sử dụng công cụ Labeler, dùng công thức có thể thay giá trị label 1 cách tự động
- Vùng tô màu đỏ và xanh không điều khiển thay số tự động theo Red value và Blue value
- Không dùng thủ thuật 2D to 1 D nên chắc làm tay (hì hì)
- hình như còn nữa nhưng không nhớ hết ... (khà khà)

Thay đổi min max của X Axis và Y Axis, ta có thể tuỳ biến hình dạng của đồng hồ đo thí dụ như hình sau:

Ghi chú:
- với dạng méo này, không xài Doughnut tô màu được.
- nếu muốn min bên trái, max bên phải, thì hoán đổi min angle thành max angle, max angle thành min angle. Chỉ cần thay đổi trong khu vực khai báo thôi.

 

[ptm0412]

Một thí dụ về đồng hồ đo đã trang trí

Một mẫu đồng hồ đo đã trang trí theo mẫu bài 1, hình 1.

 

[ptm0412]

Hiệu ứng 3D của Excel 2007

​

 

[ptm0412]

Ứng dụng Speedometer: Đồng hồ chạy theo giờ hệ thống

 

[AdamsTech]

Thanks for your attached file, Mr Ptm0412,
Would you or anyone show me how to make a clock like that?
Any help would be grateful!

 

[cadafi]

Dành cho các thành viên "làm biếng" ^^.

Nếu các bạn có tài khoản Google thì đăng nhập vào Google Docs tạo 1 spreadsheet và biểu đồ dạng Gauge đã tích hợp sẵn vào Google spreedsheet (hy vọng sắp tới sẽ có trên MS Excel).

Mình xin giới thiệu 5 bước đơn giản để tạo 1 Speedommeter trên Google Docs Speadsheet để các bạn tham khảo thêm.

Bước 1

Bước 2

Bước 3

Bước 4

Bước 5

 

[hoangminhtien]

Em gửi bài em vẽ lên đây nhờ thấy Mỹ hướng dẫn thêm để chart đẹp và đỡ dường rà hơn.

 

[ptm0412]

Em gửi bài em vẽ lên đây nhờ thấy Mỹ hướng dẫn thêm để chart đẹp và đỡ dường rà hơn.

Hic, chẳng còn gì để góp ý cả

 

[phihndhsp]

Em gửi bài em vẽ lên đây nhờ thấy Mỹ hướng dẫn thêm để chart đẹp và đỡ dường rà hơn.

quả thật là cao thủ, từ cái chart mà biến thành 1 đồng hồ excel quả thật là làm được nhiều cái mình không thể ngờ