Excel course webinar. Master classes and trainings in the recording. Create hyperlinks. Data protection
Entry Level Materials
To study these materials, sufficient preparation is the development of materials from the "Free video tutorials" section. No other special training is required.
Master Class
Included:
Duration: 4 h 38 min
Cost: 1000 rubles
Populating some tables with data that is in other tables (often on other sheets or in other files) is one of the most typical time-consuming tasks that can be easily automated. In many cases formulas like VLOOKUP, INDEX And MATCH but, unfortunately, not always.
This webinar shows a universal solution for automating table filling using Visual Basic.
this link
Master Class
Included: videotape + open source examples shown
Duration: 4 hours 37 minutes
Cost: 1000 rubles
Another of the most time-consuming and routine tasks when working in Excel is creating a "package" of documents based on a certain template by filling its fields with data from a table. In most cases, this task is easy to automate - it is enough to describe the algorithm for filling out the form only once, and then it doesn’t matter: 10 documents need to be created or 10,000 - the documents will be created by themselves while you go about your business.
You can download the examples created in the master class from this link.
Master Class
Included: videotape + open source examples shown
Duration: 3 h 6 min
Cost: 3000 rubles
We are learning to write small but effective programs for everyday tasks: for selecting data from tables by condition, filling out a report based on several files, generating a pack of documents of the same type according to a template, speeding up the filling of documents, etc.
- how, depending on the condition, to execute one or other commands;
- how to repeat a set of actions a certain number of times;
- how to repeat a set of actions until a certain event occurs;
- how to force a macro to be executed when an event occurs (when a value in a cell changes, 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 ask 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 if a string contains certain characters;
- how to split a string into several substrings of a given length;
Master Class
Included: videotape + shown open source example
Duration: 2 hours 54 minutes
Cost: 3000 rubles
The master class is dedicated to the creation of a universal and convenient program designed to test knowledge in any area.
All questions for testing are set as 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 the choice of several items from the proposed ones at once.
Master Class
Included: videotape + open source examples shown
Duration: more than 9 hours (!)
Cost: 4000 rubles
A great video reference that goes into detail - with examples and explanations - for 112 VBA functions. 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 may ever need in practice.
The master class contains 110 (!) small programs - examples that demonstrate the use of each function in a given situation.
For the convenience of presenting 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: videotape + open source examples shown + self-paced tasks
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 learn the subject sequentially step by step, with detailed explanations.
The training is divided into two blocks with the conditional names "Practitioner" and "Specialist". Each of the two blocks is designed for approximately 2 weeks of classes.
Main block "Practitioner" contains the basic concepts, tools and techniques absolutely necessary for the implementation of almost any task. This section covers topics such as:
- conditional execution of commands;
- performing repetitive actions (cycles);
- 3 variants of cyclic structures;
- organizing a dialogue between the program and the user (a question with multiple answers, requesting a parameter value);
- tools for debugging code during development;
- existing data types and their differences;
- splitting the 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 questions:
- program response to events (opening/closing a book, changing a value in a cell, double clicking, activating a sheet, etc.);
- program processing of potential errors;
- work with data arrays;
- using the built-in help system;
- the basics of working with user forms;
- automating the creation of pivot tables and their modification;
- a 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 that are shown in the relevant training sessions. Tasks are built on the principle of gradually increasing complexity: if the implementation of more complex tasks causes difficulty, then the preliminary execution of simpler tasks will help to solve them.
To date, several dozen people have completed the training. WITH feedback from participants You can see
Materials for users with automation skills
Learning these materials requires the ability to automate simple tasks and knowledge of concepts such as loops, conditions, arrays, variable declaration, event handling, error handling, step-by-step debugging.
Master Class
Included: videotape + open source examples shown
Duration: 4 h 43 min
Cost: 3000 rubles
User forms and controls are used to create convenient and clear interface user.
With the help of these visual tools, on the one hand, the user's work is accelerated and the load is reduced, 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 data entered. In addition, the use of beautiful and convenient forms for dialogue with the user is an indicator highly professional completed project.
In this master class you will learn:
- how to create custom forms in excel files;
- how to open the form and set the initial value of the elements;
- how and when to check the correctness of the input data;
- how to programmatically change the size and position of elements and forms;
- how to set 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);
- how to call the file open dialog and set the selection by file type in it;
- how to populate a list/dropdown list with data from a worksheet in a book;
- how to allow or forbid the user to enter their own values in the drop-down list;
- how to allow the user to select several rows in the list at once and how to process the result of such a selection;
- how to get the value of the form element in the program after it is closed;
- how to programmatically insert a picture from a file and accurately indicate its location on the sheet and much more.
Master Class
Included: videotape + open source examples shown
Duration: 3 h 39 min
Cost: 3000 rubles
Excel has a toolkit that allows you to significantly expand the functionality of user applications - to make them more convenient, efficient (and spectacular). We are talking about the ability to programmatically respond to some events that occur during the user's work with the file.
Such events can be a change in the value in a cell, a double click, or a click right click 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 that they are based on the processing of certain Excel events.
Event handling capabilities in Excel are discussed in the following examples:
- change appearance tables by double clicking on the cells;
- setting up data validation in a cell depending on the value of another cell;
- displaying a progress bar to indicate the progress of long-running processes;
- user authorization, fixing the author and creation time for each table entry.
Two-day master class
Included: videotape + open source examples shown
Duration: 2 lessons of 4 hours
Cost: 5000 rubles
Automated downloading of the necessary information from the Internet is widely used as for one-time filling of a large database, and to update regularly changing indicators.
The most common examples include tasks such as:
- downloading prices for goods from price lists on the websites of suppliers (or competitors);
- fast creation of databases based on information on various Internet resources (for example, spare parts directories, 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 heads of manufacturing or trading companies in a particular region);
- etc.
In this master class, the technology for creating programs for collecting information from any site is analyzed in detail, the main problems in their development and ways to solve them are considered.
During the master class, writing 5 parsers is shown to obtain information from sites of various structure - including from such well-known portals as "Avito", "Yandex-Market", "From hand to hand".
Included: videotape + open source examples shown
Duration: 4 lessons of 2-2.5 hours
Cost: 7000 rubles
Sooner or later, when working in Excel, there is a need to automate tasks that Excel itself is not designed to solve. It could be sending a letter to e-mail, print beautifully designed text document, getting information from the 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 from outside - for example, as in our case - from MS Excel. On the one hand, using these features is somewhat more complicated than operating only with the objects of Excel itself, but on the other hand, this allows you to significantly expand the range of tasks that can be automated, and literally "work miracles"
This training will cover how to:
- fill in the data from excel in the form MS Word;
- send email from excel macro with MS Outlook;
- select letters from the specified Outlook folder according to a certain criterion;
- save a file attachment from an email;
- read or change data from the database MS Access;
- read or change data from files DBase(*.dbf);
- read information from text file;
- generate a text file based on the data on the sheet;
- save (and then read) your program settings in Windows system registry;
- create or delete a folder on the computer;
- copy, move or delete a file on a computer;
- find out the size, creation date and other attributes of the file;
- run external application (any) from Excel and etc.
Module 1. ProgramMSexcel. Setting up the program window.
Spreadsheet Microsoft Excel.
Purpose of the program.
Program interface.
Interface customization: quick access panels, ribbons, column header areas.
Scale.
Entering and editing information.
Entering information.
Data editing (replacement, deletion, addition, correction of errors).
Select columns, rows, cell ranges, unrelated cells, sheet. Deleting cell content, copying, pasting.
Working with sheets (insert, move, copy, delete).
Changing the table structure.
Adding and removing cells, rows and columns. Resizing columns, rows. Selection of cell width by content. Setting the same width for multiple columns, rows. Merging cells.
Cell formatting.
Working with fonts, aligning cell content, rotating text, adjusting indents.
Copy format according to the sample. Format cleaning.
Creating borders for the table (using border button, border tab and drawing individual borders).
Ways to create a fill for cells.
Practical lesson
Module 2 Creating lists.
Restriction on data entry.
Imposing conditions on data entry.
Formation of an error message when entering data.
Formation of a message for data entry.
Working with data formats.
Microsoft Excel data types and formats: text, numbers, dates, times, currency. Their setting. Simplified date entry.
Problems in using formats.
Organization of calculations inexcel.
Entering a formula into a cell. Copy formulas using auto fill.
Formulas with percentages. Absolute and relative links.
Preparing a document for printing.
Setting page options.
Creating headers and footers
Setting through rows and columns.
Document prepress. Using Preview.
Printing the selected area. Set the printable area.
Practical lesson
Module 3. Functions.
Use of simple functions (summation, average, minimum, maximum, number).
Ways to work with functions. Using help. Entering functions using the Function Wizard. Categories of functions.
Logic functions: IF, AND, OR, IFERROR. Nested boolean functions.
Mathematical and static functions: SUMIF, COUNTIF, SUMIF, COUNTIF.
Text functions CONCATENATE, LEFT, UPPER, LOWER, PROPER
Array functions: HLOOKUP, VLOOKUP.
Solving practical problems using functions.
Copying calculation results and formulas using Paste Special.
Review possible errors in formulas.
Practical lesson
Module 4 Graphics in documents.
Create diagrams. Diagram Wizard.
Setting chart properties.
Construction of various types of diagrams.
Chart printing.
Conditional formatting.
The concept of conditional formatting.
Create conditional formatting (comparison 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 displaying data.
Freeze rows and columns while viewing.
Dividing the sheet into parts when viewing.
Consolidation of data.
Applying filters. Data selection by filtering.
Advanced filter. Creating conditions for an advanced filter.
Search and replace.
Pivot tables.
Creation of pivot tables.
Using filters in pivot tables.
Changing formats for pivot tables.
Setting up pivot tables.
Pivot table editing.
Refreshing pivot table data.
Turning totals on and off in PivotTables
Practical lesson
Module 6. Notes (creation, editing, deletion).
Create hyperlinks. Data protection.
Setting a password to open a book.
Sheet protection for data entry.
Permission to work with individual cells and prohibition of work on other cells.
Final examination.
On August 22, 2017, together with the Glavbukh system (https://seminar.1gl.ru), I am holding a webinar aimed primarily at accountants, but it will be useful to everyone who is constantly faced with the tasks of searching and comparing data, identifying errors, 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 overlapping conditions. For example, select overdue payments of one counterparty for two...
On Tuesday, April 18, 2017, together with the CFO system (www.1fd.ru), I am holding the final webinar in a series of webinars dedicated to working with Power BI. In previous webinars, I showed: how to get started 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 set up links and filters correctly and how it affects on displaying visual elements 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 more Excel files from Google spreadsheets from XML files And finally, how all this can be combined into one report. For the webinar, I have already prepared sample 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 "FINANCIAL STRATEGY OF THE COMPANY 2017". He was directly involved and made a presentation on the topic "Master class on creating business intelligence systems and preparing any reports using Excel and the public tool Power BI". 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 still don’t know what Power BI is - Introduction to 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 how to work with such tools as What-If Analysis and Solution Search, where I will tell you how to use parameter selection correctly 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 Estimated duration: 40-60 minutes. Conditions of participation: In order to sign up for the webinar, it is enough ...
Tomorrow, October 4, 2016, together with the CFO system (www.1fd.ru), I will be holding another webinar from the series of webinars "Excel for CFO on Tuesdays". At the webinar I will clearly show 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? linden timeline chart with scalable and resizable table data one of the varieties of Gantt charts for creating visual calendars of work charts with interactive ...
Tomorrow, September 27, 2016, together with the CFO system (www.1fd.ru), I will be holding another webinar from the series of webinars "Excel for CFO on Tuesdays". This time, let's go over Conditional Formatting - Excel on Tuesdays. Excel Formatting Tools. It will be useful to a greater extent for those who have begun to study conditional formatting, but have not yet mastered the use of formulas in it - and formulas are just the most powerful tool in Excel and used as a condition in conditional formatting can save you from such a ton of manual ...
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 devoted to discussing data protection in Excel and organizing file collaboration with standard tools of Excel itself - Techniques for protecting data and organizing 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 you can strengthen the protection of sheets from hacking. Start time: September 20, 2016, 12:00 Moscow time Approximate...
