Chúng ta có thể nhập các tệp excel và json cùng nhau không?
|
Excel là một công cụ mạnh mẽ cho phép bạn kết nối với dữ liệu JSON và đọc nó. Tuy nhiên, đôi khi dữ liệu này có thể yêu cầu một chút thao tác để được hiểu và phân tích đầy đủ trong Excel. Show
Trong bài này bạn sẽ học
Bài viết này chứa các bảng dữ liệu để tải xuống để bạn có thể thực hành và nắm vững nghệ thuật phân tích cú pháp dữ liệu JSON tùy chỉnh bằng Excel. Chúng tôi cũng hỗ trợ STEEM để bạn có thể kiếm tiền trong khi học . Nếu bạn không quen thuộc với Excel Power Tools, bạn có thể tìm hiểu về chúng tại đây. Dữ liệu JSON là gì?Dữ liệu JSON là một cách biểu diễn các đối tượng hoặc mảng. Nó dễ đọc và dễ phân tích cú pháp, ngay cả với Excel. Nhiều lệnh gọi API sẽ trả về định dạng JSON và nhiều ứng dụng web sử dụng JSON để dễ dàng di chuyển thông tin trên internet Cú pháp của JSON đến từ JavaScript và có thể được tóm tắt như sau
Nhìn vào JSON mẫu trong hình ảnh bên dưới  {"Tổng dân số". Điều này cho thấy đối tượng đầu tiên, đó là một cặp tên/giá trị. Tên của đối tượng là total_population [{"ngày". “2019-01-02”, “dân số”. 7638335801}, {“ngày”. “2019-01-03”, “dân số”. 7638557789}]} Đây là giá trị cho tổng dân số. [ đại diện cho một mảng. Mảng này chứa hai đối tượng. Các đối tượng được xác định trong dấu ngoặc nhọn và được phân tách bằng dấu phẩy. Mỗi đối tượng chứa 2 lô dữ liệu (cặp tên/giá trị) cũng được phân tách bằng dấu phẩy. Dữ liệu, được hiển thị trong các cặp tên/giá trị, trong ví dụ này là ngày và dân số Đây là một trong những bạn có thể nhìn vào chính mình. https. //api. coinmarketcap. com/v1/ticker/bitcoin/ Nếu bạn nhập cái này vào trình duyệt của mình, bạn sẽ nhận được một cái gì đó như bên dưới Chúng ta hãy xem hình ảnh đó chi tiết hơn Kết nối với dữ liệu JSON từ ExcelTrong ruy-băng Dữ liệu Excel, bên dưới GET và Chuyển đổi dữ liệu, chúng tôi có tùy chọn kết nối với dữ liệu của nhiều nguồn và nhiều loại. Nếu chúng tôi chọn Lấy dữ liệu từ tệp, thì chúng tôi sẽ có tùy chọn lấy dữ liệu từ tệp JSON. Trong ví dụ này, chúng tôi có điểm cuối API URL https. //api. coinmarketcap. com/v1/ticker/bitcoin/. Do đó, từ Data Ribbon, chúng ta có thể chọn, Get data from Web. Thao tác này sẽ mở hộp thoại trong đó bạn đặt URL Tiếp theo, Power query sẽ mở. Power query là một công cụ excel ma thuật sẽ cho phép bạn chuyển đổi dữ liệu mà bạn kết nối thành định dạng có thể sử dụng được Dữ liệu JSON sẽ xuất hiện dưới dạng danh sách bản ghi Power Query. Để excel đọc được điều này, chúng ta phải chuyển đổi danh sách thành bảng. Chọn 'đến bàn' từ tùy chọn có sẵn Tiếp theo, Power query sẽ tạo một bảng và bạn sẽ thấy bước này xuất hiện ở bên phải cửa sổ power query dưới các bước đã áp dụng. Bảng mới này chứa một bản ghi. Chúng ta phải mở rộng bản ghi này để lấy các cặp giá trị. Vì bản ghi này chỉ có 1 hàng nên chúng tôi hy vọng bản ghi này sẽ mở rộng trên các cột. Để làm điều này, nhấp chuột phải vào mũi tên trong tiêu đề cột Điều này sẽ tiết lộ tên của các cặp giá trị. Bằng cách chọn ok, một cột mới sẽ được thiết lập trong bảng. Tên sẽ ở trong tiêu đề và giá trị trong hàng. biến đổi hơn nữaNếu chúng tôi muốn dữ liệu này đi xuống hàng, chúng tôi có thể Bỏ xoay các cột. Bằng cách chọn cột id. Sau đó, từ ruy-băng Chuyển đổi, chọn danh sách thả xuống cho các cột hủy xoay vòng và chọn hủy xoay các cột khác. Khi làm việc với Power Query, điều quan trọng là đảm bảo bạn đã đặt đúng kiểu dữ liệu. Để làm việc với dữ liệu này, bây giờ chúng ta phải chuyển từ Power Query sang Excel. Nếu chúng ta chọn Tệp, sau đó chọn Đóng và tải, thao tác này sẽ tải dữ liệu dưới dạng bảng trong Excel. Hoặc, nếu chúng tôi chọn hoặc Đóng và Tải vào, dữ liệu sẽ được tải vào Mô hình Power Pivot.
Cách phân tích cú pháp dữ liệu JSON trong ExcelRất thường xuyên khi bạn truy cập dữ liệu JSON bằng Excel, nó sẽ xuất hiện trong 1 cột. Điều này có thể xảy ra vì nhiều lý do và thường là do thiết kế cơ sở dữ liệu Nhìn vào hình ảnh dưới đây. Chúng ta có thể thấy trường json_metadata vẫn ở dạng JSON của nó Khi chúng tôi gặp dữ liệu như thế này, chúng tôi có thể dễ dàng phân tích cột thành các thành phần của nó. Từ hình ảnh bên dưới, chúng ta thấy chúng ta có 4 thành phần. Chúng tôi có một mảng, một đối tượng, dữ liệu và một trong các trường dữ liệu chứa một mảng Từng bước mộtTải tập tin này. Nó chứa một bảng như hình dưới đây. (không sao chép và dán bảng vì trường JSON sẽ không được nhận dạng. ) tx_id tid json_metadata dấu thời gian 647524676 theo [“theo dõi”,{“người theo dõi”. ”mervin-gil”,”theo dõi”. "jarvie","cái gì". ["Blog"]}] 43466. 89097 647524682 theo [“theo dõi”,{“người theo dõi”. ”steliosfan”,”đang theo dõi”. "michealb","cái gì". ["Blog"]}] 43466. 89097 647524833 theo [“theo dõi”,{“người theo dõi”. ”eugenezh”,”theo dõi”. "zentricbau","cái gì". ["Blog"]}] 43466. 89097 647524855 theo [“theo dõi”,{“người theo dõi”. ”bitcoinportugal”,”theo dõi”. "manuellevi","cái gì". []}] 43466. 89097 647525074 theo [“theo dõi”,{“người theo dõi”. ”eugenezh”,”theo dõi”. "adriellute","cái gì". ["Blog"]}] 43466. 89167 647525089 theo [“theo dõi”,{“người theo dõi”. ”bigbigtoe”,”theo dõi”. "hoxly","cái gì". ["Blog"]}] 43466. 89167 647525121 theo [“theo dõi”,{“người theo dõi”. ”mervin-gil”,”theo dõi”. "yêu","cái gì". ["Blog"]}] 43466. 89167 647525159 theo [“theo dõi”,{“người theo dõi”. ”mervin-gil”,”theo dõi”. ”pechichemena”,“cái gì”. ["Blog"]}] 43466. 89167 647525233 theo [“theo dõi”,{“người theo dõi”. "imealien","theo dõi". ”pataty69″,”cái gì”. ["Blog"]}] 43466. 89167 647525652 theo [“theo dõi”,{“người theo dõi”. ”mervin-gil”,”theo dõi”. "kamile","cái gì". ["Blog"]}] 43466. 89236 647525818 theo [“theo dõi”,{“người theo dõi”. ”bitcoinportugal”,”theo dõi”. "drmake","cái gì". ["Blog"]}] 43466. 89236 647525886 theo [“theo dõi”,{“người theo dõi”. ”mervin-gil”,”theo dõi”. ”bradfordtennyson”,”cái gì”. ["Blog"]}] 43466. 89236 647525980 theo [“theo dõi”,{“người theo dõi”. ”a0i”,”theo dõi”. "shoemanchu","cái gì". ["Blog"]}] 43466. 89236 647526007 theo [“theo dõi”,{“người theo dõi”. "bình chọn", "theo dõi". ”kostyantin”,“cái gì”. ["Blog"]}] 43466. 89236 648215552 theo [“theo dõi”,{“người theo dõi”. ”ansie”,”đang theo dõi”. "hoxly","cái gì". ["Blog"]}] 43467. 75833 648215582 theo [“theo dõi”,{“người theo dõi”. ”ashokcan143″,”đang theo dõi”. "abcor","cái gì". []}] 43467. 75833 648215691 theo [“theo dõi”,{“người theo dõi”. ”ashokcan143″,”đang theo dõi”. "abasinkanga","cái gì". []}] 43467. 75903 648215820 theo [“theo dõi”,{“người theo dõi”. ” nongvo. piy”,”đang theo dõi”. ”axityo”,”cái gì”. []}] 43467. 75903 648215859 theo [“theo dõi”,{“người theo dõi”. "grid9games","theo dõi". ”yeninfer”,”cái gì”. ["Blog"]}] 43467. 75903 648215945 theo [“theo dõi”,{“người theo dõi”. ” nongvo. piy”,”đang theo dõi”. ”axityo”,”cái gì”. ["Blog"]}] 43467. 75903 Nhấp vào bất kỳ ô nào chứa dữ liệu và từ Dải băng dữ liệu, chọn TỪ Bảng/Phạm vi Nếu dữ liệu của bạn không ở định dạng bảng, thì Excel sẽ nhắc bạn tạo bảng. Chọn các ô chứa dữ liệu và đánh dấu vào ô để nói rằng bảng của bạn có tiêu đề Cửa sổ trình soạn thảo Power Query sẽ mở ra. Trên khung vẽ, bạn có thể xem dữ liệu của mình và ở bên phải, bạn có thể thấy mọi bước chuyển đổi đã diễn ra Để phân tích cú pháp cột json, trước tiên hãy chọn cột, sau đó trên Dải băng chuyển đổi, chọn Phân tích cú pháp và chọn JSON Truy vấn nguồn sẽ nhận ra [ đầu tiên và tạo định dạng danh sách. Tiếp theo, chúng ta cần mở rộng danh sách này sang các hàng mới. Để thực hiện việc này, hãy nhấp vào các mũi tên trên đầu cột và chọn Mở rộng thành hàng mới Những gì được trả về là hai dòng cho mỗi tx-id. Cột json hiện có một hàng cho tên của mảng, theo sau và một bản ghi. Bản ghi sẽ chứa dữ liệu Vì chúng tôi không cần tên của mảng, chúng tôi có thể sử dụng bộ lọc để loại bỏ tất cả các hàng theo sau Bây giờ chúng tôi còn lại với hồ sơ. Ta có thể mở rộng bản ghi này, bằng cách nhấn vào mũi tên trên cột. Từ đây ta thấy mình có tên của 3 trường dữ liệu là Người theo dõi, Theo dõi và Cái gì Khi chúng tôi chọn OK, chúng tôi sẽ nhận được một cột mới trong bảng dữ liệu của mình cho từng trường dữ liệu Tuy nhiên, trường có tên chứa mảng lại được hiển thị dưới dạng danh sách (hoặc mảng) cần được mở rộng Khi chúng tôi mở rộng điều này, chúng tôi đang ở cuối dữ liệu JSON và đã trích xuất các cột có liên quan. Giờ đây, bạn có thể sử dụng dữ liệu này để phân tích thêm trong Excel hoặc Power Pivot. Để tải nó trở lại excel, hãy chọn Tệp và Đóng & Tải Trích xuất dữ liệu JSON phức tạp hơn trong ExcelCho đến giờ, chúng ta đã xem xét việc đưa dữ liệu JSON vào Power Query bằng cách sử dụng bảng Excel và trực tiếp từ một URL. Có nhiều cách khác để bạn có thể kết nối với dữ liệu JSON bao gồm kết nối trực tiếp với tệp JSON. Kết nối là phần dễ dàng. Mọi thứ trở nên phức tạp hơn khi bạn có các cột JSON trong đó các chuỗi khác nhau ở mỗi hàng. Một người có thể bắt đầu với một mảng và do đó trả về một danh sách, nhưng một số có thể bắt đầu với một đối tượng và trả về một bản ghi. Trong Power Query, danh sách được mở rộng thành hàng mới trong bảng và bản ghi được mở rộng thành cột mới. Phân tích cú pháp dữ liệu JSON tùy chỉnh trong Excel có thể yêu cầu một số suy nghĩ Hoạt động tìm hiểu và kiếm tiềnHãy xem bảng dữ liệu nhỏ này. Bạn có thể tải xuống tệp bằng liên kết này để thực hiện Hoạt động Tìm hiểu và Kiếm tiền này. Đây là dữ liệu JSON tùy chỉnh phức tạp hơn có thể được phân tích cú pháp trong Excel bằng truy vấn Power. Cột JSON có độ dài và đối tượng và chuỗi khác nhau. Bạn sẽ phân tích cú pháp dữ liệu JSON tùy chỉnh này như thế nào để dữ liệu có sẵn trong một bảng? Hãy thử một lần. Nếu bạn có tài khoản STEEM, bạn có thể kiếm tiền trong khi học. Xem dưới đây để biết thêm chi tiếtNó đòi hỏi tư duy logic và kiến thức về Power Query ít hơn những gì chúng tôi đã đề cập ở đây nhưng tôi biết bạn có thể hiểu được. Ngoài ra, có nhiều hơn một cách để đưa ra giải pháp Đăng giải pháp của bạn trong phần bình luận bên dưới. Nếu bạn gặp khó khăn, hãy đăng bình luận cho tôi biết bạn đang gặp khó khăn ở đâu và bạn gặp phải vấn đề gì Video bên dưới cho biết cách bạn có thể phân tích cú pháp dữ liệu JSON đơn giản bằng cách sử dụng Excel Power Query và cách bạn có thể phân tích cú pháp dữ liệu JSON tùy chỉnh như được trình bày chi tiết trong hoạt động Chúng ta có thể nhập tệp Excel và JSON cùng nhau không?Trong Phiên bản Excel Mới hơn
. Hộp thoại Nhập dữ liệu xuất hiện. Tìm kiếm tệp JSON, rồi chọn Mở. Select Data > Get Data > From File > From JSON. The Import Data dialog box appears. Search the JSON file, and then select Open.
Tôi có thể xuất tệp JSON trong Excel không?JSON (Javascript Object Notation) là định dạng trao đổi dữ liệu được sử dụng nhiều nhất hiện nay. Microsoft Excel không có hỗ trợ tích hợp để nhập JSON sang excel hoặc xuất dữ liệu excel sang JSON .
Bạn có thể nhập vào tệp JSON không?Kể từ Chrome 91, bạn có thể nhập JSON trực tiếp chẳng hạn. // Bài kiểm tra. json {"xin chào". "world" } // Nhập tĩnh nhập json từ ". /kiểm tra. json" khẳng định { loại. "json" }; . |
