Thực đơn
trang chủExcel

Đào tạo excel nâng cao. Làm việc trong Excel dành cho người dùng nâng cao

Đồng nghiệp!

Sự cần thiết phải tự động hóa tính toán, có tính đến lượng lớn dữ liệu nguồn được liên kết với tôi Hoạt động chuyên môn, tỏ ra rất quan tâm đến học excel, như những chương trình có khả năng to lớn.

Có rất nhiều thông tin trên Internet, đào tạo trực tuyến, hội thảo trên web và dạy học. Thành thật mà nói: hiệu quả nhất đối với tôi là học khóa học “MS Excel nâng cao”, khóa học này hóa ra có thể hiểu được ngay cả đối với một người “nghiệp dư”, ngắn gọn, chứa một lượng lớn tài liệu được tổ chức tốt để nghiên cứu. Riêng tôi xin nói về cách giảng bài của tác giả: đây là cách trình bày tài liệu bài giảng chu đáo, dễ hiểu, dễ nghe.

Thật khó để xác định chính xác khóa học đã giúp ích như thế nào, bởi vì mỗi mô-đun trong số 6 mô-đun đều đóng góp tích cực cho công việc của tôi: thực hiện các phép tính phức tạp, hợp nhất tệp, bảng tổng hợp, định dạng, v.v.

Cảm ơn bạn vì chuyến tham quan tuyệt vời vào không gian và khả năng vô tận chương trình excel. Ngoài ra, cảm ơn bạn vì đã cung cấp liên tục, hữu ích và không phô trương các ví dụ, bài học và thủ thuật để nghiên cứu và lặp lại tài liệu bạn đã học. Sự lặp lại là mẹ của việc học!

Tôi chúc bạn thành công, Dmitry, từ tận đáy lòng!

Trưởng phòng POS của một công ty logistics

Trong video bài học tác giả ngôn ngữ có thể truy cập trình bày tài liệu khóa học bằng cách sử dụng cụ thể ví dụ minh họa từ thực tiễn hàng ngày. Một lợi thế không thể phủ nhận Khóa học mang tính tương tác - đích thân tác giả kiểm tra bài tập về nhà đã hoàn thành, nếu cần, bạn có thể liên hệ với anh ấy để làm rõ các câu hỏi hoặc để lại tin nhắn trên diễn đàn. Dù tôi có đủ người dùng có kinh nghiệm Excel, trong tài liệu khóa học, tôi đã khám phá ra với sự ngạc nhiên không giấu giếm toàn bộ dòng kỹ thuật thực tế mà tôi hiện đang sử dụng trong công việc của mình. Một số kỹ thuật hiệu quả đến mức chúng có thể tiết kiệm hàng giờ, thậm chí cả ngày làm việc! Ngoài ra, trong suốt khóa học, tôi đã học được các sơ đồ mới mà trước đây tôi chưa từng nghe đến (thác nước, phễu bán hàng, Pareto, v.v.).

Nhà phân tích

MS Excel nâng cao là khóa học hữu ích dành cho những ai muốn đơn giản hóa công việc và tự động hóa nhiều quy trình trong thời gian ngắn. Rất định dạng thuận tiệnđào tạo: video + đính kèm tập tin excel, trong đó bạn có thể lặp lại những kiến ​​thức mới thu được trong thực tế. Các video có dung lượng nhỏ (bạn có thể xem trong giờ ăn trưa tại nơi làm việc) nhưng chúng trình bày thông tin ngắn gọn và rõ ràng.

Tôi nhận thấy mô-đun bảng tổng hợp đặc biệt hữu ích. Mặc dù thực tế là tôi đã có kinh nghiệm làm việc với họ nhưng tôi đã khám phá ra rất nhiều điều thông tin hữu ích: sử dụng các lát cắt, tạo bảng tóm tắt cho khách hàng trên tờ riêng biệt, áp dụng định dạng có điều kiện cho bảng tổng hợp, nhóm, sắp xếp, bảng tổng hợp, v.v.

