Точку данных в диаграмме (например, в линейчатой) можно заменить изображением
Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).
И Ctrl + V — вставляем изображение.
После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше.
И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".
Мини-курс "Магия новых функций Excel" 🔥
Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).
И Ctrl + V — вставляем изображение.
После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше.
И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".
Мини-курс "Магия новых функций Excel" 🔥
This media is not supported in your browser
VIEW IN TELEGRAM
Делаем кнопку группировки более наглядной
Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (подробнее про нее здесь) агрегирует только данные из видимых ячеек.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).
И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима (и в ней что-то есть), выдаем другую надпись ("Скрыть").
Мини-курс "Магия новых функций Excel" 🔥
Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (подробнее про нее здесь) агрегирует только данные из видимых ячеек.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).
И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима (и в ней что-то есть), выдаем другую надпись ("Скрыть").
=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A3)=0; "⬇️Подробнее"; "⬆️Скрыть")
Мини-курс "Магия новых функций Excel" 🔥
This media is not supported in your browser
VIEW IN TELEGRAM
Контрольное значение: отслеживаем значения ячеек в любом месте
Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе — добавьте эту ячейку в окно контрольного значения.
Лента инструментов:
Формулы — Окно контрольного значения
Formulas — Watch Window
В примере добавляем в Таблице в строке итогов сумму по всем заказанным товарам в окно контрольного значения, переходим на другой лист и меняем там цены некоторых товаров — и наблюдаем "в режиме онлайн", как меняется сумма заказа из-за изменения цен.
Мини-курс "Магия новых функций Excel" 🔥
Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе — добавьте эту ячейку в окно контрольного значения.
Лента инструментов:
Формулы — Окно контрольного значения
Formulas — Watch Window
В примере добавляем в Таблице в строке итогов сумму по всем заказанным товарам в окно контрольного значения, переходим на другой лист и меняем там цены некоторых товаров — и наблюдаем "в режиме онлайн", как меняется сумма заказа из-за изменения цен.
Мини-курс "Магия новых функций Excel" 🔥
Магия новых функций Excel: революция в формулах 🔥
Лемур напоминает: уже 18 ноября цена на новый курс вырастет до 2 300. Пока можно успеть купить за 890!
Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.
Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel
Лемур напоминает: уже 18 ноября цена на новый курс вырастет до 2 300. Пока можно успеть купить за 890!
Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.
Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel
This media is not supported in your browser
VIEW IN TELEGRAM
Как заполнить пустые ячейки (вниз, то есть значениями сверху)?
Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)
Выделяем столбец с пустыми ячейками (можно зажать Shift с активированной последней пустой ячейкой и кликнуть на заголовок)
Нажимаем F5 и далее «Выделить» — «пустые ячейки»
Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)
И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)
То есть в каждой пустой теперь ссылаемся на ячейку сверху.
Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)
Алгоритм в коротком видео (без звука).
Мини-курс "Магия новых функций Excel" 🔥
Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)
Выделяем столбец с пустыми ячейками (можно зажать Shift с активированной последней пустой ячейкой и кликнуть на заголовок)
Нажимаем F5 и далее «Выделить» — «пустые ячейки»
Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)
И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)
То есть в каждой пустой теперь ссылаемся на ячейку сверху.
Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)
Алгоритм в коротком видео (без звука).
Мини-курс "Магия новых функций Excel" 🔥
Forwarded from Ренат Шагабутдинов из МИФа
Визуализируй это: курс по визуализации данных в Excel
Тут неоднократно спрашивали про этот курс, так что делюсь: уже можно приобретать, а первого декабря откроется доступ сразу ко всему:
— 20 видео
— к каждому видео исходные и готовые файлы Excel
— текстовые материалы
— 6 самостоятельных работ
— Excel и Google Таблица с шаблонами диаграмм, в том числе не из уроков: вдохновляйтесь или подставляйте сразу свои данные и используйте
— Обзор литературы
Курс записан именно в Excel. В текстовом формате будут дополнения: что доступно и как работает в Google Таблицах / российском Р7-Офис. На скриншотах несколько примеров того, что внутри. На странице курса можно посмотреть один из уроков.
И там же купить:
https://www.mann-ivanov-ferber.ru/courses/visualization/
Тут неоднократно спрашивали про этот курс, так что делюсь: уже можно приобретать, а первого декабря откроется доступ сразу ко всему:
— 20 видео
— к каждому видео исходные и готовые файлы Excel
— текстовые материалы
— 6 самостоятельных работ
— Excel и Google Таблица с шаблонами диаграмм, в том числе не из уроков: вдохновляйтесь или подставляйте сразу свои данные и используйте
— Обзор литературы
Курс записан именно в Excel. В текстовом формате будут дополнения: что доступно и как работает в Google Таблицах / российском Р7-Офис. На скриншотах несколько примеров того, что внутри. На странице курса можно посмотреть один из уроков.
И там же купить:
https://www.mann-ivanov-ferber.ru/courses/visualization/
This media is not supported in your browser
VIEW IN TELEGRAM
Аргументами функции ВЫБОР / CHOOSE могут и... другие функции!
Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.
Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.
Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.
Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.
Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
This media is not supported in your browser
VIEW IN TELEGRAM
Нумеруем только видимые строки
Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.
Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.
В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).
А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.
Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.
_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.
Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.
В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).
А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.
Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)
$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.
_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Forwarded from Ренат Шагабутдинов из МИФа
Please open Telegram to view this post
VIEW IN TELEGRAM
This media is not supported in your browser
VIEW IN TELEGRAM
Как спрятать формулы?
Итак, у вас секретнейший алгоритм расчета чего-то там.
Выделяем ячейки, заходим в окно формата (Ctrl + 1) и там включаем соответствующий флажок — "Скрыть формулы".
После чего нужно защитить лист (с паролем, если вы настроены очень серьезно, хотя пароль и не обязателен).
И формулы не будет видно. Но они будут вычисляться, разумеется.
И если мы отключим защиту ячеек, влияющих на формулу (как в видео столбец F "Сумма"), то можно будет менять их значения даже при защите листа и формулы будут пересчитываться. Но мы их все равно не увидим 😸
Как отключить защиту отдельных ячеек? По умолчанию все ячейки защищаемые. Можно снять защиту для отдельных ячеек — там же, где и скрытие формул, в окне формата на вкладке "Защита".
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Итак, у вас секретнейший алгоритм расчета чего-то там.
Выделяем ячейки, заходим в окно формата (Ctrl + 1) и там включаем соответствующий флажок — "Скрыть формулы".
После чего нужно защитить лист (с паролем, если вы настроены очень серьезно, хотя пароль и не обязателен).
И формулы не будет видно. Но они будут вычисляться, разумеется.
И если мы отключим защиту ячеек, влияющих на формулу (как в видео столбец F "Сумма"), то можно будет менять их значения даже при защите листа и формулы будут пересчитываться. Но мы их все равно не увидим 😸
Как отключить защиту отдельных ячеек? По умолчанию все ячейки защищаемые. Можно снять защиту для отдельных ячеек — там же, где и скрытие формул, в окне формата на вкладке "Защита".
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Нарастающий итог: закрепляем только первую ячейку в диапазоне
Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.
1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")
2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.
1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")
2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)
3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке._ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».
Он помогает ответить на вопрос "какой должна быть переменная X, чтобы на выходе получить N". Допустим, у нас есть простейшая модель с выручкой и маржинальной прибылью. Есть параметры, введенные как значения (производство в штуках, цена и себестоимость) и есть результирующие показатели, которые вычисляются формулами.
Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из — допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Он помогает ответить на вопрос "какой должна быть переменная X, чтобы на выходе получить N". Допустим, у нас есть простейшая модель с выручкой и маржинальной прибылью. Есть параметры, введенные как значения (производство в штуках, цена и себестоимость) и есть результирующие показатели, которые вычисляются формулами.
Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из — допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥
Ура, курс "Визуализируй это" уже доступен!
Это не только 6 с половиной часов видео и файлы ко всем 20 урокам, но и вот такой набор примеров и шаблонов, оглавление (а точнее, только его часть) которого вы видите. Это и диаграммы/таблицы/спарклайны из уроков курса, и дополнительные примеры. Например, проектные диаграммы с рабочими днями в заголовке, с выделением выходных отдельным цветом, с разными вариантами визуализации прогресса.
Есть еще такой же набор в Google Таблицах, многие примеры не пересекаются (ведь есть отличия и в условном форматировании, и в спарклайнах, и в диаграммах).
После покупки все уроки и шаблоны будут доступны сразу:
https://www.mann-ivanov-ferber.ru/courses/visualization/
Это не только 6 с половиной часов видео и файлы ко всем 20 урокам, но и вот такой набор примеров и шаблонов, оглавление (а точнее, только его часть) которого вы видите. Это и диаграммы/таблицы/спарклайны из уроков курса, и дополнительные примеры. Например, проектные диаграммы с рабочими днями в заголовке, с выделением выходных отдельным цветом, с разными вариантами визуализации прогресса.
Есть еще такой же набор в Google Таблицах, многие примеры не пересекаются (ведь есть отличия и в условном форматировании, и в спарклайнах, и в диаграммах).
После покупки все уроки и шаблоны будут доступны сразу:
https://www.mann-ivanov-ferber.ru/courses/visualization/