Useful Excel functions to help an accountant. How I did management accounting in Excel Accounting in excel

• 12.04.19

  Innovations in Google Docs allow you to stop using local copies of applications from the Microsoft Office suite. Now text documents, tables and presentations can be loaded into Docs without conversion and worked with them without any restrictions. New features are currently available only to corporate users, but by the end of spring 2019 Google will remove this restriction. 1 045

• 29.04.14

  In difficult economic conditions, analysis of the financial and economic activities of an organization remains a subject of constant concern for business. Recently, one of the trends in the labor market has been an increase in demand for financial analysts; at the beginning of 2014, the demand for them was 6.1% more than in 2013. Let us recall that the subject of financial analysis itself is included in the national program for training accountants at Russian universities. 4 731

• 26.03.14

  In difficult economic conditions, analysis of one’s own financial and economic activities remains a subject of constant concern for businesses. At the beginning of 2014, demand for financial analysts was 6.1% higher than the year before. 4 525

• 08.11.12

  In connection with the adoption of Law No. 208-FZ “On Consolidated Financial Statements”, the load on the financial services of Russian organizations is increasing and, as a result, management’s interest in the issue of organizing automated preparation of financial statements under IFRS is growing. 18 728

• 23.07.12

  The problem of automating accounting processes and preparing reports under IFRS. 3 15 156

• 29.03.12

  General The Directory of Units of Measurement has been supplemented with codes of units of measurement, according to Appendix 2 of the All-Russian Classifier of Units of Measurement OK 015-94 (MK 002-97) (OKEI) (approved by Decree of the State Standard of the Russian Federation of December 26, 1994 N 366) (as amended by NN 1 , 2, 3) (as amended NN 4/99, 5/99, 6/2000,7/2000). 711

• 20.03.12

  AWS of the Chief Accountant External reporting: Added... 803

• 13.03.12

  General 1. Directory of partners. For a partner who is an individual, the field is increased to 15 characters.

• 02.03.12

  AWS of the Chief Accountant 1. External reporting: Requests for calculation of forms 3,4,6 have been added to the forms. When answered, the forms are not calculated and are not printed. 1 327

• 25.10.11

  Added algorithm and report template 1,092 9 579

• 12.07.11

  Addendum No. 1-6 to update package No. 28 General ledger 1. External reporting. 13 857

• 31.05.11

  Requests for calculation of forms 3,4,6 have been added to the forms “Accounting statements” and “Accounting statements of GNIVC”. If the answer is “No”, the forms are not calculated and are not printed. 22 021

• 25.05.11

  Added algorithm and report template “Explanations for the balance sheet and income statement (form 5)” in accordance with Order of the Ministry of Finance of the Russian Federation No. 66n dated July 22, 2010. No. 124n dated 05.10.11. Upon delivery... 6 705

• 12.11.10

  In Excel, it is convenient to check the execution of the annual budget, comparing plan and fact, but for this you need to transfer real numbers from 1C or another accounting program. Manual data entry may lead to errors. A special template for collecting actual figures from the balance sheet will help you avoid them. The finished file is posted on the website www.gazeta-unp.ru in the text of this article. 10 256

• 02.09.09

  The Tax Service continues to distribute the “Legal Taxpayer” program to everyone. This software allows you to generate tax and accounting reports. We found out in what cases a free program can be useful. At the same time, we found out why its latest version is good. 1 27 644

• 10.12.08

  Version 1.2.5 of the "Consolidation.PROF" configuration (rev. 1.2) for the 1C:Enterprise 8 system has been released. New in version: 1. New standard methodological model "Analysis and forecast of the financial condition of individual enterprises and holdings." The methodological model includes the following types of reports: Reports of the "Turnover - balance sheet" type, generated according to data from accounting registers of external information databases: "1C: Accounting 7.7"; "1C... 760

The management of companies that have decided to begin implementing a management accounting system and budgeting itself, at the first steps, is forced to face several problems, namely:

• lack of financial capital to implement a specially developed platform;
• lack of a clear understanding of what needs to be done.

