Баланс в экселе с формулами 2018

8 функций Ехсеl, которые помогут сократить работу вдвое

Баланс в экселе с формулами 2018

Согласитесь, обидно провести половину рабочей недели, подготавливая файл к совещанию, а потом узнать, что всю работу можно было сделать за несколько часов.

Но еще более обидно нанимать дополнительных специалистов, потому что те, что есть, «не справляются» с объемом работы. Хотя можно было бы сделать упор на качество, а не количество, и получить искомый результат с гораздо меньшими вложениями.

В статье расскажем, как облегчить работу себе и окружающим, используя функции Excel, о которых вы не знали.

Используйте пошаговые руководства:

Как часто вы или ваши коллеги сталкиваются с ситуацией, когда подписанный договор или смету приходится подписывать еще раз из-за такой мелочи как расхождение в копейках по суммам или НДС? Казалось бы, такой пустяк, но бухгалтерия не принимает документы и приходится тратить массу рабочего времени на правки и переподписание.

Дело в том, что Excel, в котором большинство менеджеров создает сметы и другие расчеты по договорам, считает до пятнадцати знаков после запятой, даже если в формате ячейки пользователь видит всего два знака.

Бухгалтерские же программы, например, 1С, по умолчанию считают все числа с двумя знаками после запятой. Вот и «едут» копейки в расчетах, порождая дополнительные сложности в работе менеджеров и бухгалтеров.

Но устранить проблему и снизить количество бесполезной работы сотрудников довольно просто. Разработанная таблица в Excel формы смет или приложений к договорам, использовав в расчетах функцию ОКРУГЛ. Или же обучить менеджеров ее использованию.

Функция ОКРУГЛ позволяет округлять расчеты в ячейках Ecxel до нужного количества знаков. В нашем случае, до двух.

Синтаксис

ОКРУГЛ (число; число_разрядов)

Число – значение, которое нужно округлить,

Число разрядов – количество знаков после запятой.

Впр (англ. vlookup)

Работа финансиста во многом связана с обобщением и анализом данных, собранных из нескольких источников. Будь то бюджетирование, отчет по движению денежных средств или подготовка к совещанию, мы берем данные из нескольких таблиц и сводим их в итоговой по определенным параметрам.

В зависимости от объема данных, работа может занять от нескольких часов до нескольких дней, а если данные постоянно обновляются, то это становится похоже на сизифов труд. Хорошо, что есть функция ВПР (функция вертикального просмотра), которая позволяет подставить данные из диапазона (читай любой таблицы) в итоговую таблицу по заданному параметру.

Пример с функцией ВПР

Допустим, вам нужно посчитать итоговые остатки товаров на складах по номенклатурным позициям и в денежном измерении. В качестве исходных данных вы получили несколько отчетов по остаткам на складах от розничных торговых точек. В отдельной таблице сведены данные по цене каждой номенклатурной позиции.

Вы сформировали итоговую таблицу, в которой перечислены все номенклатурные позиции, и теперь вам нужно собрать все данные воедино (см. также Power Pivot: как научиться создавать наглядные аналитические отчеты в Excel).

Перенесем данные по  остаткам на складе Точки 1.

Для этого зададим в ячейке B3 функцию ВПР.

ВПР(A3;'точка 1'!$A:$B;2;ЛОЖЬ)

И протянем ее до конца столбца. Остатки по точке 1 перенесены.

Аналогично нужно поступить с остатками по остальным складам и с таблицей с данными по ценам. Если добавить к формуле функцию ЕСЛИОШИБКА, то #Н/Д можно заменить на любой подходящий к ситуации символ.

Вся работа займет у вас не более часа, и результат будет пересчитываться автоматически и изменением исходных данных.

Поискпоз (англ. match) и 4. индекс (англ. index)

Функции ПОИСКПОЗ и ИНДЕКС близки по области применения к функции ВПР, так как тоже существенно помогают снизить время на обработку и анализ больших массивов данных по заданным критериям.

Их часто недооценивают, так как не могут придумать им применение на практике, но вместе эти две функции способны творить чудеса, о чем и расскажем ниже (читайте также про нестандартные решения в Excel для повседневных задач финансиста).

Функция ПОИСКПОЗ призвана искать указанное значение в диапазоне и отображать относительный номер строки, в которой находится искомое значение