Riêng tôi xin lưu ý bài “Mô hình dữ liệu (giảm Power Pivot): trước khi học bài này, tôi đã dành rất nhiều thời gian để kéo dữ liệu từ bảng này sang bảng khác bằng hàm VLOOKUP hoặc SUMIF. Bây giờ tôi có thể xây dựng bảng tổng hợp, được xây dựng từ hai hoặc nhiều bảng nguồn.

Các bài học về định dạng có điều kiện cũng rất hữu ích: trước khi xem những bài học này, tôi chỉ sử dụng công thức của riêng mình để xác định ô nào cần định dạng. Điều này không phải lúc nào cũng thuận tiện, vì hóa ra, chúng ta thường phải phát minh lại cái bánh xe. Bây giờ, trước khi viết công thức của riêng mình, tôi xem qua giải pháp làm sẵnđịnh dạng có điều kiện và thường tìm thấy những gì tôi cần ở đó.

Một khóa học tuyệt vời dành cho những ai muốn nâng cao kiến ​​thức về MS Excel và làm mới những kiến ​​thức hiện có.

Chuyên gia mua hàng hàng đầu

Microsoft Excel, có lẽ là phổ biến nhất chương trình văn phòng. Nó được sử dụng bởi tất cả mọi người từ tổng thống đến học sinh, vì vậy, bằng cách thành thạo công cụ này, bạn sẽ có thể nổi bật giữa các nhân viên văn phòng hoặc giúp công việc hàng ngày của bạn trở nên dễ dàng hơn.

Ngoài nhiều chức năng đơn giản, mà có thể bạn đã biết và sử dụng, sẽ rất hữu ích nếu bạn học một số thủ thuật “cho thú vị” giúp bạn đạt được thứ hạng cao hơn những người khác. Vì vậy, nếu bạn muốn thể hiện trước mặt sếp và đè bẹp đối thủ, 8 hàm Excel phức tạp này sẽ rất hữu ích.

    1. tổng 3D

Bây giờ trên tab TỔNG, chúng ta cần xem bạn đã chi tiêu bao nhiêu và vào ngày nào trong khoảng thời gian này. Chúng ta gõ =SUM('Week1:Week7'!B2) và công thức tính tổng tất cả các giá trị trong ô B2 trên tất cả các tab. Bây giờ, sau khi điền vào tất cả các ô, chúng tôi tìm ra ngày nào trong tuần chúng tôi chi tiêu nhiều nhất và cuối cùng cũng cộng tất cả chi phí của chúng tôi trong 7 tuần này.

    1. Hàm MATCH

Hàm MATCH tìm kiếm một phần tử được chỉ định trong một phạm vi (Phạm vi là hai hoặc nhiều ô trang tính. Các ô trong một phạm vi có thể là các ô liền kề hoặc không liền kề) và phản ánh vị trí tương đối của phần tử đó trong phạm vi.

Riêng biệt, INDEX và MATCH không đặc biệt hữu ích. Nhưng cùng nhau chúng có thể thay thế hàm VLOOKUP.

