TRƯỜNG HƯỚNG DẪN
- Tại sao bạn cần công thức và chức năng?
- Định nghĩa và tạo công thức
- Tham khảo và định dạng ô tương đối và tuyệt đối
- Các chức năng hữu ích mà bạn nên biết
- Tra cứu, biểu đồ, thống kê và bảng tổng hợp
Những 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à ví dụ minh họa chức năng tra cứu dọc (VLOOKUP) và tra cứu ngang (HLOOKUP). Các chức năng này được sử dụng để dịch một số hoặc giá trị khác thành một thứ có thể hiểu được. Ví dụ, bạn có thể sử dụng VLOOKUP để lấy một số phần và trả về mô tả mục.
Để điều tra điều này, hãy quay lại bảng tính “Trình ra quyết định” của chúng tôi trong Phần 4, nơi Jane đang cố gắng quyết định điều gì sẽ mang đến trường. Cô ấy không còn quan tâm đến những gì cô ấy mặc, vì cô ấy đã hạ cánh một người 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 trong cột và giày dọc, cột ngang.
Cô sử dụng hàm RANDBETWEEN (1,5) để chọn trong số năm loại giày.
Vì Jane không thể mặc số, chúng tôi cần chuyển đổi số 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 hàm VLOOKUP để dịch số trang phục sang 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:
Tiếp theo công thức dịch 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 kỹ thuật tương 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 số liệu thống kê quan trọng khác cho 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 họ xếp hạng như thế nào 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à các trường đại học uy tín, từ chối học sinh có điểm SAT thấp.
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.
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 cách phân tán rộng rãi một tập hợp các số. Nếu độ lệch chuẩn lớn, thì các con số cách xa nhau và nếu nó 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ị được quan sát, tức là 1,998 và mỗi điểm SAT. Xin lưu ý, thường là viết tắt độ lệch chuẩn bằng cách sử dụng ký hiệu sigma của Hy Lạp “σ.”
Xếp hạng phần trăm - Khi một sinh viên nhận được điểm số cao, họ có thể khoe khoang rằng họ đang ở trong top 99 phần trăm hoặc một cái gì đó như thế. "Xếp hạng phần trăm" có nghĩa là phần trăm điểm số thấp hơn một điểm 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 đối với mỗi độ lệch chuẩn, xác suất hoặc khả năng số đó nằm bên trong số độ lệch chuẩn đó là:
| STD | Tỷ lệ phần trăm điểm số | 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, cơ hội mà bất kỳ điểm SAT nào nằm ngoài 3 STD đều bằng 0, bởi vì 99,73% điểm số nằm trong 3 STD.
Bây giờ, hãy xem lại bảng tính và giải thích cách hoạt động của bảng tính.
= AVERAGE (B2: B6)
= STDEV.P (B2: B6)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Vẽ đồ thị các kết quả
Đặt kết quả vào biểu đồ giúp dễ hiểu kết quả hơn, cộng với bạn có thể hiển thị kết quả trong bản trình bày để làm cho điểm của bạn rõ ràng hơn.
Xếp hạng phần trăm là trục tung 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 một 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 cách biểu đồ trên được tạo ra.
Đầu tiên, chọn phạm vi ô trong biểu đồ. Trong trường hợp này là A2 đến C6 vì chúng tôi muốn các con số cũng như tên của học sinh.
Máy tính đề xuất biểu đồ “Nhóm-Cột, Trục phụ”. Phần “Trục phụ” có nghĩa là nó vẽ hai trục thẳng đứng. Trong trường hợp này, biểu đồ này là biểu đồ mà chúng tôi muốn. Chúng ta không phải làm gì khác.
Tính năng "Biểu đồ được đề xuất" thường không cho bạn xử lý 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 và trục dọc.
Trong hộp thoại “Chọn nguồn dữ liệu”, nhấp vào “điểm” trong “Mục nhập truyền thuyết (Series)” và nhấn “Chỉnh sửa” và thay đổi thành “Điểm”.
Ví dụ: Sự cố Giao thông
Vấn đề vận chuyển là một ví dụ điển hình của một loại toán học được gọi là "lập trình tuyến tính." Điều này cho phép bạn tối đa hóa hoặc giảm thiểu một giá trị tùy thuộc vào 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 hoạt động của nó.
Trước khi chúng ta bắt đầu với ví dụ này, chúng ta phải kích hoạt “Excel Solver”.
Bật trình bổ sung bộ giải
Chọn “File” -> “Options” -> “Add-ins”. Ở cuối các tùy chọn bổ trợ, nhấp vào nút "Go" bên cạnh "Quản lý: Bổ trợ Excel".
Trên menu kết quả, hãy nhấp vào hộp kiểm để bật, "Trình bổ trợ Solver" và nhấp vào "OK".
Ví dụ: Tính toán 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 bằ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 hàng không chuyên chở 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 chiếc iPad là khoảng cách từ nhà máy đến trung tâm phân phối đến nhà máy được chia cho 20.000 km. Ví dụ, nó là 8.024 km từ Thượng Hải đến Melbourne là 8.024 / 20.000 hoặc $.40 cho mỗi iPad.
Như bạn có thể hình dung, việc tìm ra điều này có thể rất khó mà không cần một số công thức và công cụ. Trong trường hợp này, chúng tôi phải xuất xưởng tổng cộng 462.000 (F12) iPad. Các nhà máy có công suất giới hạn 500.250 (G12).
Sử dụng bộ giải
Nếu tất cả những gì chúng ta phải làm là nhân các ma trận “chi phí” lần “vận chuyển” mà không quá phức tạp, nhưng chúng ta cũng phải đối phó với những ràng buộc ở đó.
Chúng tôi phải gửi 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 quyết như thế này: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Điều này có nghĩa là tổng số tiền đượ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 số mà mỗi trung tâm phân phối yêu cầu ($ B $ 13: $ E $ 13).
Nhập vào hai ràng buộc được nêu chi tiết trước đó và chọn phạm vi “Lô hàng”, là phạm vi số mà chúng tôi muốn Excel tính toán. Cũng chọn thuật toán mặc định "Simplex LP" và cho biết rằng chúng tôi muốn "thu nhỏ" ô B15 ("tổng chi phí vận chuyển"), nơi nó nói "Đặt mục tiêu".
Nếu máy tính nói rằng nó không thể tìm thấy một giải pháp, thì bạn đã làm điều gì đó không hợp lý, ví dụ, bạn có thể đã yêu cầu nhiều iPad hơn so với các nhà máy có thể sản xuất.
Ở đây Excel nói rằng nó tìm thấy một giải pháp. Nhấn “OK” để giữ lại giải pháp và quay lại bảng tính.
Ví dụ: Giá trị hiện tại thuần
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” (NPV) là dương, họ sẽ đầu tư vào nó. Đâ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 trước một dự án hay không là tính toán giá trị hiện tại ròng. Nếu NPV lớn hơn 0, thì dự án sẽ sinh lợi cho hai đầu vào (1) thời gian và (2) chi phí vốn.
Bằng tiếng Anh đơn giản, chi phí vốn có nghĩa là số tiền đó sẽ kiếm được bao nhiêu nếu họ chỉ để lại nó trong ngân hàng. Bạn sử dụng chi phí vốn để giảm giá trị tiền mặt để hiện tại giá trị, nói cách khác $ 100 trong năm năm có thể là $ 80 ngày hôm nay.
Trong năm đầu tiên, 45 triệu đô la được dành để đầu tư vốn 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.
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” là 13 năm.
Tạo bảng tổng hợp
Một "bảng tổng hợp" về cơ bản là một báo cáo. Chúng tôi gọi chúng là các bảng tổng hợp vì bạn có thể dễ dàng chuyển đổi chúng thành một loại báo cáo sang một loại báo cáo khác mà không phải tạo một báo cáo hoàn toàn mới. Vì vậy họ trục tại chỗ. Chúng ta hãy cho thấy 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 trở thành một nhân viên bán hàng) để 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ọ bằng đô la cho mỗi quý tài chính cho năm 2014 được hiển thị trong bảng tính bên dưới.
Chọn “Insert -> Pivot Table, nó nằm ở phía bên tay trái của thanh công cụ:
Nếu chúng ta nhấp vào tất cả bốn trường trong hộp thoại bảng tổng hợp (Quý, Năm, Bán hàng và Nhân viên bán hàng), Excel sẽ thêm báo cáo vào bảng tính không có ý nghĩa, nhưng tại sao?
Ở đây nó cho chúng ta tổng của năm 2014 + 2014 + 2014 + 2014 = 24,168, điều đó là vô nghĩa. Ngoài ra, số tiền đó là tổng của các khu 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 tổng hợp của chúng tôi.
Ví dụ: Sales by Salesman
Bạn có thể chỉnh sửa “Tổng doanh thu” để nói “Tổng doanh thu”, rõ ràng hơn. Ngoài ra, bạn có thể định dạng các ô dưới dạng tiền tệ giống như bạn định dạng bất kỳ ô nào khác. Đầu tiên, hãy nhấp vào “Tổng doanh thu” và chọn “Cài đặt trường giá trị”.
Ví dụ: Bán hàng theo Người bán hàng và Khu phố
Bây giờ, hãy thêm tổng phụ cho từng quý. Để thêm tổng phụ chỉ cần nhấp chuột trái vào trường "Quý" và giữ và kéo nó vào phần "hàng". Bạn có thể xem kết quả trên ảnh chụp màn hình bên dưới:
Phần kết luận
Tóm lại, 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 tập hoặc nhu cầu khác của bạn.
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, thậm chí là 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 rằng, bằng cách giới thiệu cho bạn rất nhiều ví dụ thực tế, chúng tôi đã không chỉ trình bày các chức năng sẵn có trong Microsoft Excel mà còn dạy bạn điều gì đó 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 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 trở lại và tham gia lớp học một lần nữa, bạn có thể bắt đầu mới với Bài học 1!
