​​Делаем ссылку

Самая неочевидная функция, которую я знаю в Excel - это ДВССЫЛ. Она преобразует текст в ссылку на ячейку. Синтаксис:

= ДВССЫЛ (ссылка на ячейку; а1)

ссылка на ячейку - если указываете ссылку в виде текста, то используйте кавычки “”. Если ссылка в виде текста расположена в другой ячейке, то можете указать эту ячейку.
а1 - необязательный аргумент, можно пропускать. Режим работы: 0 если стиль R1C1, 1 если отключен.

Допустим, в адресе А2 значение 10.
Если вы пропишите = ДВССЫЛ (”А2”), то вы сошлетесь на адрес А2, и получите в ответ 10.

Усложним задачу. В адресе А2 значение 10, в адресе В2 указано значение А2.
= ДВССЫЛ (В2) также выдаст вам в ответ 10.

То есть функция видит, что в ячейке В2 находится текст А2 и принимает это как ссылку на ячейку, где остается 10.

Увидимся в следующем посте. Видео 👇

​​Динамические выпадающие списки

Рассмотрим более рабочий кейс использования ДВССЫЛ. Сделаем динамические выпадающие списки.
Сделайте 2 столбца с именами: город и страна. Каждый столбец содержит свои значения.

Каждый столбец поочередно превратите в таблицы.
Далее, перейдите на вкладку ленты Конструктор таблицы и в поле Имя таблицы укажите по-очередно “Страна” и “Город”.
На вкладке Формулы, вы можете проверить свои таблице по функции Диспетчер имен. Что дальше?

Мы хотим сделать так, чтобы в D1 мы могли указывать имя таблицы и система давала возможность выбирать из списка либо города, либо страны.
То есть если в D1 указана Страна, то дает выбрать Австрию, Швецию и др. Если в D1 указан Город, то дает Москву, Пермь и др.

1. Выделяем список ячеек, в которых будут выпадающие списки.

2. Переходим на вкладку Данные - Проверка данных.

3. В поле Тип данных укажите Список.

4. В поле источник указывайте: = ДВССЫЛ (D1)

5. В ячейке D1 пишем Страна и система автоматически сошлется на значения из таблицы “Страна”. То же самое и для городов.

Готово. Теперь в вашем арсенале есть динамические выпадающие списки!
Видео👇

Хотите совет на 1 млн?

Учите шорткаты, они же горячие комбинации. Многие думают, что это секунды, которые им не нужны...это ошибка🙈

Применение шорткатов - один из базовых навыков, по ним считывают специалистов и людей, умеющих ценить время других.
В-общем не буду тянуть, держите мой давний, но ничуть не устаревший пост по шорткатам Excel: https://t.me/helloexcel/201

Бонусы:
▫️Win + L - блокирует экран от любопытных глаз.
▫️Win + D - сворачивает все окна. Очень здорово, когда нужно все свернуть за 1 секунду.

Непростой дайджест

Вспомним сложные и интересные материалы по продвинутым возможностям Excel. В них мы получали информацию о валютах и акциях с сайта ЦБ РФ и базы данных Московской биржи.

▫️Загружаем курс валюты с сайта ЦБ - https://t.me/helloexcel/236

▫️PRO. Получаем данные с MOEX. Часть 1- https://t.me/helloexcel/237

▫️PRO. Получаем данные с MOEX. Часть 2 - https://t.me/helloexcel/245

Прожмите крутана 😎 если такие материалы вам нравятся. Постараюсь добавить новых PRO.

​​Трюки мистера Excel

Есть такой дядька, Билл Джелен, больше известен под псевдонимом и одноименным сайтом Mr.Excel).

Билл - признанный гуру в работе с Excel. Мне нравятся его простые трюки, поэтому я решил адаптировать пару приемов Билла для вас.
Если вам понравится, то буду добавлять новые посты от мистера Excel. Поехали!

Подстановка пустых строк

Проблема: руководитель хочет, чтобы я добавил пустую строку под каждую строку с данными.

1. Рядом со своим списком создаете технический столбец Сортировка. Как вы уже догадались, мы решим проблему через сортировку таблицы.

2. Напротив данных протягиваете числовой диапазон от 1,2,3 и до n чисел. Таким образом каждая строка будет иметь свой порядковый номер.

3. Далее полностью копируете числовой диапазон и дубль помещаете под последнее значение в столбце Сортировка.

4. Переходите в Настраиваемую сортировку. По столбцу Сортировка сортируете значения по возрастанию.

5. Готово! Теперь под каждой строкой со значением есть пустая строка для заполнения. Остается только удалить технический столбец Сортировка.

Как всегда, показываю на видео👇

​​Подводим промежуточные итоги