In such situations it comes to the rescue MS Excel, which, in addition to being free, is also a universal software option for full-fledged management accounting.

Any organization must maintain accounting records, regardless of what area it operates in, what its scale is, and so on. This is largely due to the fact that accounting must establish all the facts of the company’s life and display them on the appropriate accounts to achieve the maximum picture of the financial situation as a whole.

Accounting information is often the basis for determining an organization's tax obligations. For example, the tax base for property is determined based on accounting information.

Main purpose accounting and, in parallel, final accounting reporting is the provision of generalized information to interested users (external) regarding the results of labor activity for a certain reporting period.

Interested users may include:

• banking institutions;
• other creditors;
• investors and so on.

In other words, all persons without exception to whom the company has debt obligations are taken into account.

In turn, management accounting is used exclusively by enterprise management, and not by external users. It is often used not only by company managers to assess the current situation, but also allows them to provide reliable information for making the right management decisions, including the ability to instantly respond to various factors.

It is important to pay attention to the fact that the users to whom management reporting is primarily oriented can be very different: from the structural divisions themselves to presidents of organizations or managing partners.

In turn, the focus of management accounting on high-quality and instantaneous decision-making by management is explained by difference in formats direct representation of the values ​​of the accounting in question in comparison with the accounting one.

In addition, management reports may differ in a high degree of detail or, more simply put, in the accounting methods used (for example, the current Tax Code of the Russian Federation, and not RAS or IFRS), the special accounts/sub-accounts used that cannot be used in accounting, as well as high regularity of formation and so on.

How to compose

Many domestic companies today practice using management accounting in tables generated in MS Excel.

This has a lot to do with the fact that Excel is essentially a universal tool, which includes a huge set of mathematical formulas, algorithms, special accounting mechanisms, and so on, but does not require any significant costs (for example, as other accounting software requires).

Based on this, thanks to the use of MS Excel, qualified personnel have the right generate all reports necessary for making any decisions.

Standard management accounting must include several basic components:

• report regarding profit and financial costs;
• statement regarding the movement of financial capital;
• balance of the organization.

In the process of forming the necessary tables, it is initially necessary to pay attention to the isolation of the tables.

Each separate table (including information regarding one or another aspect of the organization’s labor activity) must be provided on a separate sheet. There is no need to try to fit everything on one sheet of paper (meaning all numerical indicators). Moreover, the length of the book can vary significantly depending on the level of detail required by management.

To solve this problem in practice, built-in Excel functions are often used, including drop-down lists, groupings, and so on.

All generated tables with initial information must be place on separate sheetsExcel(for example, a sheet for recording profits from the sale of building materials, and so on), and at the very beginning of this book you should create a pivot table in which, using drop-down lists, the final indicators are displayed relative to other sheets of the book.

Due to the fact that the grouping of “Business Items” remains the same (for example, cereals, drinks, and so on), and the accounting tables themselves in Excel require the formation of a huge number of tables (accounting for profits, direct financial costs, indirect monetary losses, accompanying cash costs and so on), companies need to remember one nuance.

It lies in the fact that the most optimal tool inExcelreference books are rightfully considered. In the process of their formation, organizations will be able to fully use them as auto-fill for all existing fields upon completion of the tables.

In addition to all the available advantages, the most important thing is the presence of a huge number of all the necessary formulas and various other mathematical mechanisms (for example, the possibility of autocorrelation, dispersion, mathematical expectation, and so on), which provides significant assistance in the process of analyzing final values, as well as planning future development .

This gives every right to ultimately accept effective management decisions.

Formation algorithm

The compilation algorithm is next:

1. Conducting an analysis of the organization's monetary structure.
2. Mandatory formation of management accounting classifiers.
3. Creating a chart of accounts.
4. Formation of the concept of management accounting.
5. Creation of an appropriate list of management accounting formats.
6. System implementation and automation.

In fact, in practice, many domestic organizations have already shown that developing accounting in Excel will not be difficult. It is enough just to monitor the accuracy of the information provided and strictly adhere to the existing algorithm.

For a better understanding, please refer to picture instructions:

