Прогрессия в эксель как сделать
Обновлено: 07.07.2024
Так получилось, что когда я начала рассказывать о специфике работы аналитика, я не смогла обойтись без статей об excel и поэтому на моем канале появилось несколько статей про excel (и их количество растет!). Excel это основа основ для работы с цифрами, инструмент гибкий и must have для большинства специалистов разных сфер.
Здесь я бы хотела выложить ссылки на мои статьи, посвященные именно этому инструменту. В каждой из статей я привожу пример того, как работает функция и показываю как выглядит сама формула. Каждую из них можно опробовать на месте и я советую это делать, ведь только на практике вы сможете закрепить информацию.
Теперь есть еще одна статья подборка по публикациям в формате видеороликов - Фишки Excel - подборка видеороликов .
Эту статью я планирую обновлять каждый раз, когда будет появляться новая статья. По сути, это некий справочник, который поможет вам найти то, что нужно именно вам.
Если вы знаете, на какую тему вам бы хотелось увидеть статьи - прошу, сообщите об этом в комментариях и я постараюсь раскрыть тему максимально подробно!
✔ Общие статьи про excel:
1. Excel за 5 шагов . В статье рассказываю об этапах изучения excel и о навыках, которые на каждом из этапов вы получаете.
2. Подборка книг для изучения excel. Здесь я показала три книги, которые мне очень помогли в развитии своих навыков excel.
3. Горячие клавиши в Excel - оптимизируй свою работу . Привожу полезные сочетания клавиш, которые ускорят вашу работу в excel (копировать, вставить, отменить последнее действие и пр.)
4. Джон Уокенбах - настоящий профессионал в мире Excel . Статья об одном из самых больших профессионалов в мире excel, авторе более 50 книг про эту программу и описание нескольких из них. Своих героев надо знать в лицо :)
6. Защита файла excel и "Инспектор документов" . В статье описаны виды защиты файла от просмотра и изменения.
✔ Визуализация в excel:
1. Два способа создать диаграмму в ячейках Excel . Построить диаграмму можно и в ячейке, смотрится минималистично, сделать просто.
2. Сила условного форматирования в Excel . Условное форматирование часто недооценивают. На простых примерах покажу, что с его помощью можно сделать.
3. Чередующаяся и шахматная заливка ячеек с помощью условного форматирования в Excel. В несколько кликов мыши можно создать необходимую заливку в формате "через строку", "через столбец" или шахматную заливку.
4. Спарклайны: мини-график в ячейке Excel . Показываю как создать спарклайн, а также рассказываю о возможности форматирования спарклайнов.
5. Приемы необычной визуализации в диаграммах Excel . Создание многоуровневой диаграммы и добавление символов подписи данных.
6. Создание собственного числового формата в Excel. Рассказываю про структуру формата ячеек при создании своего формата и показываю как его создать.
7. Как создать динамическую диаграмму . Создание динамической диаграммы на базе нескольких формул.
8. Диаграммы в Excel . Показываю для чего можно использовать основные виды диаграмм и их примеры.
9. Гистограмма в Excel . Рассказываю, для чего используются гистограммы и как их строить, а также редактировать.
✔ Power Query и Power Pivot:
1. Возможности Power Query в обработке данных . Для чего нужен Power Query, как с помощью него отредактировать таблицу на примере таблицы с заказами.
2. Power Query - загрузка данных из интернета . Power Query способен напрямую скачать любую таблицу со страницы в интернете - показываю как это сделать.
3. Введение в Power Pivot . Вводная статья о том, для чего предназначен Power Pivot, как загрузить в него данные и установить связи между таблицами.
✔ Работа с таблицами и полезные советы:
1. Операции с листом рабочей книги excel . Покажу как добавить новый лист, удалить, скрыть, отобразить и изменить цвет заливки листа.
2. Плюсы и минусы сводных таблиц . Показываю, на что необходимо обратить внимание при использовании сводных таблиц.
3. Срезы и временная шкала в сводных таблицах Excel . Показываю как создать сводную таблицу и подключить срез и временную шкалу.
4. Одновременный ввод данных на двух листах Excel, изменение цвета ярлыка листа и автозаполнение прогрессией. Покажу, как одновременно вводить значения на нескольких листах, менять цвет заливки ярлыка листа и как пользоваться автозаполнением с геометрической прогрессией.
5. Форма для ввода данных в Excel . Расскажу про инструмент для альтернативного ввода данных в таблицу.
7. Проверка данных - часть 2 . Это вторая часть статьи про Проверку данных, в которой показаны несколько интересных примеров использования этого инструмента.
9. Альтернатива для примечаний в Excel - как оставить пояснение к ячейке . Показываю, как не используя примечания, оставить комментарий к ячейке или формуле.
10. Выпадающий список в Excel . Выпадающий список нужен, чтобы упростить и ускорить ввод повторяющихся значений в ячейки, делается просто, за несколько кликов мыши.
11. Умные таблицы в Excel . Как создать "умную таблицу", в чем ее преимущества и как ее отформатировать.
12. На что способны скрытые функции - создание списка листов файла excel . Покажу, как с помощью одной скрытой функции создать список ваших листов excel.
13. Объединенные ячейки: как найти и избавиться . Показан простой способ отыскать объединенные ячейки и разбить их.
14. Добавление водяного знака на лист Excel . Покажу как добавить водяной знак в виде изображения на лист.
✔ Работа с функциями и формулами в excel:
1. Как пользоваться ВПР в Excel . Без ВПР мы никуда, это мощная функция для поиска данных и соединения нескольких таблиц. Просто и понятно на примере колл-центра показываю, как она работает.
2. Абсолютные и относительные ссылки в Excel . Показано, как работают абсолютные, относительные и смешанные ссылки.
3. Приемы подготовки данных к анализу в Excel . Покажу, как убрать лишние пробелы, дубликаты строк и разбить текст по ячейкам.
4. Лайфхаки работы со сложными формулами . Приведено 4 способа упрощения работы со сложными формулами, которые полезно использовать в своей работе.
5. Просто о функциях ИНДЕКС и ПОИСКПОЗ . Понятно демонстрирую, как работают эти функции по отдельности и на что они способны вместе.
6. Непопулярные, но такие полезные функции Excel для работы с текстом . Показываю, как можно использовать такие непопулярные функции как ЕТЕКСТ, ТИП и ПРОПНАЧ (включая то, как перевести формат текста из "зЕЛЕный Чай" в такой "Зеленый чай" за одну формулу).
7. Как объединить ячейки в Excel: конкатенация (&) и СЦЕПИТЬ . Несколько способов как соединить текст из нескольких ячеек в одну.
8. Как вывести текущую дату и время в Excel несколькими способами . Покажу, как вывести текущую дату и время в отчет и применить к ним необходимый формат данных.
9. Как подсчитать количество рабочих дней между двумя датами в Excel . Одной функцией можно рассчитать количество рабочих дней с учетом праздников и выходных.
10. Базовые функции работы с датами и временем в Excel . Покажу, как работают функции с датами и временем, как рассчитать долю от года и найти дату последнего дня месяца.
11. Вычисление определенного дня года в Excel . Как быстро определить порядковый номер дня в году и рассчитать остаток количества дней относительно конкретной даты.
12. Excel: извлечение части текста из ячейки . Как из текста ячейки вытащить первое слово или последнее, а может нужно вытащить все, кроме первого - ответы на вопросы есть в статье.
13. Скрытая, но мощная функция в Excel . Статья о скрытой функции РАЗНДАТ, которая позволяет рассчитать количество дней, месяцев и лет между двумя датами.
14. Такая многогранная функция Excel СЧЁТЕСЛИ . Рассказываю про то, как можно использовать функцию СЧЁТЕСЛИ для подсчета количества ячеек, соответствующих определенному условию.
15. Округление числовых значений . Показаны функции, с помощью которых можно округлять числовые значения.
16. Немного о присвоении имен . Покажу как присваивать имена ячейкам, диапазонам, константам и как это свойство можно использовать.
17. МОДА: подсчет наиболее часто встречающихся значений в Excel . Расскажу, как использовать функцию МОДА для подсчета часто встречающихся значений.
18. Циклическая ссылка в Excel . Что такое циклическая ссылка и как ее найти.
19. Виды ошибок в формулах Excel . Перечислены основные виды ошибок, которые возникают при расчете формулы и причины, их вызывающие.
20. Excel: подсчет определенных символов в ячейке . Отвечаю на вопрос, как посчитать количество определенных символов в ячейке и не ошибиться при учете регистра текста.
21. Генератор случайных чисел в Exce l . Покажу как просто сгенерировать список случайных чисел в excel.
22. Изменение регистра текста в ячейке Excel . Описано несколько простых способов изменения регистра текста в ячейке.
Разберёмся как создать и настроить линейный индикатор выполнения (прогресс-бар) в виде диаграммы в Excel.
В современную экономическую жизнь прочно вошли понятия КПЭ (ключевые показатели эффективности, или KPI) и дашборда, которые помогают нам увидеть насколько эффективно выполняются те или иные цели. Грамотная визуализация позволяет сделать это приятным и понятным глазу языком.
Для начала давайте поймем, что же это именно такое?
Сегодня предлагаю подробно рассмотреть линейный вариант графика, который представляет собой простую горизонтальную или вертикальную полосу:
Также в целом можно выделить 2 способа построения графика:
Построение линейного индикатора (прогресс бара)
Вариант 1. Прогресс бар без шкалы
Давайте приступим к построению и начнем с самого простого варианта.
Выделяем диапазон с данными A1:B2 и строим гистограмму с накоплением (в панели вкладок выбираем Вставка -> Диаграммы -> Линейчатая гистограмма с накоплением):
Как видим Excel не совсем правильно интерпретировал данные и построил график с 2 рядами данных, поэтому для корректного отображения поменяем местами строки и столбцы (выделяем диаграмму и в панели вкладок Конструктор выбираем Строка/Столбец), этим мы добьемся отображения всех данных в одному ряду:
Отлично, диаграмма уже начинает приобретать узнаваемый вид.
Далее устанавливаем минимальную и максимальную границы для оси (щелкаем правой кнопкой мыши по горизонтальной оси и попадаем в настройки Формата оси), как 0 и 1 соответственно, чтобы наша полоска полностью помещалась и показывалась на графике:
В результате мы получаем следующий вид графика:
Уберем ненужные на текущий момент детали в виде названия, дополнительных осей и прочего, чтобы они не отвлекали от построения (их, безусловно, можно не удалять и компоновать на ваше усмотрение):
Как мы видим, полученная полоска занимает не всю ширину диаграммы, снизу и сверху мы видим пустые белые полосы.
Поэтому, чтобы растянуть диаграмму на всю возможную ширину и убрать лишние полосы, установим боковой зазор для ряда равным нулю (выделяем любой ряд с данными, щелкаем правой кнопкой мыши и выбираем Формат ряда данных -> Параметры ряда):
В итоге получаем более компактный вид:
Остались небольшие детали, покрасим части полоски в подходящие цвета и добавим подпись данных на ряд:
Все готово, перейдем к следующему варианту.
Вариант 2. Прогресс бар со шкалой
Отличие этого варианта от предыдущего заключается лишь в дополнительной подложке под полоску, которая показывает уровни выполнения показателя, остальные части практически повторяют предыдущий вариант построения.
Для начала несколько модифицируем нашу таблицу и добавим на нее дополнительные ряды для построения шкалы:
В данном случае я указал шаг шкалы равным 10%, но можно поставить абсолютно любой по вашему усмотрению, главное чтобы сумма всех таких шагов давала 100% (10 шагов по 10% как в примере, или 20 шагов по 5% и т.д.).
Выделяем диапазон с данными A1:B11 и, как и в предыдущем примере, строим линейчатую гистограмму с накоплением:
Повторяем алгоритм и меняем строки и столбцы местами, чтобы диаграмма приобрела необходимый нам вид:
Как мы видим, у нас появилась достаточно длинная полоса, где в начале находится полоска, а в конце как раз разместилась шкала в виде маленьких разноцветных прямоугольников.
Так как шкала должна быть подложкой (т.е. находиться позади основного ряда), то поместим основной ряд на второстепенную ось, чтобы он всегда находился над шкалой. Для этого в настройке формата ряда данных указываем построить ряд по вспомогательной оси (именно для ряда с основной полоской):
В результате настройки типов осей получаем:
Далее также для обеих осей указываем 0 и 1 как минимальную и максимальную границы, чтобы график был ровно от 0% до 100%:
Убираем название, оси данных и прочие ненужные в данный момент детали, настраиваем нулевой боковой зазор:
Так как шкала на полученной диаграмме не видна за основной полоской, то для основного ряда с данными установим прозрачность (щелкаем по ряду правой кнопкой мыши, в контекстном меню выбираем Формат ряда данных -> Заливка и границы -> Заливка):
Также добавим подпись данных и получаем:
По усмотрению шкалу можно сделать не сплошной, а с разделителями, для этого установим для каждого ряда шкалы белую границу:
Спасибо за внимание!
Если у вас есть вопросы по теме статьи — пишите в комментариях.
Функция СМЕЩ на первый взгляд является довольно сложной для понимания, что зачастую отталкивает пользователя от ее использования и поэтому незаслуженно редко используется.
Тем не менее она может быть очень полезна (например, при создании динамических диапазонов) и при грамотном использовании существенно упрощает работу.
Описание функции СМЕЩ
СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.
Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:
К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на 0 (№2) вниз, на 0 (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.
Возникает логичный вопрос, какие же конкретно преимущества дает использование данной функции?
Одним из важных преимуществ является возможность оперировать с динамическими диапазонами, то есть с переменными размерами, которые могут увеличиваться или уменьшаться в процессе работы.
Пример использования функции СМЕЩ
Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки.
Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.
Пример 1. Функция ПОИСКПОЗ
Предположим, что у нас имеются данные с подневными продажами компании и мы хотим определить продажи на конкретное число.
Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:
Пример 2. Функция СУММ
Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:
При добавлении новых данных в таблицу результат будет автоматически пересчитываться:
Особенности применения
Недавно мы уже разбирались с вами в том, как сделать нумерацию в таблице MS Excel. Тогда речь шла об обычном последовательном числовом ряде, но что если вам понадобилось сделать что-либо нестандартное и изобразить, например, ряд непоследовательных чисел? Показать только четные или нечетные, а может и вовсе арифметическую или геометрическую прогрессию в Excel?
Заполнение таблицы Excel нечетными числами.
Следовательно нам необходимо для автоматического заполнения нечетными значениями строки или столбца в Excel в первой ячейке прописать 1, в во второй прописать 3. Таким образом разница будет =2.
1+2=3, далее
3+2=5, далее
5+2=7.
Вот вам и автозаполнение ячеек нечетными числами.
Заполнение таблицы Excel четными числами.
Аналогично выполняется и заполнение четными числами: в первой ячейке пишем 0, во второй 2, разница также +2, но начали мы с нуля. (Автозаполнение работает при старте с любой цифры хоть 5485) потянули и получили
0+2=2
2+2=4
4+2=6 и т.д.
Заполнение таблицы Excel с определенным шагом.
Все остальное работает по тому же принципу: к примеру, заполняя таблицу с шагом в 5 и начиная с 1, следующим числом в диапазоне мы получим (1+5=) 6. Аналогично, продолжая такой ряд, мы будем иметь на нем цифры: 11,16,21,26,31 и так далее.
Заполнение таблицы Excel в геометрической прогрессии
Да, обычным методом тут не обойтись. Что же делать? Будем использовать формулы MS Excel.
Вводим простую формулу для начала геометрической прогрессии
Геометрическая прогрессия в excel
У вас на экране теперь должен присутствовать простой ряд из чисел 2 и 4. Отлично, теперь выделяем нашу C6 (только её!) и протягиваем вниз насколько это необходимо, так же, как вы уже делали раньше (и о чем написано выше).
Заполнение таблицы Excel в геометрической прогрессии
Пример таблицы Excel с заполнением в геометрической прогрессии
Наша геометрическая прогрессия в Excel готова, можете убедится в этом, пересчитав вручную 🙂
Читайте также: