excelpro

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

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

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

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

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

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

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

В этом видео вы узнаете о 5 актуальных и полезных трюках в Excel:

0:09 Автоподбор по ширине столбца/строки

1:07 Маркер-формула (быстрое протягивание)

2:04 Создание "Умной таблицы"

4:40 Выпадающий список

6:20 Сцепить/Расцепить текстовые значения

https://t.me/Excelshik/39

РАНГ возвращает позицию числа в отсортированном списке чисел.

РАНГ(число; ссылка; порядок)

• Ссылка (обязательный аргумент) — массив или ссылка на список чисел, не числовые значения при этом в ссылке игнорируются;

• Порядок (необязательный аргумент) — число, определяющее способ упорядочения:

Если порядок равен 0 или опущен, то список определяется как отсортированный по убыванию.

Если порядок — любое число кроме нуля, то список определяется как отсортированный по возрастанию.

Пример работы формулы приведен на картинке ниже.

https://t.me/hacks_excel/1974

1 шаг. Правильно созданная таблица:

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

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

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

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

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

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

2 шаг. Строим диаграмму:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 2. Готовим таблицу для диаграммы:

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

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

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

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

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

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

Шаг 3. Создание диаграммы:

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

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

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

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

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

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

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

Шаг 4. Дорабатываем диаграмму:

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

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

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

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

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

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

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

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

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

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

Стоит отметить, что при выделении 1 ячейки команда произведет автозамену на всем листе! Чтобы Excel «понял», что необходима именно произвести замену в конкретных ячейках до запуска команды нужно выделить более 1 ячейки:

2. При помощи формулы (динамический способ).

Синтаксис функции следующий:

=ПОДСТАВИТЬ(исходная ячейка; «заменяемый текст»; «замещающий текст»)

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

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

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

После чего укажем ячейку 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