Based on this image, you can see what the future model should presumably look like.

Implementation of the system in the organization

In the process of implementing management accounting in MS Excel, initially attention should be paid to automating the accounting of financial flows. This is a kind of information base for management accounting as a whole.

Financial movement, in fact, is a kind of reflection of all other operations of the organization.

The next stage is the introduction of profit accounting and financial costs. And only lastly should automation of assets and liabilities be carried out exclusively on those parts that are not related to financial flows and profits.

Existing rules

For today there is a few key rules, which must be adhered to in the process of implementing automated management accounting in MS Excel.

1. Separation of functions regarding the indication of information and those that fall on the formation of various reports. At first glance, this may seem obvious, since often institutions draw, for example, a formula for a DDS report and subsequently enter all existing DDS transactions into this formula.
2. Indication of information regarding various business transactions in the form of a register of similar entries.
3. Indication of information on all existing legal entities and accounts that directly relate to the organization in a single register.

It is possible to enter information in Excel in different ways, since there are no restrictions.

The main thing is that this is the only register for business transactions, where in the future all information regarding the organization, without exception, will be displayed, regardless of which legal entity it belongs to and what current account it has.

Examples

An example of the financial structure of an organization in management accounting based on the developed tables in Excel will look something like this:

As for the classifier, it looks like this:

I would like to clarify the fact that the classifiers of the accounting under consideration can describe a variety of objects with the aim of their reliable interpretation by all participants in this system without exception.

Today there are the following most popular classifiers:

• type of profit;
• the type of product manufactured or the provision of any services (for example, construction);
• different types of clients;
• places where profits and financial costs are generated;
• types of financial losses;
• types of obligations;
• a type of own financial capital;
• category of hired staff and so on.

Based on all of the above, we can conclude that, in fact, there is nothing complicated in this version of accounting that was considered.

How to maintain management accounting in 1C - on video.

Currently, the number of various reports that are prepared by all departments of organizations is steadily growing. Very often, enterprises automate reporting based on various software products (SAP, 1C, Intalev, etc.). But even in those organizations where the system is “automated”, they cannot do without using Excel. Since the software products that are used for automation contain general principles for constructing reports, and each enterprise is unique, it is difficult to unify all reporting in specialized software products, and sometimes it is impossible to do this. And this is where Excel spreadsheets come to the rescue. They allow you to finalize reports, present information obtained when downloading from the same 1C, in the required form, and sometimes even serve as the main program for maintaining management and other reporting.

Excel is one of the most popular programs in the basic Microsoft Office suite. The sales manager, using tables, conducts systematic work with clients. The secretary keeps records of all local regulations, correspondence, or prepares diagrams for a production meeting. An economist processes primary information and calculates company performance indicators. Spreadsheets are the most popular program used by employees of various services and various areas of activity.

The program is especially useful for employees of financial and economic departments and accounting departments if they are assigned the functions of planning and maintaining management accounting. Excel for an accountant provides the ability to consolidate data, allowing you to combine information from several files into one. Allows you to select the information you need by creating separate tables or hiding data in the original table. Allows you to systematize the available information according to the required characteristics and summarize the results.

One way or another, accounting in Excel will become much easier if you master a few useful techniques that can speed up your work with data. Find out how Excel can be useful for an accountant in the examples we have prepared. The following exercises in Excel for the work of accountants will be not just useful, but simply necessary.

A fundamental Excel tool

In addition to his immediate responsibilities, an accountant may be given the functions of preparing commercial proposals, calculating contract prices, etc. To perform calculations, it is necessary to apply various coefficients and adjustments, as well as convert prices. And most importantly, perform all actions quickly and without compromising your main responsibilities.

Excel for an accountant will help in preparing the main document, which can be performed on the main sheet, and additional calculations on separate ones.

So, when recalculating the rate, you can specify the price in one cell, the exchange rate in the second, and in the third set the conversion formula (= first cell * second cell), then press Enter and get the price in rubles. In the first sheet, you can put “=” in the desired cell, go to the second sheet and indicate the third cell with the total. Press Enter again and get the result.

