В прошлом уроке показывали полезную функцию ЕФОРМУЛА. Она доступна начиная с Excel 2013. Если Вы работаете в более старой версии, то можно создать собственную пользовательскую функцию, которая будет делать то же самое.

Код функции совсем простой:
Public Function ЕСЛИФОРМУЛА(rng As Range)
ЕСЛИФОРМУЛА = rng.HasFormula
End Function

Не забудьте, что такая функция будет работать только в файлах с поддержкой макросов. Поэтому сохраните Ваш документ в нужном формате.

#УР4 #Макросы

В одном из уроков мы уже рассказывали о применении функции КОНМЕСЯЦА. Тогда мы рассчитывали количество дней в месяце, к которому относится указанная дата.

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

#УР2 #Примеры_формул

Всем привет!
Нас ждут длинные выходные, и если хотите провести их с пользой, то самое время изучить наши видеоуроки на YouTube. 😉

Вот список интересных тем:

Как превратить неудобную для обработки перекрестную таблицу в правильную плоскую? - https://www.youtube.com/watch?v=N2VzdYasSPQ

3 способа скрыть содержимое ячеек перед печатью - https://www.youtube.com/watch?v=PlO9XOjTsXg

Как красиво визуализировать показатель KPI в Excel? - https://www.youtube.com/watch?v=Y6duLTiEHBM

Как быстро сделать сверку двух таблиц/реестров? - https://www.youtube.com/watch?v=4V75uvnZERY

Как подсчитать нарастающий итог в Excel? - https://www.youtube.com/watch?v=phipLdXO6_E

Как создать автоматический отчет на основе вложений в письмах Outlook (Power Query + VBA) - https://www.youtube.com/watch?v=olF4ZuVub0M

Когда на листе есть несколько диаграмм, которые нужно привести к одному размеру и аккуратно расположить относительно друг друга, лучший способ - использовать команды контекстной вкладки "Формат".

Выделяем все диаграммы, зажав CTRL и задаем им единый размер - ширину и высоту. Затем по очереди выделяем нужные диаграммы и делаем выравнивание относительно друг друга. Эти же приемы, разумеется, работают и для фигур, и для изображений.

#УР3 #Диаграммы

Лишние пробелы и непечатаемые символы могут сильно осложнить жизнь. Из-за них могут некорректно работать формулы, сортировки, сводные таблицы и т.д. Для исправления некорректного текста используются функции СЖПРОБЕЛЫ (удаляет лишние пробелы) и ПЕЧСИМВ (удаляет непечатаемые знаки).

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

#УР2 #Условное_форматирование

Сегодня делимся макросом для сбора листов из нескольких книг в одну. Порядок действий такой:
- запускаете макрос,
- указываете в какую из открытых книг надо собрать данные,
- указываете надо ли собирать "суперскрытые" листы,
- выбираете файлы, из которых происходит копирование.

Желательно, чтобы все файлы, из которых происходит сбор, были закрыты перед стартом макроса.

Можете скопировать код и форму в свой файл, а можете просто запускать наш и жать кнопку, когда нужно. Выбирайте, что вам удобнее.

#Готовые_инструменты

Файл с макросом

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

С её помощью можно заблокировать любые изменения, связанные с листами файла: их изменение, удаление, скрытие/открытие, переименование и даже просто перемещение ярлычков относительно друг друга. Можно защищать как с использованием пароля, так и без него.

#УР2 #Особенности_совместной_работы

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

Особенно эффективно это при работе с двумя мониторами, но даже если монитор один - пара небольших таблиц легко на нем помещается.

#УР1 #Работа_с_листами_книги

Иногда пользователи открывают какой-то файл и не обнаруживают в нем ярлыков листов (вкладок). В такой ситуации точно не стоит паниковать. Наиболее часто причины пропажи кроются в следующем:

1) Ширина полосы прокрутки максимально увеличена. В итоге ярлыки не помещаются на экране. Исправляем, потянув за 3 точки в левом нижнем углу

2) Для файла отключено отображение ярлычков листов в Параметрах. Идем в Файл-Параметры-Дополнительно и включаем обратно.

#УР1 #Работа_с_листами_книги

Когда мы включаем подписи данных на диаграмме, то видим значения всех точек. Если нужны только максимум и минимум, то можно вручную удалить ненужные точки. Однако, если таких точек много или диаграмма будет перестраиваться, такой способ вряд ли подойдет.

