Module 1. ProgramMSExcel. Setting up the program window.
Table processor Microsoft Excel.
Purpose of the program.
Program interface.
Interface customization: quick access panels, ribbons, column header areas.
Scale.
Entering and editing information.
Entering information.
Editing data (replacing, deleting, adding, correcting errors).
Select columns, rows, cell ranges, unrelated cells, worksheet. Delete cell contents, copy, paste.
Working with sheets (inserting, moving, copying, deleting).
Changing the table structure.
Add and remove cells, rows and columns. Resizing columns and rows. Selecting cell width based on content. Setting the same width for several columns and rows. Merging cells.
Formatting cells.
Working with fonts, aligning cell contents, rotating text, adjusting indents.
Copy format according to sample. Format clearing.
Create borders for a table (using the borders button, the border tab, and drawing individual borders).
Ways to create a fill for cells.
Practical lesson
Module 2: Creating Number Sequences. Creating lists.
Restriction on data entry.
Imposing conditions on data input.
Generating an error message when entering data.
Generating a message for data entry.
Working with data formats.
Microsoft Excel data types and formats: text, numbers, dates, time, currency format. Their setup. Simplified date entry.
Problems in using formats.
Organization of calculations inExcel.
Entering a formula into a cell. Copying formulas using autofill.
Formulas with percentages. Absolute and relative links.
Preparing a document for printing.
Setting page parameters.
Creating Headers and Footers
Setting through rows and columns.
Pre-press preparation of the document. Using Preview.
Print the selected area. Setting the print area.
Practical lesson
Module 3. Functions.
Using simple functions (sum, average, minimum, maximum, number).
Ways to work with functions. Using Help. Entering functions using the Function Wizard. Categories of functions.
Logical functions: IF, AND, OR, IFERROR. Nested logical functions.
Mathematical and static functions: SUMISF, COUNTIF, SUMIFS, COUNTIF.
Text functions CONCATENATE, LEFT, uppercase, lowercase, PROP.
Array functions: GPR, VPR.
Solving practical problems using functions.
Copying calculation results and formulas using paste special.
Review of possible errors in formulas.
Practical lesson
Module 4. Diagrams. Graphics in documents.
Creating diagrams. Chart Wizard.
Setting chart properties.
Construction various types diagrams.
Print the chart.
Conditional formatting.
The concept of conditional formatting.
Create conditional formatting (comparing with a value, with the result of a formula or function).
Copy conditional formatting.
Editing and removing conditional formatting. Auto format.
Practical lesson
Module 5. Working with databases.
The concept of a database.
Sorting.
Removing duplicates.
Subtotals.
Hiding and showing data.
Freeze rows and columns when viewing.
Dividing the sheet into parts when viewing.
Data consolidation.
Applying filters. Selection of data through filtering.
Advanced filter. Creating conditions for an advanced filter.
Search and replace.
Pivot tables.
Creating pivot tables.
Using filters in pivot tables.
Changing formats for pivot tables.
Setting up pivot tables.
Editing a pivot table.
Updating pivot table data.
Turn totals on or off in pivot tables
Practical lesson
Module 6.Notes (creating, editing, deleting).
Creating hyperlinks. Data protection.
Setting a password to open a book.
Protecting the data entry sheet.
Permission to work with individual cells and prohibition of work on other cells.
Final examination.
Entry-level materials
To study these materials, sufficient preparation is to master the materials from the “Free video lessons” section. No other special training is required.
Master Class
Included:
Duration: 4 hours 38 minutes
Cost: 1000 rubles
Filling some tables with data located in other tables (often on other sheets or in other files) is one of the most typical labor-intensive tasks that can be easily automated. In many cases formulas like VLOOKUP, INDEX And SEARCH, but, unfortunately, not always.
This webinar shows a universal solution for automating table filling using Visual Basic.
this link
Master Class
Included: video recording + shown examples with open source code
Duration: 4 hours 37 min
Cost: 1000 rubles
Another of the most time-consuming and routine tasks when working in Excel is creating a “stack” of documents based on a certain template by filling its fields with data from the table. In most cases, this task can easily be automated - you just need to describe the algorithm for filling out the form once, and then it doesn’t matter whether you need to create 10 documents or 10,000 - the documents will be created themselves while you go about your business.
You can download the examples created in the master class using this link
Master Class
Included: video recording + shown examples with open source code
Duration: 3 h 6 min
Cost: 3000 rubles
We learn to write small but effective programs for everyday tasks: for selecting data from tables according to a condition, filling out a report based on several files, generating a stack of documents of the same type according to a template, speeding up filling out documents, etc.
- how to execute one or another command depending on the condition;
- how to repeat a set of actions certain number once;
- how to repeat a set of actions before the occurrence of a certain event;
- how to force a macro to be executed when an event occurs (when a value changes in a cell, double click, sheet activation, etc.);
- how to add a sheet to the end of a book;
- how to delete a sheet without a warning message;
- how to prompt the user to enter a value;
- how to check the correctness of the entered data;
- how to find out the number of the last filled row or column;
- how to determine whether a string contains certain characters;
- how to split a string into several substrings of a given length;
Master Class
Included: video recording + shown example with open source code
Duration: 2 hours 54 min
Cost: 3000 rubles
The master class is dedicated to creating a universal and convenient program, intended for testing knowledge in any area.
All questions for testing are asked in the form of a list on a separate sheet, which makes setting up the program very convenient: there is no need to create a separate sheet for each question (which is very inconvenient if there are a lot of questions).
It is possible to use pictures to illustrate the question (if necessary). In addition, you can use questions where the correct answer is to select several points from those proposed.
Master Class
Included: video recording + shown examples with open source code
Duration: more than 9 hours (!)
Cost: 4000 rubles
A large video reference that discusses 112 VBA functions in detail - with examples and explanations. This is about 95% of all functions available in the VBA language (only financial functions were not considered), and this is definitely more than you will ever need in practice.
The master class contains 110 (!) small programs - examples that demonstrate the use of each function in a given situation.
For convenience of presentation of the material, all functions are conditionally combined into the following groups:
- functions for working with text;
- functions for working with numbers;
- functions for working with dates;
- value type checking functions;
- functions for converting values from one type to another;
- functions for interactive work;
- conditional selection functions;
- functions for working with arrays;
- functions for working with text files;
- other functions.
Included: video recording + shown examples with open source code + tasks for independent implementation
Duration: 23 lessons of 1.5-2.5 hours
Cost: 6000 rubles / 12000 rubles
An offer for those who are serious about mastering automation skills in Excel and want to study the subject sequentially, step by step, with detailed explanations.
The training is divided into two blocks with the conventional names “Practitioner” and “Specialist”. Each of the two blocks is designed for approximately 2 weeks of classes.
Main block "Practician" contains the basic concepts, tools and techniques absolutely necessary to implement almost any task. This block discusses in detail topics such as:
- execution of commands based on conditions;
- performing repetitive actions (cycles);
- 3 variants of cyclic structures;
- organizing a dialogue between the program and the user (question with multiple answer options, requesting a parameter value);
- code debugging tools during development;
- existing data types and their differences;
- breaking code into procedures and functions;
- 2 types of software errors etc.
Block "Specialist" is intended for those who want to learn more serious things and make their programs more functional, convenient and professional. This block addresses the following issues:
- programmatic response to events (opening/closing a workbook, changing a value in a cell, double clicking, activating a sheet, etc.);
- software processing of potential errors;
- working with data arrays;
- use of the built-in help system;
- basics of working with user forms;
- automation of creation of pivot tables and their modification;
- mechanism for creating your own Excel add-in;
- programmatic work with protected sheets; etc.
The training includes several assignments for independent work, where you can apply the techniques shown in the relevant training sessions. The tasks are built on the principle of gradually increasing complexity: if completing more complex tasks causes difficulty, then completing simpler tasks in advance will help to solve them.
To date, several dozen people have completed the training. WITH feedback from participants You can read
Materials for users with automation skills
To study these materials, you need to have the skills to automate simple tasks and know concepts such as loops, conditions, arrays, variable declarations, event handling, error handling, and step-by-step debugging.
Master Class
Included: video recording + shown examples with open source code
Duration: 4 hours 43 minutes
Cost: 3000 rubles
Custom forms and controls are used to create convenient and clear interface user.
With the help of these visual tools, on the one hand, faster user work and a reduction in workload are achieved, on the other hand, a higher quality of work is achieved, since data entry using forms and elements allows you to control the correctness of the entered data. In addition, the use of beautiful and convenient forms for dialogue with the user is an indicator highly professional completed project.
From this master class you will learn:
- how to create custom forms in Excel files;
- how to open the form and set the initial values of the elements;
- how and when to check the correctness of the entered data;
- how to programmatically change the size and position of elements and shapes;
- how to establish the invisibility or inaccessibility of an object;
- how to include additional controls in the project that are not on the toolbar (for example, a date picker field);
- how to call the file opening dialog and set it to select by file type;
- how to fill a list/dropdown list with data from a worksheet;
- how to allow or prohibit the user from entering their own values in the drop-down list;
- how to allow the user to select several lines in the list at once and how to process the result of such a selection;
- how to get the value of a form element in a program after it is closed;
- how to programmatically insert a picture from a file and specify its exact location on the sheet and much more.
Master Class
Included: video recording + shown examples with open source code
Duration: 3 hours 39 minutes
Cost: 3000 rubles
Excel has tools that can significantly expand the functionality of user applications - making them more convenient, efficient (and impressive). We are talking about the ability to programmatically respond to certain events that occur while the user is working with a file.
Such events can be changing a value in a cell, double-clicking or right-clicking on a cell, switching between sheets or adding a new sheet to a workbook, opening, closing, saving a workbook or sending it to print, etc.
In this master class we will look at several practical examples used for a variety of purposes, but all these examples have in common the fact that they are based specifically on the processing of certain Excel events.
Event processing capabilities in Excel are discussed using the following examples:
- change appearance tables by double clicking on cells;
- setting up data validation in a cell depending on the value of another cell;
- displaying a progress bar to indicate the progress of lengthy processes;
- user authorization, recording the author and creation time for each table record.
Two-day master class
Included: video recording + shown examples with open source code
Duration: 2 lessons of 4 hours
Cost: 5000 rubles
Automated downloading of necessary information from the Internet is widely used as for one-time filling of a large database, so to update regularly changing indicators.
The most common examples include tasks such as:
- downloading prices for goods from price lists on suppliers’ (or competitors’) websites;
- rapid creation of databases based on information on various Internet resources (for example, directories of spare parts, medicines, etc.);
- obtaining contact information of a mass of people selected according to a certain criterion (for example, telephone numbers of persons who posted advertisements for the sale/purchase of housing, vehicles, etc.; or positions, full names and telephone numbers of managers of manufacturing or trading companies in a particular region);
- etc.
This master class examines in detail the technology of creating programs for collecting information from a site, discusses the main problems in their development and ways to solve them.
During the master class, we showed how to write 5 parsers to obtain information from sites with different structures - including such well-known portals as "Avito", "Yandex-Market", "From Hand to Hand".
Included: video recording + shown examples with open source code
Duration: 4 lessons of 2-2.5 hours
Cost: 7000 rubles
Sooner or later, when working in Excel, the need arises to automate tasks that Excel itself is not designed to solve. This could be sending a letter to e-mail, print beautifully designed text document, obtaining information from a database - for all this there are other specialized applications: MS Word, Access, etc.
Fortunately, these other applications also have automation tools, and these tools can be called externally - for example, as in our case, from MS Excel. On the one hand, using these capabilities is somewhat more complicated than operating only with Excel objects itself, but on the other hand, this allows you to significantly expand the range of tasks that can be automated and literally “work wonders”
This training will cover how to:
- fill in the data from Excel in the form MS Word;
- send a letter from Excel macro by using MS Outlook;
- select emails from a specified Outlook folder based on a certain criterion;
- save file attachment from email;
- read or change data from database MS Access;
- read or change data from files DBase (*.dbf);
- read information from text file;
- form text file based on the data on the sheet;
- save (and then read) your program settings in system registry Windows;
- create or delete a folder on your computer;
- copy, move or delete a file on your computer;
- find out the size, creation date and other attributes of the file;
- launch external application(any) from Excel and etc.
On August 22, 2017, together with the Glavbukh system (https://seminar.1gl.ru), I am conducting a webinar, aimed primarily at accountants, but will be useful to everyone who is constantly faced with the tasks of searching and comparing data, identifying errors, and building reports in Excel. Using examples, I will show: How to automatically highlight differences in indicators and quickly compare data from several reports for different periods How to select data from a table based on many intersecting conditions. For example, select late payments from one counterparty for two...
On Tuesday, April 18, 2017, together with the CFO system (www.1fd.ru), I will be holding the final webinar in a series of webinars dedicated to working with Power BI. In previous webinars, I showed: where to start working in Power BI, how to prepare data, how to process it and bring it to the desired form through the Power BI query editor, how to build visual reports and what they can be like, how to correctly set up connections and filters and how this affects on displaying visuals, how to create calculated fields: measures in a report and custom columns in...
On February 21, 2017, together with the CFO system (www.1fd.ru) and on their platform, I will conduct the first webinar in a series of webinars on Microsoft Power BI. At the webinar I will show how you can get data from the most common data sources: from the Internet from one or several Excel files from Google spreadsheets from XML files And to top it off - how all this can be combined into one report. For the webinar, I have already prepared example files and a ready-made Power BI model, in which you can see how everything is implemented. The report is simple, not overloaded with information - the emphasis is exclusively...
On October 20 and 21, 2016, the Financial Director system (www.1fd.ru) organized the conference “COMPANY FINANCIAL STRATEGY 2017”. He directly participated and gave a presentation on the topic “Master class on creating business analytics systems and preparing any reports using Excel and the publicly available Power BI tool.” In simpler terms, he demonstrated the process of processing and loading data into Power BI and creating visual reports based on this data (if you don’t already know what Power BI is - Getting to know Power BI). Actually, why am I writing? I would like to...
On October 18, 2016, together with the CFO system (www.1fd.ru), I continue the series of webinars “Excel for CFO on Tuesdays.” This time I will demonstrate clearly how to work with tools such as What-If Analysis and Solution Search, where I will tell you how to correctly use parameter selection and why you need a scenario manager. The webinar itself will not take much time, but it may well save it in the future. Start time: October 18, 2016, 12:00 Moscow time Approximate duration: 40-60 minutes. Conditions of participation: In order to register for the webinar, you only need...
Tomorrow, October 4, 2016, together with the CFO system (www.1fd.ru), I am holding the next webinar in the series of webinars “Excel for CFO on Tuesdays.” At the webinar I will show you clearly what non-standard charts you can create in Excel to visualize your data - Smart charts in Excel: how to make data understandable. What diagrams are we talking about? timeline linden chart with scalable and changeable table data one of the types of Gantt charts for creating visual work calendars charts with interactive...
Tomorrow, September 27, 2016, together with the CFO system (www.1fd.ru), I am holding the next webinar in the series of webinars “Excel for CFO on Tuesdays.” This time we'll go over conditional formatting - Excel on Tuesdays. Excel formatting tools. It will be more useful for those who have begun to study conditional formatting, but have not yet mastered the use of formulas in it - and formulas are the most powerful tool in Excel and, when used as a condition in conditional formatting, can save you from so much manual work...
Together with the CFO system (www.1fd.ru), we continue the series of webinars “Excel for CFO on Tuesdays”. And the upcoming webinar will be dedicated to discussing data security in Excel and the organization collaboration with files standard means Excel itself - Data protection techniques and organization of collaboration with Excel files. At the webinar, I will try to tell you in an hour how to properly protect cells, sheets and workbooks in Excel, what are the nuances of protection and how to strengthen the protection of sheets from hacking. Start time: September 20, 2016, 12:00 Moscow time Approximately...