If it is necessary to carry out such calculations for a large number of points where only the price changes, then you can use a fundamental Excel technique - auto-filling formulas, or dragging.

The ability to draw formulas is one of the basic functions of the program. It automates the process of calculating data in a table, without repeatedly writing the same formula. You can stretch formulas as follows.

In the formula bar, set equal and the link to the cell from the table with the source data (=A3). After this, we simply get a duplication of the value from the table. When you drag this cell, you will get a copy of the table with data that will change accordingly with the change of information in the original table. This is an example of stretching cells without fixing ranges.

You can pin a link to keep it the same whether you drag it completely, by row, or by column. Fixing is done in the formula bar using the $ sign. This sign is placed in front of that part of the coordinates in the link that needs to be fixed:
$ before a letter – fixation by column - $С1
$ before a number – fixation along the line - С$1
$ before the letter and number – complete fixation of the cell - $C$1.

Calendar days counting

Excel can be an indispensable assistant for even simple tasks such as counting calendar days. An accountant needs to know exactly how many days there were in a particular period in order to calculate interest, the amount of penalties, penalties, loans, and the like.

If this is a short period of time, then it is easy to calculate it using a calendar, but for performing regular calculations this format is quite inconvenient. In such situations, Excel comes to the aid of the accountant.

To perform the calculations, you need to select three empty cells in the table. You need to write down the start date in one, the end date in the second, and leave the third blank to get the results.

Next, select the third cell and click “Insert Function”, you can find it by the ¶x icon. After clicking, the “Function Wizard” window will pop up. From the “Category” list, select “Date and Time”, and from the “Function” list, select “DAYS360” and press the OK button. In the window that appears, you need to insert the start and end date values. To do this, you just need to click on the table cells with these dates, and in the “Method” line put one and click OK. If the total value is not reflected in a numeric format, you need to check the cell format: right-click and select “Cell Format” from the menu, set “Number Format” and click OK.

You can also count days in this way: in the third cell, type = DAYS 360 (B1; B2; 1). In parentheses, you must indicate the coordinates of the first two cells with dates, and for the method, set the value to one. When calculating interest for weeks, you can divide the resulting number of days by 7.

You can also add or subtract any number of days to the date. To do this, you need to write the date in one cell, place an equal sign in the second, then click on the cell with the date and type “+” or “-” and the required number of days.

Sorting data

A very convenient function that allows you to place data in ascending/descending order. You can also sort data to organize records by date.

To perform this action, you must select the area that requires sorting. Then you can click the “Sort Ascending” button in the top row of the “Data” menu, you will find it by the “AY” sign. Your data will be arranged from smallest to largest in the first selected column.

Excel tables for accountants allow you to sort data starting from the first selected column. If you selected cells from left to right, the sequence will be performed in the leftmost column. If from right to left, then in the right.

If you need to sort the data by the middle column, you can use the “Data” menu - “Sorting” - “Range sorting” item. In the “Sort by” section, you need to select a column and sort type.

Working with Long Tables

Excel spreadsheets for an accountant are a multifunctional working tool that contains a lot of information for reporting and performing current calculations. When printing a table that does not fit on one sheet, you can place its “header” on each separate page, which will make it easier to find the necessary data. To do this, select “File” - “Page Settings” from the menu and the “Sheet” tab. Place the cursor on “End-to-end rows” or “End-to-end columns” and in the table click on the rows that need to be placed on each sheet.

You can also use headers and footers to work with such documents. They note the necessary data, such as date, sheet numbers, name of the compiler, etc. Setting up headers and footers is available in “Page Settings” - “Header and Footers”. There are ready-made markup options available or the ability to add your own.

In addition to useful techniques for working in Excel, an accountant needs to master its hot keys.

A set of Excel hotkeys you can't do without

Using these keyboard shortcuts in Excel will speed up your work and help you perform data analysis, create graphs, and format tables.