Пример с функциями ПОИСКПОЗ и ИНДЕКС

Вы получаете данные по продажам продукции от каждой торговой точки в конце каждого месяца и в результате сформировали таблицу вида.

Перед вами стоит задача быстро найти необходимые цифры продаж по указанной розничной точке и указанной номенклатуре. Можно, конечно искать с помощью фильтров и внимательного просмотра, но если выборка больше чем из ста строк, это будет долго и утомительно.

Использовав функции ПОИСКПОЗ и ИНДЕКС, вы получите результат быстро, и его можно будет обновлять под новые запросы. Как в примере:

Обратите внимание на строку формулы.

ИНДЕКС (А2:F7 – определили массив, в котором искать; координаты столбца заданы функцией ПОИСКПОЗ ($B$10 – искомые данные; A2:F2 – просмотр по столбцам; 0 –точное совпадение); координаты строки заданы функцией ПОИСКПОЗ ($B$11 – искомые данные; A2:A7 – просмотр по строкам; 0 –точное совпадение).

Теперь вы можете за секунду найти любое значение в таблице, просто обновив данные в двух ячейках. А можете использовать его в более сложных формулах, не затрачивая время на обновление исходных данных.

Формулы массива (англ. array formulas)

Формула массива – это ускоритель работы финансиста в разы, потому что она одна изящно заменяет несколько формул, помогая попутно решать задачи, неподвластные общеизвестным функциям Excel (см. также, как построить Диаграмму Ганта в Excel).

Как понятно из названия, формулы массива работают с массивами данных, которые бывают:

  1. Одномерными (строка, столбец).
  2. Двумерными (таблица).

Формулы массива в свою очередь тоже делятся на те, которые показывают результат в одной ячейке, и те, что выводят результат в виде массива данных.

Подбор параметра (англ. goal seek)

Эта функция (или, скорее, встроенный прием Excel) позволяет ответить на вопрос: «Какие должны быть исходные данные, чтобы получить желаемый результат?».

Например, вам нужно определить объем начальных инвестиций в стартап, для того, чтобы к n-му периоду объем свободных денежных средств компании был равен, допустим, 1 млн. руб. (предполагается закупка оборудования).

В вашем распоряжении подготовленный Cash flow.

Конечно, можно долго подбирать данные и пересчитывать результат вручную, а можно сэкономить силы и время, воспользовавшись функцией подбора параметров.

Работает это так:

  1. Открываем «Данные» – «Работа с данными» – «Анализ «что если» – «Подбор параметра».
  2. В область «Установить в ячейке» вставляем ссылку на ячейку с нужной формулой.
  3. В области «Значение» записываем искомый результат.
  4. В области «Изменяя значение ячейки» вставляем ссылку на ячейку с исходными данными.

Готово! Чтобы получить к концу 12 месяца 1 000 000 на счету, нужно вложить 5,714 млн. рублей на старте.

Предсказ (англ. forecast ) и тенденция (англ. trend)

Следующая группа функций призвана повысить эффективность работы финансиста при формировании прогнозов.

Каким образом вы рассчитываете прогнозные значения? Многие ставят их «на глаз», продвинутые пользователи Excel умеют строить линии тренда и из диаграммы берут итоговое уравнение для расчета.

Мы же расскажем вам о статистических функциях Excel, которые помогут посчитать прогноз быстро и с использованием научного подхода.

Функция ПРЕДСКАЗ рассчитывает будущие значения параметра y в зависимости от заданного параметра x с использованием линейной регрессии.

Пример исполозования функции ПРЕДСКАЗ

Разберем пример применения функции ПРЕДСКАЗ. У вас есть отчет по выручке в разрезе регионов за 9 лет. Вам нужно составить прогноз продаж по каждому региону на следующие три года.

Напишем функцию ПРЕДСКАЗ для Воронежа

= ПРЕДСКАЗ (А11; В2:В10; А2:А10)

И получим искомый результат: 161,86

Закрепив абсолютные столбцы и строки $A11, B$2:B$10; $A$2:$A$10, протянем формулу на 2019, 2020 годы и на все регионы.

Результат получен за одну минуту.

Функция ТЕНДЕНЦИЯ во многом напоминает ПРЕДСКАЗ, но отличается от него использованием линейного тренда (вида y=ax+b ) для формирования прогноза.

Пример использования функции ТЕНДЕНЦИЯ

Для таблицы с выручкой в разрезе регионов применим функцию ТЕНДЕНЦИЯ, закрепим абсолютные строки и столбцы, протянем формулу на все периоды и регионы.

Как мы видим, результат прогнозирования с использованием функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ одинаков, значит, выручка по регионам в данном случае подчиняется линейному тренду.

Бонус-трюк с перемещением данных

Если вам необходимо перенести большой массив сложных формул, которые содержат относительные ссылки на другие ячейки, и вы не хотите провести остаток рабочего дня над исправлением ссылок после переноса, внимательно прочитайте следующий абзац.

Алгоритм быстрого перемещения данных:

  1. Выделите целевой массив.
  2. Зайдите в директорию «Найти и выделить» – «Заменить».
  3. Замените знак «=» на любой другой редко используемый знак, превратив тем самым формулы в текст.
  4. Перенесите массив.
  5. Выполните обратную замену.

Готово!

Как защитить данные в Excel

По умолчанию сводная таблица в Excel это все формулы и ячейки доступны для редактирования, в том числе для непреднамеренного случайного искажения.

Какие инструменты помогут защитить важные данные в Excel, на видео рассказывает Игорь Николаев, финансовый директор группы компаний «Интернест».

Это часть большого, полезного курса «Работа с отчетностью и бюджетами в Excel», который можно пройти в Высшей школе финансового директора. После обучения выдают удостоверение о повышении квалификации.

Excel – это как раз тот случай, когда десять минут осмысленной работы заменяют час «ручного» труда. Поэтому вложения в изучение Excel для финансиста трудно переоценить. Хорошим вариантом будет прохождение сотрудниками обучающих курсов у специалистов или организация внутрикорпоративного обучения. Поверьте, это в результате сэкономит вам не только массу времени, но и ощутимую долю бюджета.

Источник: https://www.fd.ru/articles/159112-8-funktsiy-ehsel-kotorye-pomogut-sokratit-rabotu-vdvoe

How to edit, evaluate and debug formulas in Excel

Баланс в экселе с формулами 2018

In this tutorial, you will learn a few quick and efficient ways to check and debug formulas in Excel. See how to use the F9 key to evaluate formula parts, how to highlight cells that reference or are referenced by a given formula, how to determine mismatched or misplaced parentheses, and more.

In the last few tutorials, we have been investigating different aspects of Excel formulas. If you've had a chance to read them, you already know how to write formulas in Excel, how to show formulas in cells, how to hide and lock formulas, and more.

Today, I'd to share a few tips and techniques to check, evaluate and debug Excel formulas that will hopefully help you work even more efficiently with Excel.

F2 key in Excel — edit formulas

The F2 key in Excel toggles between Edit and Enter modes. When you want to make changes to an existing formula, select the formula cell and press F2 to enter the Edit mode.

Once you do this, the cursor starts flashing at the end of the closing parenthesis in the cell or formula bar (depending on whether the Allow editing directly in cells option is checked or unchecked).

And now, you can make any edits in the formula:

  • Use the left and right arrows to navigate within the formula.
  • Use the arrow keys together with Shift to select the formula parts (the same can be done using the mouse).
  • Press Delete or Backspace to delete certain cell references or other elements of the formula.

When you are done editing, press Enter to complete the formula.

To exit the Edit mode without making any changes to the formula, press the Esc key.

Editing directly in a cell or in the formula bar

By default, pressing the F2 key in Excel positions the cursor at the end of the formula in a cell. If you prefer editing formulas in the Excel formula bar, do the following:

  • Click File > Options.
  • In the left pane, select Advanced.
  • In the right pane, uncheck the Allow editing directly in cells option under Editing Options.
  • Click OK to save the changes and close the dialog.

These days, F2 is often considered an old-fashioned way to edit formulas. Two other ways to enter the Edit mode in Excel are:

  • Double clicking the cell, or
  • Clicking anywhere within the formula bar.

Is Excel's F2 approach more efficient or does it have any advantages? Nope 🙂 Simply some people prefer working from the keyboard most of the time while others find it more convenient to use the mouse.

Whichever editing method you choose, a visual indication of the Edit mode can be found at the bottom-left corner of the screen. As soon as you press F2, or double click the cell, or click the formula bar, the word Edit will appear right below the sheet tabs:

Tip. Press Ctrl + A to jump from editing a formula in a cell to the formula bar. It only works when you are editing a formula, not a value.

In Microsoft Excel, F9 key is an easy and quick way to check and debug formulas. It lets you evaluate only the selected part of the formula by replacing it with the actual values that part operates on, or with the calculated result. The following example demonstrates Excel's F9 key in action.

Supposing you have the following IF formula in your worksheet:

=IF(AVERAGE(A2:A6)>AVERAGE(B2:B6),»Good»,»Bad»)

To evaluate each of the two Average functions included in the formula individually, do the following:

  • Select the cell with the formula, D1 in this example.
  • Press F2 or double click the selected cell to enter the Edit mode.
  • Select the formula part you want to test and press F9.

For example, if you select the first Average function, i.e. AVERAGE(A2:A6), and press F9, Excel will display its calculated value:

If you select only the cell range (A2:A6) and press F9, you will see the actual values instead of the cell references:

To exit the formula evaluation mode, press the Esc key.

Excel F9 tips:

  • Be sure to select some part of your formula before pressing F9, otherwise the F9 key will replace the entire formula with its calculated value.
  • When in the formula evaluation mode, don't press the Enter key because this would replace the selected part either with the calculated value or cell values. To retain the original formula, press the Esc key to cancel the formula testing and exit the formula evaluation mode.

The Excel F9 technique is especially useful for testing long complex formulas, such as nested formulas or array formulas, where it's difficult to understand how the formula calculates the final result because it includes a few intermediate calculations or logical tests. And this debugging method lets you narrow down an error to a particular range or function causing it.

Debug a formula by using the Evaluate Formula feature

Another way to evaluate formulas in Excel is the Evaluate Formula option that resides on the Formulas tab, in the Formula Auditing group.

As soon as you click this button, the Evaluate Formula dialog box will pop up, where you can inspect each part of your formula in the order the formula is calculated.

All you need to do is click the Evaluate button and examine the value of the underlined formula part. The result of the most recent evaluation appears in italics.

Continue clicking the Evaluate button until each part of your formula has been tested.

To end the evaluation, click the Close button.

To start the formula evaluation from the beginning, click Restart.

If the underlined part of the formula is a reference to a cell containing another formula, click the Step In button to have that other formula displayed in the Evaluation box. To get back to the previous formula, click Step Out.

Note. The Step In button is not available for a cell reference pointing to another formula in a different workbook. Also, it is not available for a cell reference that appears in the formula for the second time ( the second instance of D1 in the screenshot above).

When creating sophisticated formulas in Excel, you often need to include more than one pair of parentheses to specify the order of calculations or nest a few different functions.  Needless to say, it's very easy to misplace, omit, or include an extra parenthesis in such formulas.

If you miss or misplace a parenthesis and hit the Enter key attempting to complete the formula, Microsoft Excel usually displays an alert suggesting to fix the formula for you:

If you agree to the suggested correction, click Yes. If the edited formula is not what you want, click No and make the corrections manually.

Note. Microsoft Excel does not always fix missing or mismatched parentheses correctly. Therefore, always review the proposed correction carefully before accepting it.

To help you balance the parenthesis pairs, Excel provides three visual clues when you are typing or editing a formula:

  • When entering a complex formula that contains multiple sets of parenthesis, Excel shades parenthesis pairs in different colors to easier identify them. The outside parenthesis pair is always black. This can help you determine whether you have inserted the right number of parenthesis in your formula.
  • When you type the closing parenthesis in a formula, Excel briefly highlights the parenthesis pair (the right parenthesis you've just typed and the matching left parenthesis). If you've typed what you think is the last closing parenthesis in a formula and Excel doesn't bold the opening one, your parentheses are mismatched or unbalanced.
  • When you navigate within a formula using the arrow keys and cross over a parenthesis, the other parenthesis in the pair gets highlighted and formatted with the same color. In this way, Excel tries to make parenthesis pairing more evident.

In the following screenshot, I've crossed over the last closing parenthesis using the arrow key, and the outside parenthesis pair (black ones) got highlighted:

Highlight all cells referenced in a given formula

When you are debugging a formula in Excel, it might be helpful to view the cells referenced in it. To highlight all of the dependent cells, do the following:

  • Select the formula cell and press the Ctrl + [ shortcut. Excel will highlight all the cells your formula refers to, and moves the selection to the first referenced cell or a range of cells.
  • To navigate to the next referenced cell, press Enter.

In this example, I selected cell F4 and pressed Ctrl + [ . Two cells (C4 and E4) referenced in F4's formula got highlighted, and the selection moved to C4:

Highlight all formulas that reference a selected cell

The previous tip demonstrated how you can highlight all the cells referenced in a certain formula. But what if you want to do the reverse and find out all the formulas that refer to a particular cell? For example, you might want to delete some irrelevant or outdated data in a worksheet, but you want to make sure the deletion won't break any of your existing formulas.

To highlight all cells with formulas that reference a given cell, select that cell, and press the Ctrl + ] shortcut.

in the previous example, the selection will move to the first formula on the sheet that references the cell. To move the selection to other formulas that reference that cell, press the Enter key repeatedly.

In this example, I've selected cell C4, pressed Ctrl + ] and Excel immediately highlighted the cells (E4 and F4) containing a C4 reference:

Trace relationships between formulas and cells in Excel

Another way to visually display cells relating to a certain formula is to use the Trace Precedents and Trace Dependents buttons that reside on the Formulas tab > Formula Auditing group.

Trace Precedents — show cells that supply data to a given formula

The Trace Precedents button works similarly to the Ctrl+[ shortcut, i.e. shows which cells provide data to the selected formula cell.

The difference is that the Ctrl + [ shortcut highlights all cells referenced in a formula, while clicking the Trace Precedents button draws blue trace lines from the referenced cells to the selected formula cell, as demonstrated in the following screenshot:

To get precedents lines to appear, you can also use the Alt+T U T shortcut.

Trace Dependents — show formulas that reference a given cell

The Trace Dependents button works similarly to the Ctrl + ] shortcut. It shows which cells are dependent on the active cell, i.e. which cells contain formulas referencing a given cell.

In the following screenshot, cell D2 is selected, and the blue trace lines point to the formulas that contain D2 references:

Another way to display dependents line is clicking the Alt+T U D shortcut.

Tip. To hide the trace arrows, click the Remove Arrows button that resides right belowTrace Dependents.

When you are working with a large data set, you may want to keep an eye on the most important formulas in your workbook and see how their calculated values change when you edit the source data. Excel's Watch Window was created just for this purpose.

The Watch Window displays the cell properties, such as the workbook and worksheet names, cell or range name if any, cell address, value, and formula, in a separate window. In this way, you can always see the most important data at a glance, even when you are switching between different workbooks!

How to add cells to the Watch window

To display the Watch Window and add cells to monitor, perform the following steps:

  1. Select the cell(s) that you want to watch.

    Tip. If you want to monitor all the cells with formulas on an active sheet, go to the Home tab > Editing group, click Find & Replace, then click Go To Special, and select Formulas.

  2. Switch to the Formulas tab > Formula Auditing group, and click Watch Window.
  3. The Watch Window will appear and you click the Add Watch… button.

Watch Window notes:

  • You can add only one watch per cell.
  • Cells that have external references to other workbook(s) are displayed only when those other workbooks are open.

How to remove cells from the Watch Window

To delete a certain cell(s) from the Watch Window, select the cell you want to remove, and click the Delete Watch button:

Tip. To delete several cells in one go, press Ctrl and select the cells you want to remove.

any other toolbar, Excel's Watch Window can be moved or docked to the top, bottom, left, or right side of the screen. To do this, just drag the Watch Window using the mouse to the location you want.

For example, if you dock the Watch Window to the bottom, it will always show up just below your sheet tabs, and let you comfortably inspect the key formulas without having to repeatedly scroll up and down to the formula cells.

And finally, I'd to share a couple more tips that might prove helpful for evaluating and debugging your Excel formulas.

Formula debugging tips:

  1. To view a long formula entire without overlaying the contents of neighboring cells, use the formula bar. If the formula is too long to fit into the default formula bar, expand it by pressing Ctrl + Shift + U or drag its lower border using the mouse as demonstrated in How to expand the formula bar in Excel.
  2. To see all formulas on the sheet instead of their results, press Ctrl + ` or click the Show Formulas button on the Formulas tab. Please see How to show formulas in Excel for full details.

This is how to evaluate and debug formulas in Excel. If you know more efficient ways, please do share your debugging tips in comments. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

Источник: https://www.ablebits.com/office-addins-blog/2016/01/28/evaluate-debug-formulas-excel/

Excel для бухгалтера: примеры использования Эксель в бухгалтерии

Баланс в экселе с формулами 2018

В настоящее время количество различных отчетов, которые готовятся всеми подразделениями организаций, неуклонно растет. Очень часто на предприятиях осуществляется автоматизация отчетности на базе различных программных продуктов (SAP, 1С, Инталев и прочее).

Но даже в тех организациях, где система «автоматизирована», не обходятся без использования Excel.

Поскольку в программных продуктах, которые используются для автоматизации, заложены общие принципы построения отчетов, а каждое предприятие — уникально, сложно унифицировать всю отчетность в специализированных программных продуктах, а иногда — и невозможно это сделать.

И в этом случае на помощь приходят электронные таблицы Excel. Они позволяют дорабатывать отчеты, представлять информацию, полученную при выгрузке из той же 1С, в необходимом виде, а иногда и вообще служат основной программой ведения управленческой и прочей отчетности.

Программа Excel является одной из наиболее востребованных среди базового пакета Microsoft Office. Менеджер по продажам, используя таблицы, ведет систематизированную работу с клиентами.

Секретарь ведет учет всех локальных нормативных актов, корреспонденции или готовит диаграммы на производственное совещание. Экономист обрабатывает первичную информацию и вычисляет показатели работы компании.

Электронные таблицы — это самая популярная программа, используемая сотрудниками различных служб и различных направлений деятельности.

Особенно полезной программа оказывается для работников финансово-экономических отделов и бухгалтерии, если на нее возложены функции планирования и ведения управленческого учета.

Программа Excel для бухгалтера предоставляет возможность консолидации данных, позволяя сводить информацию из нескольких файлов в один. Позволяет выбрать необходимую информацию, создавая отдельные таблицы, либо скрывая данные в исходной таблице.

Позволяет систематизировать имеющуюся информацию по требуемым признакам и подбивать итоги.

Так или иначе бухгалтерия в Excel станет намного проще, если освоить несколько полезных приемов, которые смогут ускорить работу с данными. Узнайте, чем может быть полезен excel для бухгалтера в примерах, которые мы подготовили. Следующие упражнения в Эксель для работы бухгалтеров окажутся не просто полезными, а просто необходимыми.

Фундаментальный инструмент Excel

Кроме непосредственных обязанностей бухгалтеру могут добавить функции по подготовке коммерческих предложений, расчета договорных цен и прочее. Для выполнения расчетов необходимо применять различные коэффициенты и поправки, а также конвертировать цены. И главное, выполнять все действия быстро и без ущерба основным обязанностям.

Эксель для бухгалтера поможет в подготовке основного документа, который можно выполнять на основном листе, а дополнительные расчеты на отдельных.

Так, пересчитывая курс, в одной ячейке можно указать цену, во второй курс валюты, а в третьей задать формулу пересчета (= первая ячейка * вторая ячейка), далее нажать Enter и получить цену в рублях. В первом листе в нужной ячейке можно поставить “=”, перейти на второй лист и указать третью ячейку с итогом. Опять нажать Enter и получить результат.

Если необходимо провести такие расчеты по большому количеству пунктов, где изменяется только цена, то можно воспользоваться фундаментальным приемом Excel — автозаполнение формул, или протягивание.

Возможность протягивать формулы — одно из базовых функций программы. Она автоматизирует процесс подсчета данных в таблице, без многократного прописывания одной и той же формулы. Выполнять протягивание формул можно следующим образом.

В строке формул ставим равно и ссылку на ячейку из таблицы с исходными данными (=А3). После этого получим просто дублирование значения из таблицы. При протягивании этой ячейки получится копия таблицы с данным, которые будут изменяться соответственно со сменой информации в исходной таблице. Это пример протягивания ячеек без фиксирования диапазонов.

Можно закрепить ссылку, чтобы оставить ее неизменной при протягивании полностью, по строке или по столбцу. Фиксирование выполняется в строке формул с помощью знака $. Этот знак ставят перед той частью координат в ссылке, которую необходимо зафиксировать: $ перед буквой – фиксирование по столбцу — $С1 $ перед цифрой – фиксирование по строке — С$1

$ перед буквой и цифрой – полное фиксирование ячейки — $С$1.

Подсчет календарных дней

Excel может стать незаменимым помощником даже в таких простых действиях, как подсчет календарных дней. Бухгалтеру необходимо точно знать сколько дней было в том или ином периоде, чтобы рассчитать проценты, размер пени, неустойки, кредита и тому подобное.

Если это небольшой отрезок времени, то его просто посчитать, воспользовавшись календарем, но для выполнения постоянных расчетов такой формат достаточно неудобен. В таких ситуациях приходит Excel в помощь бухгалтеру.

Чтобы выполнить расчеты, необходимо выделить три свободных ячейки в таблице. В одну нужно записать начальную дату, во вторую конечную, а третью оставить пустой для получения результатов.

Источник: https://finacademy.net/materials/article/5-poleznykh-priemov-excel

14 лайфхаков в Excel, которые спасут бизнесмена-неудачника

Баланс в экселе с формулами 2018

Плюс Excel в том, что программа часто уже есть на компьютере сразу после покупки — производитель заранее установил стандартный пакет Office. Либо ты его скачал — сложно представить того, кто не пользуется Word из того же пакета. То есть Excel у тебя уже есть, как и элементарные познания в нём. Сейчас мы их расширим.

1. Выдели ячейки, которые влияют на результат подсчётов

При работе с большим количеством формул ты можешь легко в них запутаться: данные какой ячейки используются в подсчёте конкретной формулы?  Чтобы понять, откуда взялись те или иные цифры, на вкладке «Формулы» нажми на «Влияющие ячейки».  

2. Создавай наглядные мини-диаграммы

Excel 2010 даёт удобный инструмент — спарклайны или маленькие диаграммы. Благодаря им ты увидишь, как вырос доход компании или увеличился штат сотрудников. Чтобы найти спарклайны, перейди во вкладку «Вставка», выбери группу «Спарклайны» и нажми на «График» или «Гистограмма» — что больше понравиться.

3. Не бойся использовать функции

Функции — это конструкции, которые считают, проверяют условия, возвращают данные и пригодятся в других случаях, когда нужно быстро выполнить задачу.

К примеру, функции «МИН», «МАКС», «СРЗНАЧ» позволят тебе быстро узнать, у кого из сотрудников самая высокая или низкая зарплата,  сколько ты платишь своему персоналу в среднем.

Функции «ИНДЕКС» и «ПОИСКПОЗ» ищут нужную информацию в большом объеме данных. Например, если введешь название месяца в годовом финансовом отчёте, то получишь сведения о доходах или расходах за этот период.

Функция «СУММПРОИЗВ» позволит тебе быстро совершать расчеты. В таблице с количеством проданных за месяц товаров эта функция покажет сумму, на которую ты их продал.  

4. Быстро вводи данные одновременно в несколько ячеек

Выбери несколько ячеек, даже несмежных, введи нужную информацию и нажми Ctrl + Enter — данные появятся во всех выбранных ячейках.

5. Отображай данные из таблиц на карте

Excel 2013 позволяет отображать информацию на географической карте. Скачав плагин Bing Maps, ты получишь ряд недоступных до этого возможностей: увидишь, в каких регионах товар пользуется спросом или решишь, где открыть следующий филиал.

Чтобы найти плагин, на вкладке «Вставка» выбери «Магазин приложений».

6. Пользуйся автозаменой, чтобы работать быстрее

Полезно тем, кто постоянно вводит одну и ту же информацию. Вместо имени сотрудников достаточно ввести инициалы, а Excel самостоятельно заменит их полным именем.  Для этого перейди в «Файл» — «Параметры Excel» — «Правописание» — «Автозамена».

7. Заставь Excel анализировать вместо себя

Представь, что ты — владелец книжного магазина и должен продать новую книгу. За три месяца ты продал экземпляров на сумму 212 865 грн. За четвертый месяц ты хочешь продать на 300 000 грн. Excel посчитает за тебя необходимое количество книг. Используй для этого функцию «Подбор параметра» («Данные» — «Анализ «что — если»).

При нажатии всплывет окно с тремя полями для ввода: «Установить в ячейке», «Значение» и «Изменяя значение в ячейке». В первое ты должен ввести ячейку, где содержится сумма — 212 865 грн. Во второе — желаемый результат — 300 000, а в последнее — ячейку, которую для этого нужно изменить, то есть ту, где должно находиться число проданных книг за четвертый месяц. Несколько секунд — и готово.

Excel посчитал, что для достижения цели нужно реализовать 1937 экземпляров.

8. График работы в Excel

Удобное представление графика работы — диаграмма Ганта. Для начала тебе нужно составить таблицу, где следует указать время начала и окончания различных проектов и их длительность.

Далее, построй по этой таблице линейную диаграмму с накоплениями.

Немного модифицируй ее: сделай прозрачными  колонки, которые находятся ближе к левой оси — для этого кликни по ним правой кнопкой мыши, выбери из выпадающего окна «Формат ряда данных», перейди во вкладку «Заливка» и выбери «Нет заливки».

Далее, щелкни по левой оси правой кнопкой мыши и перейди в «Формат оси». Там поставь галочку рядом с пунктом «Обратный порядок категорий». Последний штрих — в настройках формата верхней оси поменяй формат даты на тот, который удобнее.

9. Используй готовые шаблоны

Кто сказал, что пользоваться чужим —  плохо? Миллионы людей работают в Excel, многие делятся своими наработками. Скачай готовые шаблоны учета дохода и расходов компании, расчета окупаемости, графика рабочих смен сотрудников и другие.  

10. Запароль важные документы

Перейди на вкладку «Рецензирование» — там есть функции «Защитить лист» или «Защитить книгу».

11. Импортируй данные из других источников

Это удобно, когда тебе нужно перенести данные из текстового файла или базы данных Access. Вручную это заняло бы много времени, а так пару кликов — и все готово. Для этого перейди на вкладку «Данные» и выбери, откуда хочешь импортировать информацию.

12. Используй сводные таблицы, чтобы сделать свой отчет информативнее

У тебя есть таблица, где указано какой товар и на какую сумму продал каждый менеджер в течение месяца.

Тебе нужно узнать, какая продукция пользовалась большим спросом или какой менеджер проявил себя лучше всех. Для этого используй сводные таблицы. Сначала перейди на вкладку «Вставка» и выбери «Сводные таблицы». Далее появится окно — просто нажми «ОК». После этого в окошке справа выбери то, что хочешь видеть в таблице.

Готово. Теперь ты можешь наглядно увидеть, какой товар продавался лучше, а также отследить, кто из менеджеров реализовал больше продукции.

13. Проверка данных помогает избежать ошибок

Excel настолько умный, что может даже запретить что-то делать, если ты дулаешь это неправильно, и предупредить об ошибках. Например, можно жёстко определить перечень поставщиков и запретить вводить других. Или поставить напоминание о том, что идентификационные код состоит из определенного количество символов. Ищи «Проверка данных» во вкладке «Данные».

14. Восстанавливай случайно закрытый без сохранения документ

Бывают моменты, когда в окошке «Сохранить изменения?» ты на автомате нажимаешь «Нет». Если это снова случилось, в Excel 2010 нажми на «Файл» и найди вкладку «Последние». В правом нижнем углу ты увидишь функцию «Восстановить несохраненные книги». В версии 2013 года следуй такому пути: «Файл» — «Сведения» — «Управление версиями».

Excel — удобная программа, которая считает, сортирует, строит графики и анализирует данные. Частному предпринимателю, у которго небольшой бизнес, не обязательно тратиться на узкоспециализированные программы и пытаться изучить их навороты — часто хватает знакомого со школы Excel, чью пользу тебе так и не объяснил стареющий профессор, привыкший к перфокартам.

Источник: https://businessviews.com.ua/ru/business/id/14-lajfhakov-v-excel-kotorye-spasut-biznesmena-neudachnika-1030/

Юридиада
Добавить комментарий