Уверен каждый из вас знает как работает функция СУММ. Функция жестко привязывается к диапазону с данными и считает их сумму.
Проблема в том, что если вы начнете использовать фильтр, и выберете одно из несколько значений, то функция не пересчитает результат.

Это важная менеджерская задача - сделать динамический отчет, который будет показывать результаты с учетом фильтрации.
Проблему решает функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис:

= ПРОМЕЖУТОЧНЫЕ.ИТОГИ (номер функции; ссылка1; ссылка2;…)

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

▫️от 1 до 11 - режимы учета итогов включая скрытые вручную строки;

▫️от 101 до 111 - режимы учета итогов без скрытых строк.

Описание режимов:

▫️1 / 101 - СРЗНАЧ

▫️2 / 102 - СЧЁТ

▫️3 / 103 - СЧЁТЗ

▫️4 / 104 - МАКС

▫️5 / 105 - МИН

▫️6 / 106 - ПРОИЗВЕД

▫️7 / 107 - СТАНДОТКЛОН

▫️8 / 108 - СТАНДОТКЛОНП

▫️9 / 109 - СУММ

▫️10 / 110 - ДИСП

▫️11 / 111 - ДИСПР

ссылка1, ссылка2…. - указываете диапазоны ячеек для расчет промежуточных итогов, возможно указать сразу несколько.

Посмотрите пример расчет промежуточной суммы и среднего значения в видео 👇

​​Сортировка с пользовательским порядком

Продолжаем разбирать отличные трюки от Mr.Excel. Адаптация статьи о том, как создавать свой собственный порядок сортировки.

Проблема: мой руководитель хочет, чтобы я отсортировал отчет по географическому признаку. В моем ежегодном отчете обычно сначала указываются результаты по США, затем по Европе и только потом по Австралии. Мне нужно отсортировать так, чтобы страны отображались как США, Англия, Франция, Германия и Австралия.

Решение:

1. В пустом диапазоне ячеек создайте список для индивидуальной сортировке. В рамках примера это будут значения: США, Англия, Франция, Германия и Австралия.

2. Перейдите на вкладку ленты Файл - Параметры - Дополнительно. Найдите в группе “Общие” строку Создать списки для сортировки и заполнения. Нажмите на кнопку Изменить списки.

3. Если вы выбрали данные на шаге 1, поле ссылки рядом с кнопкой Импорт уже содержит ячейки, содержащие ваш список. Нажмите Импорт.

4. Нажмите Ок дважды.

5. Теперь выделите диапазон для сортировки и перейдите на вкладку Данные - Сортировка.

6. В столбце Порядок выбирайте Настраиваемый список, далее найдите список стран. Нажмите Ок.

7. Готово. Теперь все данные отсортированы по вашему списку в порядке: США, Англия, Франция, Германия и Австралия.

Те же действия в видео 👇

​​Сортировка по дням недели

Hello, friends!
Продолжаем адаптацию материалов Mr.Excel.

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

Решение:

Сортируя дня недели, вы не увидите порядок от понедельника до воскресенья, а лишь получите алфавитную сортировку.
В Excel встроены Настраиваемые списки для месяцев и дней.

1. Выберите диапазон с днями недели для сортировки.

2. Выберите вкладку ленты Данные - Сортировка.

3. Выберите столбец с днями недели и Сортировать по значениям. Справа в столбце Порядок выберите Настраиваемый список.

4. Выберите нужный порядок Понедельник, Вторник, Среда в диалоговом окне Настраиваемый список. Нажмите «ОК».

Готово, Excel правильно отсортирует данные.
Если хотите создать свой список значений с уникальным порядком, то воспользуйтесь материалом: https://t.me/helloexcel/274

Видео 👇

Подписывайтесь на Hello Excel

​​Быстрый поиск отличий в списках

Перед вами 2 списка значений, с первого взгляда они содержат одинаковые значения, однако если приглядеться, то различия есть.
Вы хотите быстро понять какие ячейки содержат различные данные.

1. Выделите 2 списка.

2. Нажмите Ctrl + G.

3. Нажмите кнопку Выделить.

4. В открывшемся окне поставьте чек-бокс на функции отличия по строкам и нажмите Ок.

5. Excel выделит ячейки с различиям, вам достаточно их выделить среди других. Перейдите на заливку ячеек на вкладке ленты Главная и выберете подходящий вам цвет заливки.

6. Готово. Теперь вы видите различия в списках.

Это неплохой и быстрый способ найти различия в больших списках значений без применения формул. Посмотрите в видео👇

Подписывайтесь на Hello Excel

​​Поиск формул и связей

Часто приходится корректировать файлы коллег, клиентов, внутри которых есть формулы Каждый шаг по такой таблице может создать дополнительные проблемы в виде нарушенных связей и битых ссылок.
Это легко упустить, когда размер таблицы измеряется сотнями столбцов и строк.

