Tra cứu, Biểu đồ, Thống kê và Bảng Pivot
Đã xem xét các chức năng cơ bản, tham chiếu ô và chức năng ngày và giờ, giờ chúng tôi đi sâu vào một số tính năng nâng cao hơn của Microsoft Excel. Chúng tôi trình bày các phương pháp để giải quyết các vấn đề kinh điển trong tài chính, báo cáo bán hàng, chi phí vận chuyển và thống kê.
CHUYỂN ĐỔI TRƯỜNG- Tại sao bạn cần công thức và hàm?
- Xác định và tạo công thức
- Tham chiếu ô tương đối và tuyệt đối và định dạng
- Các chức năng hữu ích bạn nên biết
- Tra cứu, Biểu đồ, Thống kê và Bảng Pivot
Các chức năng này rất quan trọng đối với doanh nghiệp, sinh viên và những người chỉ muốn tìm hiểu thêm.
VLOOKUP và HLOOKUP
Dưới đây là một ví dụ để minh họa các chức năng tra cứu dọc (VLOOKUP) và tra cứu ngang (HLOOKUP). Các hàm này được sử dụng để dịch một số hoặc giá trị khác thành thứ gì đó dễ hiểu. Ví dụ: bạn có thể sử dụng VLOOKUP để lấy số phần và trả về mô tả mục.
Để điều tra vấn đề này, chúng ta hãy quay lại bảng tính Quyết định của Nhà sản xuất của chúng tôi trong Phần 4, nơi Jane đang cố gắng quyết định nên mặc gì khi đến trường. Cô ấy không còn hứng thú với những gì cô ấy mặc, vì cô ấy đã có bạn trai mới, vì vậy bây giờ cô ấy sẽ mặc trang phục và giày ngẫu nhiên.
Trong bảng tính của Jane, cô liệt kê trang phục theo cột dọc và giày, cột ngang.
Cô mở bảng tính và chức năng RANDBETweEN (1,3) tạo ra một số giữa hoặc bằng một và ba tương ứng với ba loại trang phục cô có thể mặc.
Cô sử dụng chức năng RANDBETweEN (1,5) để chọn trong số năm loại giày.
Vì Jane không thể đeo số, chúng tôi cần chuyển đổi tên này thành tên, vì vậy chúng tôi sử dụng các chức năng tra cứu.
Chúng tôi sử dụng chức năng VLOOKUP để dịch số trang phục thành tên trang phục. HLOOKUP dịch từ số giày sang các loại giày khác nhau trong hàng.
Bảng tính hoạt động như thế này cho trang phục:
Excel chọn một số ngẫu nhiên từ một đến ba, vì cô có ba tùy chọn trang phục.
Tiếp theo, công thức chuyển số thành văn bản bằng cách sử dụng = VLOOKUP (B11, A2: B4,2) sử dụng số ngẫu nhiên giá trị từ B11 để tìm trong phạm vi A2: B4. Sau đó, nó đưa ra kết quả (C11) từ dữ liệu được liệt kê trong cột thứ hai.
Chúng tôi sử dụng cùng một kỹ thuật để chọn giày, ngoại trừ lần này chúng tôi sử dụng VOOKUP thay vì HLOOKUP.
Ví dụ: Thống kê cơ bản
Hầu hết mọi người đều biết một công thức từ thống kê - trung bình - nhưng có một thống kê khác rất quan trọng đối với doanh nghiệp: độ lệch chuẩn.
Ví dụ, nhiều người đã đi học đại học đã khổ sở vì điểm SAT của họ. Họ có thể muốn biết làm thế nào họ xếp hạng so với các sinh viên khác. Các trường đại học cũng muốn biết điều này vì nhiều trường đại học, đặc biệt là những trường danh tiếng, từ chối sinh viên có điểm SAT thấp.
Vì vậy, làm thế nào chúng ta, hoặc một trường đại học, đo lường và giải thích điểm SAT? Dưới đây là điểm SAT cho năm học sinh từ 1.870 đến 2.230.
Những con số quan trọng cần hiểu là:
Trung bình cộng - Trung bình cũng được gọi là trung bình.
Độ lệch chuẩn (STD hoặc) - Con số này cho thấy mức độ phân tán rộng rãi của một bộ số. Nếu độ lệch chuẩn lớn, thì các số cách xa nhau và nếu bằng 0, tất cả các số đều giống nhau. Bạn có thể nói rằng độ lệch chuẩn là chênh lệch trung bình giữa giá trị trung bình và giá trị quan sát được, tức là 1,998 và mỗi điểm SAT. Xin lưu ý, thông thường là viết tắt độ lệch chuẩn bằng cách sử dụng ký hiệu Hy Lạp sigma là σ.
Xếp hạng phần trăm - Khi một học sinh nhận được điểm cao, họ có thể khoe rằng họ nằm trong top 99 phần trăm hoặc đại loại như thế. Xếp hạng Phần trăm của Phần trăm có nghĩa là tỷ lệ phần trăm thấp hơn một điểm số cụ thể.
Độ lệch chuẩn và xác suất được liên kết chặt chẽ. Bạn có thể nói rằng với mỗi độ lệch chuẩn, xác suất hoặc khả năng số đó nằm trong số độ lệch chuẩn đó là:
| STD | Tỷ lệ điểm | Phạm vi điểm SAT |
| 1 | 68% | 1.854-2.142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99,73% | 1,567-2,429 |
| 4 | 99,994% | 1.424-2.572 |
Như bạn có thể thấy, khả năng bất kỳ điểm SAT nào nằm ngoài 3 STD thực tế là bằng 0, vì 99,73 phần trăm điểm nằm trong 3 STD.
Bây giờ chúng ta hãy nhìn vào bảng tính một lần nữa và giải thích cách nó hoạt động.
Bây giờ chúng tôi giải thích các công thức:
= AVERAGE (B2: B6)
Điểm trung bình của tất cả các điểm trong phạm vi B2: B6. Cụ thể, tổng của tất cả các điểm được chia cho số người tham gia bài kiểm tra.
= STDEV.P (B2: B6)
Độ lệch chuẩn trên phạm vi B2: B6. Số .Pv có nghĩa là STDEV.P được sử dụng trên tất cả các điểm, tức là toàn bộ dân số chứ không chỉ là một tập hợp con.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Điều này sẽ tính toán tỷ lệ phần trăm tích lũy trong phạm vi B2: B6 dựa trên điểm SAT, trong trường hợp này là B2. Ví dụ: 83 phần trăm điểm số dưới điểm số của Walker.
Vẽ đồ thị kết quả
Đặt kết quả vào biểu đồ giúp dễ hiểu kết quả hơn, ngoài ra bạn có thể hiển thị kết quả đó trong bản trình bày để làm cho quan điểm của bạn rõ ràng hơn.
Học sinh ở trục ngang và điểm SAT của họ được hiển thị dưới dạng biểu đồ thanh màu xanh theo tỷ lệ (trục dọc) từ 1.600 đến 2.300.
Xếp hạng phần trăm là trục dọc bên phải từ 0 đến 90 phần trăm và được biểu thị bằng đường màu xám.
Cách tạo biểu đồ
Tạo biểu đồ là một chủ đề cho chính nó, tuy nhiên chúng tôi sẽ giải thích ngắn gọn về cách tạo biểu đồ trên.
Đầu tiên, chọn phạm vi các ô sẽ có trong biểu đồ. Trong trường hợp này từ A2 đến C6 vì chúng tôi muốn các số cũng như tên của học sinh.
Từ menu Chèn Chèn, hãy chọn Char Charts -> Biểu đồ được đề xuất
Máy tính đề xuất một biểu đồ Cột cụm, cột trục thứ cấp. Phần Trục thứ cấp có nghĩa là nó vẽ hai trục dọc. Trong trường hợp này, biểu đồ này là biểu đồ chúng tôi muốn. Chúng ta không phải làm gì khác.
Bạn có thể sử dụng di chuyển biểu đồ xung quanh và đặt lại kích thước cho đến khi bạn có nó theo kích thước và ở vị trí bạn muốn. Khi bạn hài lòng, bạn có thể lưu biểu đồ trong bảng tính.
Nếu bạn nhấp chuột phải vào biểu đồ, sau đó chọn Chọn dữ liệu, thì nó sẽ hiển thị cho bạn dữ liệu nào được chọn cho phạm vi.
Tính năng Biểu đồ được đề xuất của Cốt truyện thường giúp bạn không phải đối phó với các chi tiết phức tạp như xác định dữ liệu cần bao gồm, cách gán nhãn và cách gán trục dọc trái và phải.
Trong hộp thoại Chọn dữ liệu Nguồn, hãy nhấp vào điểm số của điểm trong mục Legend Legend Entries (Sê-ri) và nhấn Chỉnh sửa, xóa và thay đổi nó để nói Điểm số.
Sau đó đổi sê-ri 2 (Phần trăm phần trăm trực tuyến) thành Phần trăm phần trăm.
Quay trở lại biểu đồ của bạn và nhấp vào Bảng xếp hạng Tiêu đề trực tuyến và thay đổi nó thành Điểm SAT SAT. Hiện tại chúng tôi có một biểu đồ hoàn chỉnh. Nó có hai trục ngang: một cho điểm SAT (màu xanh) và một cho phần trăm tích lũy (màu cam).
Ví dụ: Vấn đề giao thông
Vấn đề vận chuyển là một ví dụ kinh điển về một loại toán học gọi là lập trình tuyến tính. Trực tiếp Điều này cho phép bạn tối đa hóa hoặc tối thiểu hóa một giá trị theo các ràng buộc nhất định. Nó có nhiều ứng dụng cho nhiều vấn đề kinh doanh, vì vậy rất hữu ích khi tìm hiểu cách thức hoạt động của nó.
Trước khi bắt đầu với ví dụ này, chúng tôi phải kích hoạt Bộ giải Excel Excel.
Kích hoạt bổ trợ bộ giải
Chọn tập tin lâm sàng -> Tùy chọn của bạn Ở dưới cùng của các tùy chọn bổ trợ, nhấp vào nút Go Go bên cạnh Quản lý: Bổ trợ Excel.
Trên menu kết quả, nhấp vào hộp kiểm để bật, Bổ trợ Solver, bổ trợ và nhấp vào OK OK.
Ví dụ: Tính chi phí vận chuyển iPad thấp nhất
Giả sử chúng tôi đang vận chuyển iPad và chúng tôi đang cố gắng lấp đầy các trung tâm phân phối của mình bằng cách sử dụng chi phí vận chuyển thấp nhất có thể. Chúng tôi có thỏa thuận với một công ty vận tải và hàng không để vận chuyển iPad từ Thượng Hải, Bắc Kinh và Hồng Kông đến các trung tâm phân phối được hiển thị bên dưới.
Giá để vận chuyển mỗi iPad là khoảng cách từ nhà máy đến trung tâm phân phối đến nhà máy chia cho 20.000 km. Ví dụ: cách Thượng Hải đến Melbourne là 8,024 km, là 8,024 / 20.000 hoặc 0,40 đô la cho mỗi iPad.
Câu hỏi đặt ra là làm thế nào để chúng tôi vận chuyển tất cả các iPad này từ ba nhà máy này đến bốn điểm đến này với chi phí thấp nhất có thể?
Như bạn có thể tưởng tượng, việc tìm ra điều này có thể rất khó khăn nếu không có một số công thức và công cụ. Trong trường hợp này, chúng tôi phải gửi 462.000 (F12) tổng số iPad. Các nhà máy có công suất giới hạn 500.250 (G12).
Trong bảng tính, để bạn có thể thấy nó hoạt động như thế nào, chúng tôi đã nhập 1 vào ô B10 có nghĩa là chúng tôi muốn gửi 1 iPad từ Thượng Hải đến Melbourne. Vì chi phí vận chuyển dọc theo tuyến đường đó là 0,40 đô la cho mỗi iPad, tổng chi phí (B17) là 0,40 đô la.
Số được tính bằng cách sử dụng hàm = SUMPRODVEL (chi phí, đã giao hàng) Chi phí trực tuyến là các phạm vi B3: E5.
Và các tàu được vận chuyển trên phạm vi B9: E11:
TỔNG HỢP nhân lên nhiều lần chi phí của chúng tôi, lần này, phạm vi nhiều lần, trong đó Cái đó được gọi là phép nhân ma trận.
Để SUMPRODVEL hoạt động chính xác, hai ma trận - chi phí và vận chuyển - phải có cùng kích thước. Bạn có thể khắc phục giới hạn này bằng cách tạo thêm chi phí và vận chuyển cột và hàng với giá trị bằng 0 để các mảng có cùng kích thước và không có tác động đến tổng chi phí.
Sử dụng bộ giải
Nếu tất cả những gì chúng ta phải làm là nhân lên các ma trận, chi phí, thời gian, các lần vận chuyển, điều đó sẽ không quá phức tạp, nhưng chúng ta cũng phải đối phó với các ràng buộc ở đó.
Chúng tôi phải vận chuyển những gì mỗi trung tâm phân phối yêu cầu. Chúng tôi đặt hằng số đó vào bộ giải như thế này: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Điều này có nghĩa là tổng của những gì được vận chuyển, tức là, tổng số trong các ô $ B $ 12: $ E $ 12, phải lớn hơn hoặc bằng với những gì mỗi trung tâm phân phối yêu cầu ($ B $ 13: $ E $ 13).
Chúng tôi không thể vận chuyển nhiều hơn chúng tôi sản xuất. Chúng tôi viết các ràng buộc như thế này: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Bây giờ, hãy chuyển đến trình đơn Dữ liệu và đặt nút. Nếu không có nút Sol Solver, bạn cần bật bổ trợ Bộ giải.
Nhập vào hai ràng buộc được nêu chi tiết trước đó và chọn phạm vi của Ship Shipments, đó là phạm vi số mà chúng tôi muốn Excel tính toán. Đồng thời chọn thuật toán mặc định, đơn giản, LP đơn giản và chỉ ra rằng chúng tôi muốn giảm thiểu tối đa các tế bào B15 (Tổng chi phí vận chuyển), trong đó nói là Set Set Objective.
Nhấn vào Sol Solvvvv và Excel lưu kết quả vào bảng tính, đó là những gì chúng ta muốn. Bạn cũng có thể lưu cái này để bạn có thể chơi xung quanh với các tình huống khác.
Nếu máy tính nói rằng nó không thể tìm ra giải pháp, thì bạn đã làm điều gì đó không logic, chẳng hạn, bạn có thể đã yêu cầu nhiều iPad hơn các nhà máy có thể sản xuất.
Ở đây Excel đang nói rằng nó tìm thấy một giải pháp. Nhấn nút OK OK để giữ giải pháp và quay lại bảng tính.
Ví dụ: Giá trị hiện tại ròng
Làm thế nào để một công ty quyết định có nên đầu tư vào một dự án mới? Nếu giá trị hiện tại ròng của người Viking (NPV) dương, họ sẽ đầu tư vào đó. Đây là một cách tiếp cận tiêu chuẩn được thực hiện bởi hầu hết các nhà phân tích tài chính.
Ví dụ, giả sử công ty khai thác Codelco muốn mở rộng mỏ đồng Andinas. Cách tiếp cận tiêu chuẩn để xác định xem có nên tiến lên với một dự án hay không là tính giá trị hiện tại ròng. Nếu NPV lớn hơn 0, thì dự án sẽ có lãi với hai đầu vào (1) thời gian và (2) chi phí vốn.
Nói một cách dễ hiểu, chi phí vốn có nghĩa là số tiền đó sẽ kiếm được bao nhiêu nếu họ chỉ để nó trong ngân hàng. Bạn sử dụng chi phí vốn để chiết khấu giá trị tiền mặt để hiện giá trị, nói cách khác, 100 đô la trong năm năm có thể là 80 đô la ngày hôm nay.
Trong năm đầu tiên, 45 triệu đô la được dành làm vốn để tài trợ cho dự án. Các kế toán đã xác định rằng chi phí vốn của họ là sáu phần trăm.
Khi họ bắt đầu khai thác, tiền mặt bắt đầu xuất hiện khi công ty tìm và bán đồng mà họ sản xuất. Rõ ràng, họ càng khai thác nhiều, họ càng kiếm được nhiều tiền và dự báo của họ cho thấy dòng tiền của họ tăng lên cho đến khi đạt tới 9 triệu đô la mỗi năm.
Sau 13 năm, NPV là 3.945.074 USD, vì vậy dự án sẽ có lãi. Theo các nhà phân tích tài chính, thời gian hoàn vốn của hồi giáo là 13 năm.
Tạo bảng Pivot
Một bảng trụ cột của người Viking về cơ bản là một báo cáo. Chúng tôi gọi chúng là các bảng trụ vì bạn có thể dễ dàng chuyển đổi chúng thành một loại báo cáo khác mà không phải thực hiện toàn bộ báo cáo mới. Vì vậy họ trục tại chỗ Hãy cho một ví dụ cơ bản dạy các khái niệm cơ bản.
Ví dụ: Báo cáo bán hàng
Nhân viên bán hàng rất cạnh tranh (đó là một phần của việc bán hàng) vì vậy họ tự nhiên muốn biết họ đối đầu với nhau như thế nào vào cuối quý và cuối năm, cộng với số tiền hoa hồng của họ sẽ là bao nhiêu.
Giả sử chúng ta có ba nhân viên bán hàng - Carlos, Fred và Julie - tất cả đều bán xăng dầu. Doanh thu của họ tính bằng đô la trên mỗi quý tài chính cho năm 2014 được hiển thị trong bảng tính bên dưới.
Để tạo các báo cáo này, chúng tôi tạo một bảng trụ cột:
Chọn Chèn Chèn -> Bảng Pivot, nó nằm ở phía bên trái của thanh công cụ:
Chọn tất cả các hàng và cột (bao gồm tên nhân viên bán hàng) như dưới đây:
Hộp thoại bảng xoay xuất hiện ở phía bên phải của bảng tính.
Nếu chúng tôi nhấp vào tất cả bốn trường trong hộp thoại bảng xoay vòng (Quý, Năm, Bán hàng và Nhân viên bán hàng) thì Excel sẽ thêm một báo cáo vào bảng tính không có ý nghĩa, nhưng tại sao?
Như bạn có thể thấy, chúng tôi đã chọn tất cả bốn trường để thêm vào báo cáo. Hành vi mặc định của Excel là nhóm các hàng theo trường văn bản và sau đó tổng hợp tất cả các hàng còn lại.
Ở đây nó cho chúng ta tổng của năm 2014 + 2014 + 2014 + 2014 = 24.168, điều này là vô nghĩa. Ngoài ra, nó là tổng của các phần tư 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Chúng tôi không cần thông tin này, vì vậy chúng tôi bỏ chọn các trường này để xóa chúng khỏi bảng xoay vòng của chúng tôi.
Tuy nhiên, Sum Sum của Sales Sales (tổng doanh số) là thích hợp, vì vậy chúng tôi sẽ khắc phục điều đó.
Ví dụ: Bán hàng bởi nhân viên bán hàng
Bạn có thể chỉnh sửa Sum Sum của Sales Sales để nói rằng Tổng doanh số, mà rõ ràng hơn. Ngoài ra, bạn có thể định dạng các ô là tiền tệ giống như bạn định dạng bất kỳ ô nào khác. Lần đầu tiên nhấp vào Sum Sum của Sales Sales và chọn Cài đặt trường giá trị.
Trên hộp thoại kết quả, chúng tôi đổi tên thành Tổng doanh số bán hàng, sau đó nhấp vào Định dạng số Số và thay đổi thành Tiền tệ.
Sau đó, bạn có thể thấy công việc của mình trong bảng xoay vòng:
Ví dụ: Bán hàng theo nhân viên bán hàng và quý
Bây giờ hãy thêm tổng phụ cho mỗi quý. Để thêm các tổng số phụ, chỉ cần nhấp chuột trái vào trường Khu phố vụn và giữ và kéo nó vào phần hàng của các hàng. Bạn có thể thấy kết quả trên ảnh chụp màn hình bên dưới:
Trong khi chúng ta đang ở đó, hãy xóa các giá trị của Sum Sum của Quý. Chỉ cần nhấp vào mũi tên và nhấp vào Xóa Xóa Trường. Trực tiếp trong ảnh chụp màn hình, bây giờ bạn có thể thấy chúng tôi đã thêm các hàng của Quý Quý, phân chia doanh số của mỗi nhân viên bán hàng theo quý.
Với những kỹ năng mới mẻ này, giờ đây bạn có thể tạo các bảng trụ từ dữ liệu của riêng bạn!
Phần kết luận
Kết thúc, chúng tôi đã cho bạn thấy một số tính năng của các công thức và chức năng của Microsoft Excel mà bạn có thể áp dụng Microsoft Excel cho các nhu cầu kinh doanh, học thuật hoặc các nhu cầu khác của mình.
Như bạn đã thấy, Microsoft Excel là một sản phẩm khổng lồ với rất nhiều tính năng mà hầu hết mọi người, ngay cả người dùng cao cấp, không biết tất cả chúng. Một số người có thể nói rằng làm cho nó phức tạp; chúng tôi cảm thấy nó toàn diện hơn.
Hy vọng, bằng cách trình bày cho bạn nhiều ví dụ thực tế, chúng tôi đã chứng minh không chỉ các chức năng có sẵn trong Microsoft Excel mà còn dạy cho bạn một vài điều về thống kê, lập trình tuyến tính, tạo biểu đồ, sử dụng số ngẫu nhiên và các ý tưởng khác mà bạn hiện có thể áp dụng và sử dụng trong trường học của bạn hoặc nơi bạn làm việc.
Hãy nhớ rằng, nếu bạn muốn quay lại và tham gia lớp học một lần nữa, bạn có thể bắt đầu làm mới với Bài học 1!