Но можно легко создать дополнительный ряд, в котором будет всего две точки - максимальная и минимальная. И уже для этого доп. ряда включить подписи данных, а сам ряд сделать невидимым (убрать все границы и заливки). Такое решение универсально и будет само перестраиваться после пересчета данных.

#УР3 #Диаграммы

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

Чтобы создать ряд десятичных чисел с нужной точностью, можно использовать формулу, которая на ходу будет округлять значение. Или использовать инструмент Прогрессия для создания арифметической прогрессии. Тоже очень удобно.

#УР1 #Простые_Вычисления

Одна из частых ошибок в расчетных файлах - случайная замена формулы на значение. Если это сделано не умышленно, то расчёты, вероятнее всего, тоже будут содержать ошибку. Причем обнаружить ее не так просто, ведь мы видим в ячейках только результат работы формулы.

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

#УР1 #Простые_Вычисления

Вводить время в ячейки Excel очень просто. Достаточно указать часы, минуты и секунды через двоеточие. Но если значение времени нужно использовать в какой-то формуле, то такой способ ввода не сработает. Формула просто не поймёт, что мы пытаемся работать со временем.

Вы, наверное, помните, что время на самом деле - это десятичное число. То есть вместо любого значения времени можно ввести соответстующее числовое значение. Но это крайне неудобно.

Выход такой: вводите время, как текстовую строку (то есть в кавычках). При этом, если с таким значением времени производится какое-то арифметическое действие, Excel сам поймет, что значение в кавычках это время. А если Вам нужно такое значение с чем-то сравнить с помощью логического оператора, то преобразовать время из текста в число можно любым удобным способом: двойным отрицанием, сложением с нулем, умножением на 1 и т.д.

И помните - любую константу из формулы можно вынести в ячейку и ссылаться на неё.

#УР2 #Примеры_формул

В продолжение разговора о времени в формулах. Для сбора значения времени из отдельных частей часто используется функция ВРЕМЯ. А для превращения текстовой строки в нормальное время - функция ВРЕМЗНАЧ. Это удобно, но с ними нужно быть аккуратными.

Помните, что эти функции возвращают значение времени в пределах от 0:00:00 до 23:59:59. Поэтому если у Вас есть задача собирать нормальное время из отдельных частей, и есть вероятность, что итоговый результат будет больше суток, не стоит брать в работу эти функции. Можно обойтись, например, склеиванием значений в текстовую строку и дальнейшим двойным отрицанием.

#УР1 #Простые_Вычисления

И еще немного о времени в Excel. Время в программе - это дробная часть суток. 24 часа - это одни сутки, 12 часов - 0,5 суток, 1 час - 1/24 суток и т.д. Ну и разумеется, всем известно, что в сутках 24 часа, в часу 60 минут, а в минуте - 60 секунд. Простое вычисление даёт нам понять, что 1 сутки = 24 часа = 1440 (60x24) минут = 86400 (24x60x60) секунд.

Используя эти знания, мы можем написать небольшую формулу сбора значения времени из отдельных частей. Показываем пару вариантов, которыми пользуются и делятся с нами подписчики.

#УР1 #Простые_Вычисления

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

Сначала выделяем саму ячейку с формулой. Затем вызываем это окно (можно командой на ленте, а можно нажать F5 или CTRL+G, вызвав окно "Переход", а уже там выбрать "Выделить").

Ну а в самом окне указываем, что нужно выделить "влияющие ячейки". Если нужны только те ссылки, которые прямо указаны в итоговой формуле, то выбираем "только непосредственно". Ну а после выделения можно выбрать любую нужную заливку.

#УР1 #Простые_Вычисления

Всем Привет!!!😊
Праздники прошли, и мы возвращаемся с нашими традиционными стримами! 🚀

Завтра (30 мая) - разбираем самые интересные вопросы, попавшие в наши боты.
Наше постоянное место встречи:
https://www.youtube.com/watch?v=jGyYVrKY0mA
Так что советуем подписаться, чтобы не пропустить начало😉

Время - в 20:00 по МСК
Ждём вас и ваших друзей, делитесь постом, не стесняйтесь👍
Ваши, tDots.ru

А у нас сейчас будет вопрос про визуализацию в Excel.
Не пропустите!

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

На самом деле, команды для фильтрации полей значений сводной спрятаны в указанных выше кнопках. Выбираете Фильтры по значению, выбираете нужное поле и задаете условия фильтрации. Быстро и просто.

Ну а если очень хочется именно кнопки в столбцах значений, то испоьзуйте приём из этого урока https://tttttt.me/excel_everyday/957

#УР2 #Сводные_таблицы