Попробуем обнаружить связи в таблице через уже знакомую комбинацию Ctrl + G.
По шагам:

1. Нажмите Ctrl + G.

2. Нажмите кнопку Выделить.

3. Чтобы выделить формулы, поставьте чек-бокс формулы и нажмите Ок. Система выделила ячейки с формулами. Покрасьте их желтой заливкой на вкладке ленты Главная.

4. Теперь обнаружим ячейки, которые используются в формулах. Повторите те же действия с Ctrl + G, только поставьте чек-бокс влияющие ячейки. Покрасьте ячейки оранжевой заливкой.

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

Подписывайтесь на Hello Excel

Введение в статистику

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

Приступайте к первой функции ⬇️

​​Делим выборку пополам. Функция МЕДИАНА

Все мы привыкли оценивать данные через среднее. Будь то средняя продолжительность жизни, средний доход или среднее количество воды, выпиваемое человеком за жизнь. Как это посчитать среднее в Excel вы уже знаете.

Однако использовать для оценки только среднее опрометчиво - среднее включает минимумы и максимумы. Это хорошо прослеживается в статистике по среднему доходу в стране. Смотришь на цифры и удивляешься насколько они отличаются от реального положения. Это происходит потому что вся выборка включает в себя максимальные значения.

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

Если вернемся к примеру с доходом по стране. Допустим, медиана - 35 000 рублей.

Это означает что ровно у 50% опрошенных доход меньше 35 000 рублей. А у второй половины доход выше 35 000 рублей.

В Excel функция так и называется МЕДИАНА. Синтаксис:

= МЕДИАНА (число1; число2 …)

число1; число2 … - диапазоны ячеек, для которых необходимо посчитать медиану.

Посмотрите пример с расчетом медианного возраста из выборки опрошенных. В выборке 12 значений, медианой будет тот возраст, который поделит список на 6 участников младше медианы и 6 участников старше👇

Подписывайтесь на Hello Excel

​​Находим наиболее частые значения. Функция МОДА

На предыдущем примере с выборкой по возрасту попробуем еще одну статистическую функцию - МОДА.
Мода - еще одно понятие из статистики и означает наиболее частое повторение в выборке.
Если ваша выборка равна 5, 6, 7, 5, 8, то модой будет значение 5. Оно встречается 2 раза в выборке.

▫️В Excel 2007 и ранних версиях функция называется МОДА.

▫️В более поздних версиях она называется МОДА.ОДН.
Синтаксис:

= МОДА.ОДН (число1; число2 …)

число1; число2 … - диапазоны ячеек, для которых необходимо посчитать моду.

На примере в выборку входит 12 значений и 2 раза в выборке встречается значение 28. Это и есть мода. Посмотрите видео👇

Подписывайтесь на Hello Excel

Навигация канала обновлена или как говорится "amigos, navegación actualizada!"
Здесь вы найдете все посты канала, сгрупированные по темам.

https://telegra.ph/Navigaciya-09-10-2

​​Массовое форматирование по образцу

Как часто вы используете функцию Формат по образцу? Если часто, то следующий лайфхак сильно упростит вашу работу.

1. Выделяйте ячейку, формат которой вы хотите перенести.

2. Дважды нажмите на Формат по образцу. Массовый режим переноса формата включен 👍

3. Теперь нажимайте поочередно на ячейки, которые хотели бы отформатировать по образцу.

Больше не нужно нажимать на “кисть” десятки раз. Видео👇

Подписывайтесь на Hello Excel
#кейсы

​​Кейс. Перевернуть диапазон ячеек с помощью функции массива

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

Если изменятся данные, то вам необходимо изменить и транспонированный диапазон. Неудобно. В Excel есть функция ТРАНСП, она позволяет автоматически перевернуть (транспонировать) нужный вам диапазон ячеек.

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

По шагам:

1. Выделите диапазон ячеек, куда вы хотите транспонировать ваш диапазон с данными. Обратите внимание, что размер диапазона должен совпадать с размером исходного диапазона. 8 ячеек по вертикали? Значит выделяйте 8 ячеек по горизонтали.

2. Нажмите = и начните вводить формулу: = ТРАНСП (ссылка на массив). В скобках указываете исходный диапазон.

3. Не спешите подтверждать формулу. Так как это формула массива, то для ее работы необходимо завершить ввод формулы комбинацией Ctrl + Shift + Enter. После нажатия вокруг формулы появятся фигурные скобки.

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

Обратите внимание: для удаления формулы необходимо выделить весь массив и после этого удалять.

Видео👇

Подписывайтесь на Hello Excel
#кейсы