• F4 - when entering a formula, adjusts the type of links (relative, fixed). Can be used to repeat the last action.
• Shift+F2 - editing notes
• Ctrl+; - enter the current date (for some computers Ctrl+Shift+4)
• Ctrl+’ - copying the values ​​of the cell located above the current one (for some computers the combination Ctrl+Shift+2 works)
• Alt+F8 - open the macro editor
• Alt+= - sum the range of cells located above or to the left of the current cell
• Ctrl+Shift+4 - determines the cell's currency format
• Ctrl+Shift+7 - setting the outer border of the selected range of cells
• Ctrl+Shift+0 - defining the general cell format
• Ctrl+Shift+F - the combination opens a cell formatting dialog box
• Ctrl+Shift+L - enable/disable the filter
• Ctrl+S - save the file (save as often as possible so as not to lose valuable data).

Using shortcut keys and useful techniques in Excel will simplify and speed up your work only if you already have sufficient knowledge of this program. If you want to improve your level and use spreadsheets more effectively to conduct accounting in Excel, you can download the book for free

I work as an ordinary analyst and it so happened that in the summer of 2014, while participating in an e-commerce project, I did management accounting in MS Excel on my knees in 3 weeks. I've been planning it for a long time and finally decided to post it on Habr. I think it will be useful for small entrepreneurs who understand the importance of managing financial flows, but do not want to spend a significant amount of time and money on maintaining management accounting. I do not pretend to be the ultimate truth and will be glad to see other solutions proposed by community members.

The business I was involved with in the summer was an ordinary online clothing store in the premium and higher segment with a turnover of about 1 million rubles per month. The business worked, not to say very successfully, but it worked and continues to work. The owner understood the need for management accounting and, with this understanding, hired me as a financial director (analyst/manager...), since the previous one left the business 3 months before my arrival. Actually, there was a hole of the same duration in management accounting. Looking ahead, I will say that I did not eliminate the hole (we decided not to stir up the past), but created a system that works successfully with minimal labor to this day.

My predecessor led the administration in Fingrad, which turned out to be a very powerful tool. For example, it made it possible to automatically download information from 1C and statements of different bank clients, creating transactions according to pre-formulated rules. This is certainly a useful thing, however, if the double entry system was followed, it increased the work time significantly. To avoid increased work, this tool allowed the generation of “dependent transactions”. The dog was buried in the creation of these additional wiring. And then it turned out that behind all the power of Fingrad there was a uniqueness that determined the complete absence of expertise in the public domain. Ordinary users (who, by the way, paid 3,000 rubles a month for access to the system) had access only to the “User’s Guide” on the official website and 6 video lessons there. Youtube, which gave access to a couple dozen more video tutorials, also didn’t help much. There were basically no forums with “how to...” information. Support, in response to specific questions about the rules for creating “dependent postings” and requests for help specifically in my case, was frozen with the phrases “you and I do not have a support agreement, so we are not ready to answer such specific questions.” Although it would seem - what is specific about such requests, and even with screenshots on my part? It is clear that everything can be done with your hands, but the question is, why then pay at all for a tool that greatly increases the time required for management and does not provide any benefits for small businesses?

Having convinced the owner of the inexpediency of using Fingrad with such volumes of business and downloading all the information from the system, I set it to BIG and fatty cross. At the same time, the decision to go to MS Excel was not spontaneous. A good Google search on the topic of management accounting found monsters similar to Fingrad, or links to web applications for managing personal finances, while the main requirements for the system were:

Possibility of maintaining BDDS and BDR based on a changeable chart of accounts;
- simplicity in further management accounting (including by “financially illiterate” users);
- flexibility (the ability to expand/remove functionality on the go);
- no tool/interface overload.

First, let’s clarify the terms: being not a financier, by BDDS I mean “Balance of Cash Flows”, BDR - “Budget of Income and Expenses”. We consider the BDDS on the cash basis (the day of the transaction - the “Operation Date” column) and use it for operational day-to-day planning, and the BDDS on the accrual basis (the “Accrual Period” column) for strategic planning, within a year or more.

So, how everything works and how it works (ideally):

1. Management accounting is collected based on information entered by end users using a form in Google Docs. The field names and option encodings in the final management accounting file are marked in red - a kind of field mapping.

