Cập nhật nội dung chi tiết về Cách Sửa Lỗi Ngày Tháng Của Dữ Liệu Được Trích Xuất Từ Phần Mềm Ra Excel Chi Tiết Nhất mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Khi trích xuất dữ liệu từ các phần mềm khác (ví dụ như phần mềm chấm công) ra excel, chúng ta thường gặp phải 1 vấn đề đó là dữ liệu ngày tháng, thời gian thường bị lỗi, không thể sử dụng để tính toán ngay. Trong bài viết này chúng tôi sẽ hướng dẫn các bạn cách sửa lỗi ngày tháng của dữ liệu được trích xuất từ phần mềm ra excel.
Đặc điểm dữ liệu thời gian trích xuất từ phần mềm
Trong file ví dụ chúng ta có bảng dữ liệu được trích xuất từ phần mềm chấm công như sau:
Trong đó các cột Date và InTime là dữ liệu dạng Ngày và Giờ. Tuy nhiên những dữ liệu này lại không dùng để tính toán ngay được vì nó không phải dữ liệu đúng chuẩn dạng thời gian mà Excel quy định.
Chúng ta có thể kiểm tra bằng cách sử dụng hàm IsNumber cho cột B và C như sau:
Như vậy về bản chất dữ liệu trong ô B2 là Text chứ không phải Number. Do đó không dùng trong tính toán được.
Cách sửa lỗi định dạng thời gian
Hàm VALUE giúp chuyển dữ liệu từ dạng Text về dạng Number. Về bản chất, dữ liệu thời gian trong Excel là dạng Number.
Cách làm như sau:
Bước 1: Tại cột E (cột phụ), chúng ta dùng hàm VALUE, đối tượng của hàm VALUE là giá trị trong cột B
Ví dụ: E2=VALUE(B2)
Kết quả của đoạn text 1/Jun/2018 khi chuyển sang Number có giá trị là 43252
Bước 2: Copy kết quả của hàm Value, dán trở lại vào cột B
Bước 3: Định dạng dữ liệu bằng Format cells
Chúng ta sẽ định dạng lại dữ liệu trong cột B để đưa về đúng định dạng dd/mm/yyyy bằng cách:
Chọn vùng dữ liệu trong cột B
Mở Format Cells (phím tắt Ctrl + 1)
Trong thẻ Number chọn Custome, trong mục Type nhập dd/mm/yyyy
Như vậy kết quả thu được sẽ là dữ liệu dạng thời gian có dạng ngày/tháng/năm đúng tiêu chuẩn của Excel.
Cách 2: Dùng công cụ TextToColumn
TextToColumn là một công cụ khá hữu ích trong Excel. Ngoài việc tách dữ liệu ra nhiều cột thì nó còn có tác dụng chuyển đổi dữ liệu về đúng định dạng tiêu chuẩn trong Excel. Cách thực hiện như sau:
Bước 1: Chọn bảng dữ liệu cần chuyển đổi, trong thẻ Data chọn TextToColumn
Bước 2: Trong cửa sổ TextToColumn (step 1 of 3) bấm Next để sang Step 2 of 3
Bước 3: Bấm Next một lần nữa để sang Step 3 of 3
Tại bước này, chúng ta bấm chọn mục Date và chọn tương ứng cách hiển thị thời gian theo dạng DMY (D là ngày, M là tháng, Y là năm)
Sau đó bấm Finish để hoàn thành.
Cả 2 cách đều áp dụng được cho dữ liệu Ngày, Giờ
Cách dùng hàm VALUE sẽ tuân thủ theo thiết lập ngày tháng mặc định trong Region của máy tính (Control pannel/ Region/ Date-Time)
Cách dùng TextToColumn cho phép tự do lựa chọn cách hiển thị Tháng trước Ngày sau hay Ngày trước tháng sau một cách linh hoạt hơn. Kết quả trả về ngay tại vị trí ban đầu, không cần copy/paste như cách dùng hàm VALUE
Tìm hiểu thêm các nội dung tương tự:
Hướng dẫn chuyển kiểu định dạng dữ liệu từ số (Number) sang chữ (Text) trong Excel Hướng dẫn cách chuyển từ ngày tháng (Date) về định dạng số (Number) hoặc chữ (Text) trong Excel Sử dụng hàm VALUE và các tùy chọn khác để chuyển đổi định dạng của số từ text sang number
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hướng Dẫn Cách Import Dữ Liệu Từ File Csv Vào Excel Chi Tiết Nhất
Trong hướng dẫn này, chúng tôi sẽ giới thiệu cách để nhập (import) dữ liệu từ một tập tin CSV bên ngoài có chứa các khoản thanh toán của khách hàng. Bài tập này sẽ cung cấp sẵn tập tin CSV đính kèm để bạn đọc có thể dễ dàng thực hành và hiểu rõ hơn.
Bước 2: Mở một bảng tính mới
Bước 3: Trên giao diện ribbon của Excel, bạn chọ thẻ DATA
Bước 4: Sau đó bấm nút From Text ở góc trên cùng bên trái, ngay dưới nhóm tính năng Get External Data
Bước 5: Cửa sổ Import File sẽ hiển thị.
Bước 6: Bạn duyệt đến thư mục nơi lưu tập tin CSV đã tải về máy tính ở trên bước 1, sau đó kích chọn tên tập tin định dạng .csv rồi bấm nút Import.
Bước 7: Trình hướng dẫn Text Import Wizard sẽ hiển thị yêu cầu bạn thực hiện tuần tự theo ba bước.
Trước tiên kích vào tùy chọn Delimited (1).
Tiếp theo bấm nút Next (2) để chuyển sang bước 2
Tích vào tùy chọn Comma ngay dưới mục Delimited (1), rồi bấm Next (2)
Kích tùy chọn General dưới mục Column data format (1), sau đó bấm nút Finish (2) để kết thúc
Bước 8: Quay trở ra hộp thoại Import Data, bạn bấm nút OK
Bước 9: Ngay lập tức bạn sẽ nhận được dữ liệu đã được Import vào file.
Bước 2: Mở một bảng tính mới
Bước 3: Trên giao diện ribbon của Excel, bạn chọ thẻ DATA
Bước 4: Sau đó bấm nút From Text ở góc trên cùng bên trái, ngay dưới nhóm tính năng Get External Data
Bước 5: Cửa sổ Import File sẽ hiển thị. Duyệt đến thư mục nơi lưu tập tin CSV đã tải về ở bước 1, sau đó kích chọn tên tập tin định dạng .csv rồi bấm nút Import.
Bước 6: Trình hướng dẫn Text Import Wizard sẽ hiển thị yêu cầu bạn thực hiện tuần tự theo ba bước.
Trước tiên kích vào tùy chọn Delimited (1), rồi bấm nút Next.
Sang bước 2, dưới mục Delimiter bạn kích vào tùy chọn Other rồi nhập dấu gạch chéo vào ô trống tương ứng bên phải. Sau đó bấm nút Next để tiếp tục.
Lưu ý: Nếu tập tin CSV được phân tách bằng dấu phẩy hoặc bất kỳ dấu nào khác thì bạn vẫn kích vào tùy chọn Other, rồi nhập dấu tương ứng vào ô bên phải.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Cách Sửa Lỗi Ngày Tháng Bị Đảo Ngược Trong Excel
Chính vì vậy nên khi viết ngày tháng trong Excel chúng ta sẽ thấy ngày tháng bị tự động đảo ngược cho nhau.
Chỉ duy nhất những trường hợp bạn điền “ngày” lớn hơn 12 thì ngày tháng của bạn mới không bị đảo ngược lại nhưng đó là do Excel đã không đọc hiểu ô đó là một ô ngày tháng (bởi vì không có tháng nào lớn hơn 12 cả) mà chỉ coi nó như một ô dữ liệu thông thường.
Thật vậy, bạn có thể thấy điều này khi nhìn vào kiểu ô của ô dữ liệu. Nó nằm trong mục Number của ribbon Home. Kiểu ô của ô dữ liệu đó vẫn là General chứ không biến đổi thành Date như những trường hợp khác.
Cách 1: Viết ngày tháng như một ô dữ liệu ký tự chứ không phải ô dữ liệu ngày tháng
Để làm được điều này, bạn cần phải điền ‘ trước khi điền ngày tháng. Với một dấu ‘ ở phía trước, nội dung phía sau của nó đều được Excel coi là ký tự và ô dữ liệu đó sẽ là ô dữ liệu ký tự bình thường General.
Phương pháp này giải quyết một cách triệt để và hữu dụng trong mọi trường hợp sử dụng ngày tháng của Excel. Các bạn có thể đổi lại cách viết ngày tháng trước kia bằng với kiểu viết ngày tháng phù hợp với mình, ở đây thì chúng ta sẽ dùng kiểu Việt Nam đó là ngày đến tháng rồi đến năm.
Trong quá trình thực hiện các bước thay đổi quy cách ghi ngày tháng của Excel, ThuThuatPhanMem sẽ sử dụng hàm TODAY cho việc kiểm tra.
Hàm TODAY giúp bạn trích xuất ra ngày tháng năm tại thời điểm bạn sử dụng hàm này (nó sẽ dựa vào ngày tháng năm trong hệ thống máy tính của bạn). Công thức cho hàm TODAY là:
=TODAY()
Từ đầu ta sử dụng hàm TODAY thử thì sẽ thấy thứ tự sắp xếp ngày tháng năm là tháng đứng trước ngày và năm đứng cuối cùng. (Ngày 25 tháng 12 năm 2019).
Hướng Dẫn Kỹ Thuật Làm Sạch Bảng Dữ Liệu, Sửa Lỗi Dữ Liệu Trong Excel
Trong thực tế rất khó để đảm bảo bảng dữ liệu không có lỗi. Lỗi này có thể do đánh máy sai, hoặc Excel hiểu sai về kiểu dữ liệu, hoặc dữ liệu bạn nhận được từ người khác… Do vậy kỹ năng làm sạch bảng dữ liệu là không thể thiếu khi bạn làm việc trên Excel. Kỹ năng này bao gồm:
Như trong chương 1 chúng ta đã biết có 3 loại dữ liệu hay gặp là:
Kiểu Number : là dạng số, chỉ chứa các con số (có thể kèm theo các ký tự về tiền tệ, %, các dấu ngăn cách phần thập phân, phần nghìn)
Kiểu Date : là dạng ngày tháng, thời gian. Gồm các con số kết hợp dấu ngăn cách thời gian như dấu gạch ngang ( – ), gạch chéo ( / ) hoặc dấu hai chấm ( : )
Dữ liệu kiểu Text
Dữ liệu này thường khó phát hiện lỗi bởi nó không có tính quy luật. Đặc biệt trong các bảng dữ liệu lớn thì càng khó khăn hơn, bởi không thể làm điều này bằng mắt thường.
Tuy nhiên may mắn là không phải dữ liệu Text nào cũng phải kiểm tra và sửa lỗi. Bạn chỉ cần kiểm tra trên những cột chứa nội dung quan trọng, phục vụ cho việc lập báo cáo thôi. Những cột này có đặc điểm là dữ liệu có tính lặp lại theo 1 danh sách nhất định.
Các bước kiểm tra như sau:
Bước 1 : tạo bộ lọc (Filter) cho bảng dữ liệu. Bước này sẽ giúp bạn giảm bớt được các nội dung cần đánh giá. Các nội dung trong bộ lọc đã được giản lược đi những nội dung giống nhau (mỗi nội dung giống nhau chỉ xuất hiện 1 lần)
Bước 2 : một số đặc điểm lỗi hay gặp:
Lỗi ký tự text về dấu cách trong bộ lọc
Bộ gõ Unicode: sử dụng các font Arial, Times New Roman, Calibri
Bộ gõ VNI: sử dụng các font bắt đầu với từ VNI
Bộ gõ TCVN3: sử dụng các font bắt đầu với từ .Vn
Khi đó để phát hiện có lỗi font chữ không, bạn sẽ thiết lập định dạng toàn bộ bảng dữ liệu về thống nhất hiển thị theo 1 font chữ, ví dụ là Times New Roman. Khi đó các đoạn Text được tạo ra bởi các bộ gõ khác sẽ không hiển thị đúng tiếng việt nữa (bị lỗi font).
Định dạng thống nhất font chữ
Cách khắc phục sai sót
Bước 1 : Chọn toàn bộ cột dữ liệu ( chọn ô đầu tiên trong cột, sau đó bấm phím tắt Ctrl + Shift + Phím mũi tên đi xuống – down arrow )
Bước 2 : Copy nội dung được chọn tại Bước 1, dán ra 1 vị trí khác, Sheet khác
Vị trí công cụ Remove Duplicates trên thẻ Data
Bước 4 : Dựa trên danh sách còn lại ở bước 3, bạn nhập lại theo 1 danh sách đúng chính tả
Như vậy bạn sẽ có 1 bảng gồm 2 cột:
Bước 5 : Trong bảng dữ liệu, bạn tạo 1 cột phụ. Mục đích là sử dụng công thức để tham chiếu tới các dữ liệu đúng.
=VLOOKUP( Dữ liệu gốc, Bảng tham chiếu, 2, 0)
Bước 6 : Kéo công thức (filldown) xuống toàn bộ nội dung theo bảng dữ liệu
Bước 8 : Xóa cột phụ, hoàn tất thao tác.
Trong các thao tác trên, quan trọng nhất là bước 3, 4, 5. Mục đích là để tạo ra 1 bảng làm cơ sở gồm cả danh sách sai và danh sách đúng. Bạn chỉ có thể sửa chính tả trên 1 số ít dữ liệu, khó có thể sửa trên số lượng lớn. Do vậy cần lọc bỏ các giá trị trùng lặp trước, sau đó mới sửa lại danh sách.
Dữ liệu kiểu Number
Dữ liệu kiểu Number khá dễ để nhận diện và phát hiện sai sót, bởi nó có tính quy luật và có những đặc trưng riêng. Những sai sót hay gặp với dữ liệu Number thường là:
Sử dụng dấu ngăn cách phần thập phân, phần nghìn không đúng. Việc này khiến dữ liệu của bạn nhìn giống Số nhưng không phải kiểu số.
Viết chữ O thay vì số 0. Vì 2 vị trí phím này gần nhau và cũng giống nhau nên rất dễ viết nhầm.
Dữ liệu chứa các con số nhưng là kiểu Text. Loại lỗi này khó phát hiện bằng mắt thường, bạn cần phải dùng hàm kiểm tra kiểu dữ liệu mới phát hiện được.
Các bước phát hiện sai sót
Bước 1 : Tạo 1 cột phụ bên cạnh cột muốn kiểm tra
Bước 2 : Tại cột phụ, bạn dùng hàm ISNUMBER để xem dữ liệu tại cột muốn kiểm tra có đúng kiểu Number hay không. Nếu kết quả là False thì không phải kiểu Number.
Bước 3 : Sử dụng bộ lọc Filter lọc những kết quả False trên cột phụ.
Cách khắc phục sai sót
Dựa trên kết quả thu được trong quá trình kiểm tra, bạn sẽ xem xét quy luật và sửa những lỗi đó.
Nếu có xen lẫn các ký tự dấu chấm, dấu phẩy một cách cố ý (bản chất dữ liệu số khi nhập vào Excel sẽ không yêu cầu nhập kèm các ký tự này), bạn hãy xóa các dấu đó đi với chức năng Find & Replace của Excel
Nếu sử dụng chữ O thay vì số 0, hãy sửa trực tiếp ký tự này (trường hợp này rất ít xảy ra nên có thể sửa trực tiếp từng lỗi)
Nếu lỗi là sai kiểu dữ liệu (ngoài các trường hợp trên mà vẫn có lỗi) thì bạn sửa bằng cách sau:
Trong đó dữ liệu gốc là ô trong cột dữ liệu gốc. Hàm Value sẽ chuyển dữ liệu về dạng Number
Cách phân biệt dạng dữ liệu trong Excel với nhóm hàm IsNumber, IsNonText, IsText
Dữ liệu kiểu Date
Dữ liệu ngày tháng, thời gian là loại dữ liệu thường xuyên sử dụng và cũng hay có sai sót nhất. Do chúng ta không để ý tới việc thiết lập chuẩn ngày tháng trong Region dẫn tới việc nhập sai về thứ tự các số ngày, số tháng trong chuỗi ngày tháng năm (nội dung này đã được nói rõ tại chương 1).
Việc thiết lập thời gian lại tùy thuộc vào từng máy tính. Khi bạn đưa file Excel sang 1 máy tính có thiết lập thời gian khác thì dữ liệu ngày tháng lại tự động thay đổi theo máy tính mới. Việc này gây ra nhiều sai sót, nhầm lẫn trong quá trình thu thập dữ liệu thời gian.
Các bước phát hiện sai sót
Có 2 bước cơ bản để kiểm tra dữ liệu thời gian có sai sót hay không:
Cách khắc phục sai sót
Về bản chất dữ liệu thời gian là dữ liệu kiểu số (number) nhưng có cách định dạng riêng (theo dạng Date). Do đó cách xử lý lỗi của dữ liệu Date giống với Number, đó là dùng hàm Value.
Sau khi thu được kết quả, bạn chỉ cần định dạng lại dữ liệu đó về dạng ngày tháng là được.
Bài học rút ra
Luôn kiểm tra lại nội dung trong bảng dữ liệu để đề phòng sai sót. Nếu dữ liệu sai thì bạn không thể làm ra được báo cáo đúng.
Mỗi kiểu dữ liệu lại có 1 cách kiểm tra và sửa khác nhau. Hãy học thuộc và luyện tập kỹ các kỹ thuật tìm và sửa lỗi này, vì thực tế đôi khi phức tạp hơn nhiều so với ví dụ trong bài viết này.
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Bạn đang đọc nội dung bài viết Cách Sửa Lỗi Ngày Tháng Của Dữ Liệu Được Trích Xuất Từ Phần Mềm Ra Excel Chi Tiết Nhất trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!