Hướng dẫn chuyển phân số bị lưu dạng text về số thập phân và tính trung bình của một số theo từng lần

Làm thế nào để chuyển phân số bị lưu dạng text về số thập phân và tính trung bình của một số theo từng lần trong Excel. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.

 

Hướng dẫn chuyển phân số bị lưu dạng text về số thập phân và tính trung bình của một số theo từng lần

1. Hướng dẫn chuyển phân số bị lưu dạng text về số thập phân

Trong quá trình sao chép dữ liệu từ 1 file khác vào 1 file excel, đôi khi dữ liệu không trả về đúng loại dữ liệu mong muốn, đặc biệt là với dữ liệu dạng số, số thập phân. Điều này ảnh hưởng nhiều tới quá trình xử lý và tính toán trong excel. Vậy làm thế nào để khắc phục điều này? Hãy cùng Học Excel Online tìm hiểu về cách chuyển phân số bị lưu dạng text về dạng số thập phân thông qua ví dụ sau:

Cột A chứa các giá trị phân số mà theo mắt thường chúng ta nhìn thấy

Cột B chứa các công thức được tính toán từ cột A

Cột C diễn giải công thức nằm trong cột B

Vậy bạn có biết tại sao lại xuất hiện các giá trị như ở cột B không? Làm thế nào để chuyển các số thập phân ở cột A về đúng dạng số thập phân để cho ra các giá trị đúng ở cột B? Sau đây là 1 cách khắc phục bằng hàm tự tạo trong VBA của Học Excel Online:

Bước 1: Mở cửa sổ Visual Basic Application (VBA)

Có 2 cách để mở cửa sổ VBA:

  • Cách 1: Bấm tổ hợp phím tắt Alt + F11
  • Cách 2: Trong tab Developer, bấm chọn Visual Basic

Bước 2: Tạo mới 1 Module

Bấm chuột phải vào mục Project – VBAProject rồi chọn Insert / Module

Tạo mới 1 Module

Bước 3: Tạo hàm trong VBA để định dạng lại dữ liệu

Trong Module 1 vừa tạo, chúng ta dùng lệnh Function để tạo ra 1 hàm.

Hàm có tên là EVAL, sử dụng đối số là Range (lấy từ 1 ô nào đó trong excel)

EVAL = Evaluate(Range.Value)

Evaluate là việc đánh giá, định hình lại dữ liệu về đúng định dạng mà nó đang hiển thị.

Tham số trong Evaluate là Range.Value thể hiện là chỉ định dạng về giá trị của nội dung bên trong Range

Ví dụ: Dữ liệu hiển thị là 3/7 sẽ được đánh giá là số thập phân (3 phần 7) chứ không phải là ngày 03 tháng 07.

Cấu trúc đầy đủ của Function như sau:

Function EVAL(range) 'Hàm định dạng lại dữ liệu
EVAL = Evaluate(range.Value)
End Function

Bước 4: Cách sử dụng hàm EVAL

Cách sử dụng rất đơn giản: trong ô B1 chúng ta viết =Eval(A1)

2. Hướng dẫn cách tính trung bình của một số theo từng lần

2.1. Đặt vấn đề

Thông thường bạn vẫn hiểu rằng muốn tính trung  bình của 1 số thì sẽ lấy số đó chia cho tổng số lần. Thế nhưng nếu như việc chia từng lần đó gây ra số lẻ thì sao? hoặc nếu chúng ta buộc phải làm tròn số trung bình của từng lần thì liệu việc làm tròn đó còn chính xác hay không?

Xét ví dụ sau:

Tính trung bình của một số theo từng lần

Ví dụ chúng ta muốn thanh toán 1 khoản tiền là 14.654.000 theo 6 đợt (6 lần), vậy chúng ta sẽ tính là:

14.654.000 / 6 = 2.442.333,33333333

hẳn bạn không muốn tính lẻ tới như vậy, do đó bạn muốn làm tròn tới hàng nghìn cho dễ trả

=> Theo nguyên tắc làm tròn thì số 2.442.333,3333 sẽ làm tròn thành 2.442.000

(Hàm ROUND(Số cần làm tròn, -3)

Như vậy phần số lẻ sẽ bị thiếu, do đó làm tổng số tiền của 6 đợt sẽ còn 14.652.000 => Kết quả không đúng

Sai lệch sẽ trở nên lớn hơn nhiều với việc chia thành nhiều lần thanh toán hơn.

2.2. Cách giải quyết

2.2.1 Bù trừ phần thiếu vào lần cuối

Bù trừ phần thiếu vào lần cuối

Quy định sẵn tổng số cần phải thanh toán luôn luôn bằng với số nợ ban đầu nên D11 = C1

Tại các lần thanh toán từ 1 đến 5 thì tính trung bình như thông thường

Tại lần thanh toán cuối (lần thứ 6) thì sẽ bằng Tổng số phải thanh toán – Tổng những lần thanh toán trước đó

D10 = D11 – SUM(D5:D9)

Như vậy với cách này toàn bộ phần sai lệch do làm tròn ở những lần trước đó sẽ được cộng vào lần cuối cùng.

Ưu điểm:

Đảm bảo kết quả cuối cùng luôn đúng

Nhược điểm

Không đồng đều giữa các lần. Tại lần cuối có thể phát  sinh lớn hơn hẳn so với các lần trước đó.

2.2.2 Cách tính trung bình sau mỗi lần thanh toán

Để khắc phục nhược điểm của cách trên, chúng ta tìm hiểu về cách tính trung bình sau mỗi lần thanh toán.

Cách này được hiểu như sau:

Tính trung bình sau mỗi lần thanh toán.

Tại mỗi lần thanh toán, số phải thanh toán = Số tiền còn lại chưa thanh toán / Số lần  còn phải thanh toán

Số tiền còn lại chưa  thanh toán = Số tiền ban đầu – Số đã thanh toán ở những lần trước đó

Số tiền ban đầu = C1

Số tiền đã thanh toán ở những lần trước đó:

  • Lần 1: Sum(E4:E4) là lần 0
  • Lần 2: Sum(E4:E5) là của lần 0 + lần 1
  • Lần 3: Sum(E4:E6) là của Lần 0 + lần 1 + lần 2
  • Tương tự cho những lần tiếp theo

Khi đó ta có thể thấy ô E4 luôn xuất hiện trong công thức tính tổng => Cố định điểm E4

Số lần còn phải thanh toán = Đếm số dòng của các lần  còn lại. Sử dụng hàm ROWS để đếm cột Lần thanh toán, từ lần thực hiện đến lần 6 => khi đó ô B10 luôn xuất hiện trong các công thức ROWS => Cố định điểm B10

Để làm tròn số => Đặt  toàn bộ công thức trong hàm ROUND

Như vậy chúng ta có thể thấy kết quả đã dồn những phần sai lệch do làm tròn ngay thời điểm số sai lệch vừa đủ để thành 1 số nguyên (ở ô E7 và E9) => Kết quả chính xác hơn tại các lần.

Như vậy bài viết đã hướng dẫn cho bạn cách chuyển phân số bị lưu dạng text về số thập phân và tính trung bình của một số theo từng lần. Chỉ cần vài thao tác đơn giản, bạn đã có thể chuyển phân số và tính giá trị trung bình một cách nhanh chóng nhất. Chúc các bạn thực hiện thao tác thành công!

Ngày:13/08/2020 Chia sẻ bởi:Nhi

CÓ THỂ BẠN QUAN TÂM