Сообщество - MS, Libreoffice & Google docs
Добавить пост

MS, Libreoffice & Google docs

365 постов 11 506 подписчиков
559

5 интересных лайфхаков. Часть 5

Трюки пятой части:


0:13 - Детальный отчет из сводной таблицы

1:46 - Вычисляемые поля в сводной таблице

3:46 - Невидимая формула

4:54 - Именованная ячейка в формулах

6:50 - Именованная встроенная функция


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

71

Как сделать абсолютную ссылку в Excel

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


0:00 - Введение

0:18 - Что такое относительная адресация в Excel

3:35 - В каких случаях абсолютная ссылка предпочтительнее

4:13 - Как сделать абсолютную ссылку в Excel

5:27 - Как сделать таблицу умножения в Excel


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

284

Анализ чувствительности проекта в Excel

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


Рассмотрим элементарную экономическую модель в Excel по формированию прибыли от реализации продукции:

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

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

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост
2. Чувствительность нескольких показателей к изменению 1 параметра:

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост
3. Чувствительность 1 показателя к изменению 2 параметров:

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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


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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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


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

Анализ чувствительности проекта в Excel Таблица, Microsoft Excel, Длиннопост

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

Показать полностью 9
144

Пузырьковая диаграмма

Пузырьковая диаграмма — это интересный и своеобразный тип диаграммы. Она строится по 3-м параметрам: горизонтальная ось, вертикальная ось и размер пузырька. Вот такого плана получается диаграмма:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост
1 шаг. Правильно созданная таблица:

Таблица данных должна иметь 3 столбца с измерениями:


1-е измерение будет использовано для построения горизонтальной оси.

2-е измерение — вертикальная ось.

3-е измерение — это размер пузырька.


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


Такая таблица у нас есть:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост
2 шаг. Строим диаграмму:

Выделяем исходные данные. В примере это ячейки В2:D7. Далее идем: вкладка Вставка — Точечная — Пузырьковая диаграмма. Я использовала объемную, но можно и плоскую:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост

Диаграмма на этом уже будет построена. Единственно, чтобы она была понятная, ее лучше настроить, добавив названия нужных элементов.

3 шаг. Настраиваем диаграмму:

Во-первых, следует подписать оси: кнопка Элементы диаграммы («зеленый плюсик») — Названия осей:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост

Затем для горизонтальной оси я добавила заголовок из ячейки В2, для вертикальной оси — из ячейки С2. Для этого нужно, выделив элемент с названием оси, перейти в строку формул, поставить там знак = («равно»), затем сделать щелчок на нужную ячейку:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост

Во-вторых, добавим подписи данных к пузырькам: кнопка Элементы диаграммы («зеленый плюсик») — Подписи (метки) данных — Дополнительные параметры. Откроется область для настройки подписей данных. Здесь можно выбрать, какие именно данные хотим отобразить у пузырька: можно показать любой из 3-х показателей или все сразу, и еще добавить наименования из столбца А, выбрав параметр Значения из ячеек:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост

Дополнительно можно выбрать стиль оформления, изменить название или сделать еще какие-нибудь настройки:

Пузырьковая диаграмма Microsoft Excel, Таблица, Длиннопост

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

Показать полностью 5
373

5 интересных лайфхаков. Часть 2

Трюки второй части:


0:15 - Быстрый поиск листов и их копирование

1:12 - Скрыть содержимое ячейки

2:10 - Функция ТЕКСТ

3:30 - Умножение в ячейке (Специальная вставка)

4:17 - Быстрое добавление данных в диаграмму


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

91

5 СОВЕТОВ по оформлению таблицы в Excel

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


0:18 - Стили таблицы

1:05 - Стили ячеек

2:55 - Темы книги

3:53 - Очистка форматирования

4:19 - Диагональ в заголовке таблицы

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

141

Диаграмма-показатель

В этом уроке я расскажу, как построить диаграмму для отображения одного-единственного показателя. Особенность диаграммы в том, что сохраняется возможность выбора, чей именно показатель отображать, а также для сравнения показатель отображается относительно максимального значения (для примера я взяла % от 100%, но можно использовать и другие значения).


Вот что получится в итоге:

Шаг 1. Подготовка таблицы и среза:

1. Имеем простую таблицу (рисунок 1).


2. Чтобы к таблице добавить срез, ее нужно преобразовать в «умную» таблицу (Ctrl+T). Затем на вкладке Конструктор нажимаем Вставить срез, выбираем Фио — ОК. (рисунок 2).


*Примечание. Команда Срез для «умных» таблиц появилась в версии Excel 2013, в более ранних версиях можно сделать из первоначальной таблицы сводную таблицу, а затем добавить срез для сводной таблицы.


3. Также нужна будет Строка итогов, ее тоже включаем на вкладке Конструктор (рисунок 3).


Теперь, используя Срез по фамилиям, можно фильтровать таблицу по нужной фамилии. Ниже карусель с рисунками по действиям:

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Шаг 2. Готовим таблицу для диаграммы:

Для диаграммы нам понадобится таблица, ссылающаяся на отфильтрованные данные.


1. Нужно выбрать одну фамилию в срезе, чтобы применить фильтр к таблице (рисунок 1 ниже).


2. В свободном месте на листе делаем новую таблицу из двух столбцов. В первом столбце будут только единицы (1). Количество таких единиц = количеству секторов диаграммы. Если хотите сделать, как часы, будет 12 единиц. Я сделала 8 (рисунок 1).


3. Напротив первой единицы делаем ссылку на итоговую строку «умной» таблицы. Обратите внимание, как выглядит ссылка. Ссылка не должна быть вида А1, она должна быть именно ссылкой на [Итоги];[Выполнение плана] (рисунок 2).


4. Напротив второй единицы вносим формулу =1-'предыдущая ячейка' (рисунок 3).


На этом таблица готова. Ниже карусель с картинками по этому шагу:

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Шаг 3. Создание диаграммы:

1. Выделяем таблицу с единицами, создаем кольцевую диаграмму: Вставка — Круговая — Кольцевая (рисунок 1). Вид получившейся диаграммы показан на рисунке 2.


2. Далее нужно настроить диаграмму. Выделяем внутренний ряд на диаграмме, на вкладке Формат применяем светлую заливку (я решила сделать диаграмму в зеленых тонах, поэтому здесь выбрала светло-зеленый цвет) (рисунок 3).


3. Во внешнем ряду диаграммы выбираем левый сектор, для него заливку убираем совсем: Формат — Заливка фигуры — Нет заливки (рисунок 4).


4. Цвет же основного (правого) сектора внешнего кольца можно сделать, наоборот, более ярким (рисунок 5).


5. Пускаем внешний ряд по вспомогательной оси: вкладка Конструктор — Изменить тип диаграммы — Комбинированная. Ряд 2 — включить параметр Вспомогательная ось для ряда 2. (рисунок 6).


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


Карусель с рисунками:

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Шаг 4. Дорабатываем диаграмму:

1. Убираем легенду (кнопка + справа, снять флаг Легенда) (рисунок 1 ниже).


2. Двойным кликом на любом секторе диаграммы открываем область Формат ряда данных, уменьшаем диаметр отверстия, я поставила 50% (рисунок 2). Внешнее кольцо становится толще.


3. Внутреннее кольцо тоже надо сделать таким же по толщине. В области Формат ряда данных выбираем Параметры ряда — Ряд 1 (рисунок 3), затем снова устанавливаем диаметр отверстия 50% (рисунок 4).


4. Добавляем надпись с отображением процента. Саму надпись можно найти на вкладке Вставка (или Формат) — Надпись (рисунок 5).


5. Теперь ее надо правильно привязать к ячейке с процентом. Для этого надпись должна быть выделена целиком (курсор внутри стоить НЕ должен!), затем в строке формул нужно ставить «=» и сделать ссылку на ячейку с процентом (первая ячейка в правом столбце вспомогательной таблицы) (рисунок 6).


6. Дальше надпись нужно поместить в центр диаграммы, а затем можно настроить по своему смотрению: изменить шрифт, размер, цвет букв (рисунок 7):

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост
Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост

Также дополнительно можно изменить настройки среза (вкладка Срез): цвет, наименование, размер кнопок. Можно изменить название самой диаграммы. Можно переместить диаграмму со срезом на отдельный лист, или сделать любые другие настройки по своему желанию. Я сделала так:

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост

Несмотря на то, что статья такая объемная получилась, реальное время, потраченное на создание этой диаграммы — всего лишь несколько минут. Поэтому в помощь вам еще ниже небольшое видео (без звука) по всем шагам:

Диаграмма-показатель Microsoft Excel, Диаграмма, Видео, Длиннопост

Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

Показать полностью 21
197

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем

Наверняка тебе уже доводилось встречаться с функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel. Эта функция автоматически вставляется в ячейку, если мы прописываем формулу и ссылаемся на одну из ячеек сводной таблицы.


Так, в следующем скриншоте я хотел сослаться на ячейку С3, но вместо привычной нам ссылки в итоге была вставлена функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

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


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

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

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

«ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ» в Excel – Отключаем Microsoft Excel, Таблица, Видео, Длиннопост

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


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

Показать полностью 1 1
205

Функции и фишки Excel #3

Использование именованных функций:
Любую функцию или набор функций в Excel вы можете поместить в выбранный вами именованный диапазон.


Для этого необходимо перейти на вкладку Формулы → Диспетчер имён → Создать.


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

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

Функции и фишки Excel #3 Таблица, Microsoft Excel, Видео, Длиннопост

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

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

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

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

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

https://t.me/hacks_excel/1947

Показать полностью 2
157

Формулы для расчета НДС в Excel

Рассматриваем способы расчета НДС в Excel

Несмотря на всю простоту в расчетах - запутаться, где какую применить формулу - проще простого.

Из этого видео вы узнаете о том, как выделить НДС из Суммы в excel, как рассчитать НДС в excel, как добавить к сумме НДС в Excel


Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi

Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!