2. In the end, it looks like this (what was transferred to the final management file is filled with green).

3. Management accounting is built on the basis of .xls downloaded from Fingrad (hence the names that are strange for third-party users and, in general, an excessive number of columns). We kindly ask you not to take the meanings of the columns “Incoming” and “Expense” seriously - a lot has been changed randomly.

The filling mechanism is simple: carefully transfer it to the “General Ledger” tab from the Google Docs form and bank statements. The lines used to generate the BDR are highlighted in red, and the BDDS are highlighted in green, which are summary tables and are built on the basis of intermediate tabs with self-explanatory names. The only columns in which the information is not related to other sources are “Original ID” (unique row values) and “Creation Date” (=TDATE(), and then copy and paste as a value)

4. DDS (cash flow) items are located on a separate tab “PS_service” and may well be regularly revised depending on specific needs (do not forget to update the formulas on the sheets “Data_BDDS”, “Data_BDR”).

5. The picture shows a sample of the BDDS, in the default format, reduced to weekly “relevance”.

6. Sample BDS (monthly). Pay attention to the thesis already mentioned above about the use of lines from the “General Book”: Budget and Fact for BDR, Plan and Fact for BDDS.

7. Working with the BDDS implies maintaining the “Plan” lines as up to date as possible. I am quite pedantic in working with primary information and the comments I made saved the entire history of changes. How will it be for you - a question for you. My approach allowed me to catch about 1 significant error per week, which threatened discrepancies of tens to hundreds of thousands of rubles. By the way, time was eaten up a little.

8. Actually himself

Over the course of four days, experienced editors from Glavbukh taught in classes how to create a high-quality magazine article. One of the school’s students, Polina Alekseeva, decided to consolidate her knowledge in practice and wrote an article specifically for the site about the useful functions of the well-known Microsoft Excel program.

Microsoft Excel is a program with wide capabilities. Many people are familiar only with the basic skills of working with the program and simple functions. An accountant often does not have enough time to learn how to automate the calculation of any indicator, but in vain. We invite you to spend 10 minutes a day and master the features of Excel that are useful in your work.

Here is an overview of Excel features useful to an accountant. Open the program and try our examples. It's really simple!

1. How to change the color of a cell depending on its value

Everything is very simple - we change the color (fill, font, frames) if the value meets certain conditions. We fill the negative balance with red, and the positive balance with green. Late orders are highlighted in red, and orders delivered on time are highlighted in black.

To do this, select the cells that should automatically change their color and select Format - Conditional Formatting from the menu.

In the window that opens, set the conditions and, by clicking the Format button, set the cell formatting options if the condition is met. In this example, we make large customers in bold blue font, and small ones in black italics:

In the condition, we check either the cell value or the given formula.

Examples of conditions:

formula = $A1 =, highlights the entire line
formula = cell = MAX/MIN ($A$1:$A$10) highlights maximum, minimum values
formula = cell, = AVERAGE ($A$1:$A$10) highlights values ​​greater (less than) the average

2. How to select the required entries from the list

The first method is autofilter

We hide all rows, except those that satisfy the specified selection conditions. To do this, select any cell in the list and select Data - Filter - AutoFilter from the menu.

In the first row containing the column headers, arrow buttons will appear—autofilter buttons. To select records, click on the autofilter button in the header of the desired column and select a filtering condition from the drop-down list:

Autofilter performs the following functions:

Sorts the list by the selected column (select “Sort”)
- finds a specified number (or percentage) of the largest or smallest elements in the list (select “Top 10”)
- performs filtering based on a given condition (select “Condition”)

In this case, we select from the list the document numbers posted during the specified period.

The second method is an advanced filter

To use an advanced filter, first of all, set a range of conditions:

Let's copy the headings of the columns for which conditions will be entered
- enter conditions for filtering below the copied headings: cells of the same Excel row are connected by logical “AND”, and cells of different rows are connected by logical “OR”.

For example, to select from the list documents posted during the period January 15-16, in which account 903003 appears, you need to set the condition as follows:

