MS, Libreoffice & Google docs

Условно выделим 3 варианта анализа чувствительности, доступные в Excel:

1. Чувствительность 1 показателя к изменению 1 параметра:

Просчитаем варианты изменения прибыли в зависимости от изменения цены. Для этого построим таблицу следующего вида, где в 10 строке укажем желаемые значения цены, а в ячейке B11 зададим ссылку на целевой показатель (прибыль):

Выделив указанный фрагмент запустим инструмент Таблицы данных на вкладке Данные

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

После нажатия ОК получаем сформированные варианты:

Т.е. при цене 5 руб прибыль составит 10 рублей, при цене 6 рублей — 20 рублей и так далее.. Самое время построить диаграмму:

2. Чувствительность нескольких показателей к изменению 1 параметра:

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

В данном случае программа просчитает как варианты прибыли, так и варианты выручки при изменении цены:

3. Чувствительность 1 показателя к изменению 2 параметров:

Таблицы данных могут также успешно применяться для 2 переменных. Например, определим зависимость прибыли от изменения одновременно цены и объема продаж. Для этого построим следующую таблицу:

По аналогии с предыдущими примерами, в ячейке B8 должна находиться ссылка на анализируемый показатель, т.е. на ячейку B5 (прибыль).

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

Получаем следующий результат. Здесь, кстати, хорошо подходит применение условного форматирования:

При цене 6 руб и объеме продаж 11 ед прибыль будет равна 22 руб, при цене 7 руб и объеме продаж 12 ед прибыль будет равна 36 руб и так далее.

С визуализацией здесь несколько сложнее, для отдельных случаев подойдет вид нескольких графиков:

Мой телеграм канал с фишками Excel - https://t.me/joinchat/og7xI9fRFqNmZWZi

Чтобы быстро округлять значения времени в Excel, удобно использовать тот факт, что время - дробная часть единицы (1 мин = 1/1440, 1 час = 1/24) и функции ОКРУГЛения

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

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

Символ амперсанда (&) в колонтитулах в Excel является служебным и на печать не выводится.

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

https://t.me/hacks_excel/1947

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

Делается это очень просто. Выбираем одну из имеющихся сводных таблиц (1) и во вкладке «Анализ» (2), щелкнув по треугольнику в выпадающем списке "Параметры" убираем галочку напротив пункта «Создать GetPivotData», просто нажав на него (3):

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

Вот и всё! Настолько просто можно отключить автовставку функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. При этом, обрати внимание на то, что измененная нами настройка является настройкой на уровне программы Excel, то есть если мы откроем любой другой файл Excel, в нём уже точно также данная функция будет отключена. Если же мы при этом откроем этот же файл на другом компьютере, где настройка «Создать GetPivotData» еще не была отключена, то там будет всё также вставляться рассмотренная функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Но не переживай! Все уже прописанные формулы не будут изменяться. В конце концов ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — это самая обычная функция, которая позволяет получать данные из сводных таблиц с помощью указания полей сводной таблицы вне зависимости от их физического расположения на рабочем листе.

Также предлагаю посмотреть это короткое видео, в котором всё описанное в этом посте наглядно показано:

Раньше странно было наблюдать, почему при автоматизации бизнес процессов заказчики боятся баз данных

Цепляние за эксель у многих происходит до последнего

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

Потом понял, что они даже по своему правы

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

Переход к базе данных это следующий уровень сложности, знаний для контроля над которым просто нет

Тут они уже нутром понимают, что обратной дороги не будет. Придётся зависеть от этих мутных ИТ-шников, с их sql запросами и прочей магией

А главное - не понятно где данные и как понять, что они защищены

В экселе - все понятно, вот файл, в нем закладки с табличками

А база данных это где?

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

А если база данных в "облаке"?

В газетах вон постоянно пишут про хакеров и как из облаков данные утекают

Нет, нам такой прогресс не нужен. Лучше эксель

Тут все надежно, проверено мудростью предков, и есть панацея от всех проблем: ctrl+alt+delete

Защита документа паролем:

Иметь дополнительную защиту никогда не помешает. Чтобы защитить документ паролем, перейдите во вкладку Файл, раздел Сведения и выберите опцию Защита документа.

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

Удобное скрытие данных:

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

— Шаг 1

Нажмите сочетание клавиш CTRL + K в любой ячейке, перейдите в раздел Место в документе и введите адрес любой ячейки, которая находится далеко от начала листа (например, GV150).

— Шаг 2

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

— Шаг 3

Перейдите по появившейся ссылке, введите данные, которые нужно спрятать и нажмите сочетания CTRL + ↑ и CTRL + ←, чтобы вернуться обратно к началу листа.

Как преобразовать даты в месяц?

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

— Шаг 1

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

— Шаг 2

Нажмите сочетание клавиш CTRL + 1 и перейдите во вкладку (все форматы).

— Шаг 3

В поле Тип введите ММММ. Если вам требуется также сохранить день — в этой же строке введите ДД, разделив 2 параметра точкой.

Ввод значений, не превышающих суммы:

В примере представлен простой рабочий лист с расчетом бюджета, составляющие которого лежат в диапазоне ячеек B1:B6. Запланированный бюджет хранится в ячейке E1. В момент, когда пользователь хочет изменить значение в любой из ячеек в диапазоне B1:B6, в случае если сумма (значение в ячейке E2) превысит установленный бюджет, пользователю будет выведено окно с ошибкой. Следующая формула проверки данных будет контролировать, чтобы сумма составляющих не превышала бюджета:

https://t.me/hacks_excel/2203