Ví dụ, để cái bàn lớnđể tìm người đứng đầu Wells Fargo, hãy viết =INDEX(A3:A11,MATCH("Wells Fargo",B3:B11,0).
Bằng cách sử dụng Hàm VLOOKUPđiều này không thể thực hiện được vì nó chỉ tìm kiếm từ trái sang phải. Và sự kết hợp của hai điều cuối cùng cho phép bạn thực hiện điều này một cách dễ dàng.

Một trong những điều nhất chức năng tiện lợi trong kho vũ khí của Excel và một trong những cách đơn giản nhất là ký hiệu $. Nó cho chương trình biết những gì không nên làm điều chỉnh tự động công thức khi sao chép vào tế bào mới, như Excel thường làm.

Trong trường hợp này, dấu $ ở phía trước “A” ngăn chương trình thay đổi công thức theo chiều ngang và phía trước “1” – theo chiều dọc. Nếu bạn viết “$A$1”, thì giá trị của ô được sao chép sẽ giống nhau theo mọi hướng. Rất tiếp tân thuận tiện khi bạn phải làm việc với cơ sở dữ liệu lớn dữ liệu.

    1. Lựa chọn tham số

Không có cái này Hàm Excelđội ngũ các nhà phân tích, tư vấn và dự báo sẽ gặp khó khăn. Hãy hỏi bất kỳ ai trong lĩnh vực tư vấn hoặc bán hàng và họ sẽ cho bạn biết tính năng Excel này hữu ích như thế nào.

Ví dụ: bạn đang bán một trò chơi điện tử mới và bạn cần tìm hiểu xem người quản lý của bạn cần bán bao nhiêu bản trong tháng thứ ba để kiếm được 100 triệu đô la. Để thực hiện việc này, trong menu “Công cụ”, hãy chọn chức năng “Lựa chọn tham số”. Chúng tôi muốn ô Tổng doanh thu hiển thị giá trị 100 triệu USD. Trong trường ô đã đặt, hãy chỉ ra ô trong đó tổng cộng, trong trường giá trị – số tiền mong muốn và trong ô Thay đổi, chọn ô nơi số lượng hàng bán được trong tháng thứ ba sẽ được hiển thị. Và Voila! – chương trình đã đối phó. Các tham số và giá trị trong các ô có thể dễ dàng thao tác để có được kết quả như mong muốn.

    1. hàm VLOOKUP

Chức năng này cho phép bạn nhanh chóng tìm thấy giá trị bạn cần trong bảng. Ví dụ: chúng ta cần biết điểm cuối cùng của Bette, chúng ta viết: =VLOOKUP(“Beth”,A2:E6,5,0), trong đó Beth là tên học sinh, A2:E6 là dãy bảng, 5 là số cột và 0 có nghĩa là chúng tôi không tìm kiếm kết quả khớp chính xác với ý nghĩa.

Chức năng này rất tiện lợi, nhưng bạn cần biết một số tính năng sử dụng của nó. Đầu tiên, VLOOKUP chỉ tìm kiếm từ trái sang phải, vì vậy nếu cần tìm kiếm theo thứ tự khác, bạn sẽ phải thay đổi cài đặt sắp xếp của toàn bộ trang tính. Ngoài ra nếu bạn chọn quá cái bàn lớn, việc tìm kiếm có thể mất nhiều thời gian.

Nếu bạn muốn thu thập tất cả các giá trị từ các tế bào khác nhau thành một, bạn có thể sử dụng hàm CONCATENATE. Nhưng tại sao phải gõ nhiều chữ cái như vậy khi bạn có thể thay thế chúng bằng “&”.

    1. Mảng

Để tạo một mảng hoặc ma trận, bạn sẽ cần một số thao tác, nhưng chúng phức tạp hơn so với các công thức thông thường, vì việc hiển thị kết quả không yêu cầu một mà là nhiều ô.

Ví dụ: hãy nhân hai ma trận. Để thực hiện việc này, hãy sử dụng hàm MULTIPLE (Mảng 1, Mảng 2). Điều quan trọng là đừng quên đóng công thức dấu ngoặc đơn. Bây giờ hãy nhấn tổ hợp phím Ctrl+Shift+Enter và Excel sẽ hiển thị kết quả của phép nhân dưới dạng ma trận. Điều tương tự cũng xảy ra với các hàm khác hoạt động với mảng - thay vì chỉ nhấn Enter để nhận kết quả, hãy sử dụng Ctrl+Shift+Enter.

    1. hàm CHỈ SỐ

Phản ánh giá trị hoặc tham chiếu ô tại giao điểm của một hàng và cột cụ thể trong phạm vi ô đã chọn. Ví dụ: để xem ai đứng thứ tư trong danh sách những nhà quản lý hàng đầu được trả lương cao nhất ở Phố Wall, chúng ta nhập: =INDEX(A3:A11, 4).

Văn phòng tích hợp sản phẩm của Microsoft Excel thuận tiện cho việc tạo cơ sở dữ liệu và duy trì hồ sơ hoạt động. Các tiện ích bổ sung của chương trình cung cấp cho người dùng khả năng người dùng nâng cao tự động hóa công việc và loại bỏ lỗi bằng cách sử dụng macro.

TRONG đánh giá này coi như các tính năng hữu ích Các chương trình Excel mà người dùng nâng cao sử dụng để giải quyết Các nhiệm vụ khác nhau. Chúng ta sẽ học cách làm việc với cơ sở dữ liệu trong Excel. Hãy cùng tìm hiểu cách sử dụng macro trong thực tế. Chúng tôi cũng sẽ xem xét việc sử dụng tính năng chia sẻ tài liệu cho công việc chung (nhiều người dùng).

Cách làm việc với cơ sở dữ liệu trong Excel

Cơ sở dữ liệu (DB) là một bảng có một bộ nhất định thông tin (cơ sở dữ liệu khách hàng, kiểm kê kho, kế toán thu nhập và chi phí, v.v.). Dạng biểu diễn này thuận tiện cho việc sắp xếp theo tham số, tìm kiếm nhanh, tính toán các giá trị theo tiêu chí nhất định, v.v.

Ví dụ: hãy tạo cơ sở dữ liệu trong Excel.

Thông tin được nhập thủ công. Sau đó, chúng tôi đánh dấu phạm vi dữ liệu và định dạng nó "dưới dạng bảng". Trước tiên, có thể đặt phạm vi cho cơ sở dữ liệu (“Chèn” - “Bảng”). Và sau đó nhập dữ liệu.

Tìm thông tin bạn cần trong cơ sở dữ liệu

Chọn Menu chính – tab “Chỉnh sửa” – “Tìm” (ống nhòm). Hoặc nhấn tổ hợp phím nóng Phím shift+ F5 hoặc Ctrl + F. Trong thanh tìm kiếm, nhập giá trị mong muốn. Bằng cách sử dụng của nhạc cụ này Bạn có thể thay thế một tên giá trị trong toàn bộ cơ sở dữ liệu bằng một tên giá trị khác.



Hãy sắp xếp các giá trị tương tự trong cơ sở dữ liệu

Cơ sở dữ liệu của chúng tôi được biên soạn theo nguyên tắc “bảng thông minh” - có một mũi tên ở góc dưới bên phải của mỗi thành phần tiêu đề. Nó có thể được sử dụng để sắp xếp các giá trị.

Hãy trưng bày hàng hóa đang có tại kho số 3. Click vào mũi tên ở góc tên “Warehouse”. Chọn giá trị mong muốn từ danh sách thả xuống. Sau khi nhấn OK chúng ta truy cập được thông tin về kho số 3. Nhưng chỉ.

Hãy cùng tìm hiểu những sản phẩm nào có giá dưới 100 rúp. Nhấp vào mũi tên bên cạnh “Giá”. Chọn “Bộ lọc số” - “Nhỏ hơn hoặc bằng”.



Đặt tham số sắp xếp.

Sau khi nhấn OK:

Ghi chú. Bằng cách sử dụng bộ lọc tự động tùy chỉnh, bạn có thể đặt đồng thời một số điều kiện để sắp xếp dữ liệu trong cơ sở dữ liệu.

Hãy tìm kết quả trung gian

Hãy tính tổng giá vốn hàng hóa ở kho số 3.

Bằng cách sử dụng bộ lọc tự động, chúng tôi sẽ hiển thị thông tin trên đến kho này(xem ở trên).

Trong cột “Chi phí”, hãy nhập công thức: =INTERMEDIATE TOTAL(9;E4:E41), trong đó 9 là số hàm (trong ví dụ của chúng tôi là SUM), E4:E41 là phạm vi giá trị.



Lưu ý mũi tên bên cạnh kết quả công thức:

Nó có thể được sử dụng để thay đổi hàm SUM.

vẻ đẹp là gì phương pháp này: nếu thay đổi kho chúng ta sẽ nhận được tổng giá trị mới (theo dãy mới). Công thức vẫn giữ nguyên - chúng tôi chỉ thay đổi các tham số của bộ lọc tự động.



Cách làm việc với macro trong Excel

Macro được thiết kế để tự động hóa công việc thường ngày. Đây là những hướng dẫn truyền đạt quá trình hành động để đạt được một mục tiêu cụ thể.

Có rất nhiều macro trong truy cập mở. Bạn có thể sao chép và dán những nội dung này vào sổ làm việc của mình (miễn là các hướng dẫn hoàn thành mục đích đã định). Chúng ta hãy nhìn vào ví dụ đơn giản Cách tự ghi macro.

Hãy tạo một hướng dẫn sẽ tự động tính toán chi phí cho đơn đặt hàng của khách hàng:

  1. Hãy sao chép bảng sang một trang tính mới.
  2. Hãy loại bỏ dữ liệu số lượng. Nhưng hãy đảm bảo rằng các ô này có định dạng số không có chữ số thập phân (vì có thể đặt hàng riêng lẻ chứ không phải theo đơn vị khối lượng).
  3. Giá trị "Giá" phải ở định dạng tiền tệ.
  4. Hãy loại bỏ dữ liệu chi phí. Hãy nhập công thức vào cột: giá * số lượng. Và chúng ta sẽ nhân lên.
  5. Ở cuối bảng là “Tổng cộng” (có bao nhiêu đơn vị hàng hóa đã được đặt hàng và chi phí là bao nhiêu). Thậm chí thấp hơn - "Tổng cộng".

Bảng có dạng sau:

Bây giờ hãy dạy Microsoft Excel thực hiện một thuật toán nhất định.



Cách làm việc trên Excel cùng lúc cho nhiều người

Để nhiều người dùng có thể truy cập vào cơ sở dữ liệu trong Excel thì cơ sở dữ liệu đó phải được mở. Đối với phiên bản 2007-2010: “Đánh giá” - “Truy cập vào sách”.



Ghi chú. Vì phiên bản cũ 2003: “Dịch vụ” - “Truy cập vào cuốn sách.”

Nhưng! Nếu 2 người dùng trở lên thay đổi giá trị của cùng một ô trong khi truy cập tài liệu thì sẽ xảy ra xung đột quyền truy cập.



Hoặc thông tin người dùng nhập trước đó sẽ bị xóa khi người dùng tiếp theo nhập giá trị mới vào cùng ô.

Chương trình không chỉ tăng năng suất của người dùng mà còn theo dõi lỗi của họ!

Thật không may, có một số hạn chế trong chế độ nhiều người chơi. Ví dụ:

  • trang tính không thể bị xóa;
  • các tế bào không thể được kết hợp hoặc tách rời;
  • tạo và chỉnh sửa macro;
  • làm việc hạn chế với dữ liệu XML(nhập, xóa bản đồ, chuyển đổi ô thành phần tử, v.v.).

Nhưng về tổng thể chế độ này rất hấp dẫn đối với công việc lớn và thường xuyên, việc hoàn thành cùng nhau dễ dàng hơn và nhanh hơn. Quyền truy cập được chia sẻ làm tăng đáng kể năng suất của nhân viên trong văn phòng.

Trong ví dụ: chia sẻ file- Quá trình kết nối và làm việc ở chế độ nhiều người dùng được mô tả chi tiết hơn.