Трюки третьей части:
0:10 - Выборка уникальных записей
1:21 - Ограничение на ввод данных
3:10 - Мгновенное заполнение
4:20 - Многострочный текст
5:45 - Быстрое перемещение в больших таблицах
Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi
Трюки второй части:
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 Сцепить/Расцепить текстовые значения
СЧЁТЕСЛИ позволяет подсчитать количество ячеек, соответствующих указанному условию.
СЧЁТЕСЛИ(диапазон; критерий)
• Диапазон — диапазон ячеек по которым необходимо выполнить подсчет;
• Критерий — условие, определяющее какие ячейки нужно подсчитать. Условие должно быть в виде числа, ссылки на ячейку, выражения и т.д.
Пример использования:
Предположим у нас есть данные по продажам продуктов в магазине.
Подсчитаем количество категорий продуктов, относящихся отдельно к овощам и фруктам (критерий №1 в нашем примере).
В качестве диапазона выбираем ячейки A2:A13 (категория продукта) и задаем критерий подсчета F3 («Овощи»)
РАНГ возвращает позицию числа в отсортированном списке чисел.
РАНГ(число; ссылка; порядок)• Число (обязательный аргумент) — значение, для которого определяется ранг;
• Ссылка (обязательный аргумент) — массив или ссылка на список чисел, не числовые значения при этом в ссылке игнорируются;
• Порядок (необязательный аргумент) — число, определяющее способ упорядочения:
Если порядок равен 0 или опущен, то список определяется как отсортированный по убыванию.
Если порядок — любое число кроме нуля, то список определяется как отсортированный по возрастанию.
Пример работы формулы приведен на картинке ниже.
Пузырьковая диаграмма — это интересный и своеобразный тип диаграммы. Она строится по 3-м параметрам: горизонтальная ось, вертикальная ось и размер пузырька. Вот такого плана получается диаграмма:
1 шаг. Правильно созданная таблица:
Таблица данных должна иметь 3 столбца с измерениями:
1-е измерение будет использовано для построения горизонтальной оси.
2-е измерение — вертикальная ось.
3-е измерение — это размер пузырька.
Дополнительно может присутствовать столбец с текстовыми обозначениями — пусть, но для построения диаграммы он особенно не требуется.
Такая таблица у нас есть:
2 шаг. Строим диаграмму:
Выделяем исходные данные. В примере это ячейки В2:D7. Далее идем: вкладка Вставка — Точечная — Пузырьковая диаграмма. Я использовала объемную, но можно и плоскую:
Диаграмма на этом уже будет построена. Единственно, чтобы она была понятная, ее лучше настроить, добавив названия нужных элементов.
3 шаг. Настраиваем диаграмму:
Во-первых, следует подписать оси: кнопка Элементы диаграммы («зеленый плюсик») — Названия осей:
Затем для горизонтальной оси я добавила заголовок из ячейки В2, для вертикальной оси — из ячейки С2. Для этого нужно, выделив элемент с названием оси, перейти в строку формул, поставить там знак = («равно»), затем сделать щелчок на нужную ячейку:
Во-вторых, добавим подписи данных к пузырькам: кнопка Элементы диаграммы («зеленый плюсик») — Подписи (метки) данных — Дополнительные параметры. Откроется область для настройки подписей данных. Здесь можно выбрать, какие именно данные хотим отобразить у пузырька: можно показать любой из 3-х показателей или все сразу, и еще добавить наименования из столбца А, выбрав параметр Значения из ячеек:
Дополнительно можно выбрать стиль оформления, изменить название или сделать еще какие-нибудь настройки:
Телеграм канал, для новичков! - https://t.me/joinchat/og7xI9fRFqNmZWZi
В этом уроке я расскажу, как построить диаграмму для отображения одного-единственного показателя. Особенность диаграммы в том, что сохраняется возможность выбора, чей именно показатель отображать, а также для сравнения показатель отображается относительно максимального значения (для примера я взяла % от 100%, но можно использовать и другие значения).
Вот что получится в итоге:
Шаг 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
Excel - это не только ВПР и дашборды, но и забавно, а иногда - бессмысленно и беспощадно.
На Youtube есть канал, где его автор творит в екселе всякие админские непотребства. Сейчас он дошёл до отправки в телеграм сообщений о результатах мониторинга. Я иногда подбираю у него идеи, однако сильно чешусь в неприличных местах, когда вижу его код.
Увага: просмотр канала опасен для неокрепшей психики юных админов.
После просмотра видео, где он пингует список хостов, я подумал: а какого чёрта! И переписал это на свой лад.
Основная нагрузка - функция Ping
Function Ping(IP)
Ping = CreateObject("Wscript.Shell").Run("ping -n 1 -w 1000 " & IP, 0, True) = 0
End Function
Запускает внешний процесс, в котором выполняет собственно ping до нужного хоста.
В оригинале было так:
Весь остальной код - рюшечки вокруг этой функции: обход списка заданное количество раз, раскрашивание ячеек, ожидание паузы и проч.
Файл доступен по ссылке https://disk.yandex.ru/d/cA1XcK44Dx4uwQ
В сегодняшнем видео мы за тридцать минут рассмотрим все самые важные основы работы с формулами и функциями в Excel. Узнав и практикуя все советы из этого видео, ты сможешь вывести свой уровень работы с формулами в Excel на абсолютно новый, по-настоящему профессиональный уровень.
В этом видео мы рассмотрим как самые основы работы с формулами (например, относительность и абсолютность ссылок, использование смешанных ссылок, прописывание значений для формул, принцип их вычисления формул и т.д.), так и советы для уже продвинутых пользователей (такие, как, например, использование шорткатов и комбинаций клавиш, в том числе мнемоников, как быстро выбирать доступные функции и т.д.). Кроме того, мы рассмотрим, что из себя представляет специальная вставка при копировании, какие варианты специальной вставки существуют и в каких ситуациях их использовать.
Приятного просмотра! 😊