To filter data by criteria from our table, select the original list and select Data - Filter - Advanced filter from the menu. The Advanced Filter window will open, in which we enter the address of the source range and the address of the condition range.

3. The SUM function sums the entire given range, how to sum only the values ​​that meet a certain condition?

In this case, we use the SUMIF function. Let's look at a specific example. Let’s say you need to calculate revenue subject to VAT using this table:

Our actions in this case are as follows: use the SUMIF function (“rate, %”, “>0”, “total purchases”)

Function syntax - SUMIF(Range; Criterion; Summation range). Here “Range” is the cells that are checked for compliance with the criterion; "Criterion" - a criterion in the form of a number, expression or text; “Summarization range” - cells for summation.

Function Syntax – COUNTIF(Range; Criterion)

Excel has a wide range of functions that perform financial calculations. For example, the PMT function, which allows you to determine monthly loan payments. As input data for this function, you need to enter the interest rate, the number of periods and the loan amount.

In the example, we calculate the amount of monthly payments and the total amount of payments.

Function syntax PLT(monthly interest rate; number of periods; loan amount)

Other financial functions that can be used to calculate loans and investments:

Function NPER(Rate; Payment; Present value; Future value; Type) – calculates the period for which you can pay off the loan, if there is data on the loan amount, monthly payments and bank interest.

Function BID(Periods; Payment; Present Value; Future Value; Type) - calculates what the bank’s interest should be for a loan with a given amount, term and monthly payment.

Function PS(Rate; Periods; Payment; Future value; Type) - calculates how much we can borrow from a given bank if we are willing to pay a certain amount monthly.

5. How to correlate records from one table with records from another?

Let's look at this issue using an example. Our chart of accounts is presented in Excel in the form - account, name. Before updating the software, it was decided to delete accounts that had no turnover in the last 3 years. Now we have a list of accounts and we need to copy the names from the original chart of accounts. The VLOOKUP function allows you to automate this process.

Excerpt from the original chart of accounts (1):

Accounts with turnover over the last 3 years (2) – 1000003 and 1100001.

Let's determine the name of these accounts using the first table. If we have a list of thousands of accounts in front of us, the task no longer looks so simple.

Solution - function VLOOKUP, which searches for a given value (in our example, this is the account number) in the leftmost column of the specified table, moving from top to bottom and, having found it, displays the contents of the adjacent cell.

For the function, you must enter arguments such as: the value you are looking for (the cell containing the value is indicated), the array in which the search takes place, and the column number:

6. How to create a compact and visual report from a huge table?

For this we use pivot tables. For example, using this mechanism it is very convenient to analyze transactions downloaded from the 1C program. Let's consider this situation in more detail.

The original table looks like:

To build a pivot table, place the cursor in any cell of the data list and select the menu item Data – Pivot Table. Next, follow the instructions of the Pivot Table Creation Wizard. As a result of this operation, we obtain a report template on a separate sheet:

Working with the template is easy: drag the names of columns (fields) from the list of fields of the pivot table into the areas of rows, columns, pages and layout data:

1) drag the “Debit” field into the row area of ​​the data table;
2) place the “Month” field in the columns area;
3) in the data area - the “Amount” field, etc.

So, the report is ready. Note that the source table may have thousands of records, but we only spent a few minutes preparing the report.

You can drill down into the report by dragging any additional field into the row area, for example, credit account, subaccount, etc.

When the number of indicators in a table is no more than 3-4, it is more convenient to use the Subtotals mechanism instead of pivot tables. We proceed according to the following scheme:

First step– Sorting

First, we will sort the table to form groups of rows, by which we will later calculate the totals – Data – Sorting

Second step- Summarizing

In this dialog box, we indicate the field by which our table is sorted, select a mathematical function (sum, average, minimum-maximum, quantity, etc.), and check the columns for which we need to summarize.

Click on the OK button and get a table in which the totals are automatically included.

When summarizing results, Excel performs the grouping itself. Sorted groups can be quickly and conveniently collapsed and expanded using the plus and minus signs to the left of the table. When collapsed, the report looks more compact and informative.