Эксель когда знаешь все просто
Навыки работы с функциями управления датами и временем должны быть в арсенале специалистов, которые анализируют данные или хотя бы работают с excel. Ниже я покажу как работают основные функции, которые могут быть вам полезны.
1. Как получить день, месяц и год из даты и собрать дату из составляющих. Это просто, когда знаешь какие функции использовать. Ниже на примере я покажу несколько из них:
- Функция ДЕНЬ возвращает номер дня;
- Функция МЕСЯЦ возвращает номер месяца;
- Функция ГОД возвращает год;
- Функция ДЕНЬНЕД возвращает порядковый номер дня недели, при этом 2 во втором аргументе означает, что неделя начинается с понедельника, а заканчивается воскресеньем;
- Функция ДАТА "собирает" из составляющих необходимую дату, т.е. делает обратное действие от первых трех функций.
2. Как получить часы, минуты и секунды из времени и собрать время из составляющих . В Excel существуют инструменты для извлечения времени, аналогичные функциям извлечения дня, месяца и года.
Предположим, что из времени 12 часов 33 минуты 13 секунд нужно вытащить по отдельности количество часов, минут и секунд. Для этого существуют отдельные функции:
- Функция ЧАС возвращает количество часов;
- Функция МИНУТЫ возвращает количество минут;
- Функция СЕКУНДЫ возвращает количество секунд;
- Функция ВРЕМЯ "собирает" из составляющих необходимое время, т.е. делает обратное действие от первых трех функций.
3. Расчет прошедшей и оставшейся частей года в % . Иногда в некоторых отчетах необходимо отразить какая часть года уже прошла и какая осталась до окончания года. В этом может помочь функция ДОЛЯГОДА . Она имеет 2 аргумента: начальная дата и конечная дата (иными словами отчетная дата, на основании которой мы и считаем долю):
4. Поиск даты последнего дня месяца . Если вам необходимо узнать какая дата является последней в определенном месяце - можно воспользоваться функцией КОНМЕСЯЦА . У этой функции два аргумента: первый - начальная дата, второй - количество месяцев вперед или количество месяцев назад (отрицательное значение) от начальной даты.
Чтобы узнать какая дата будет последней в текущем месяце, к этой формуле нужно добавить еще одну функцию - СЕГОДНЯ :
✔ Ищите ответы на свои вопросы в статье Фишки excel - подборка статей . Там я перечислила все свои статьи по разбору работы функций excel.
✔ Вот здесь есть подборка видео о том, как строить необычные диаграммы - Фишки Excel - подборка видеороликов .
Написал очередную статью и видеоурок про то, как подсвечивать в больших таблицах строки с прошлыми, будущими и текущими датами разными цветами . Пригодится всем, кто ведет реестры договоров, отгрузок, продаж и хочет наглядно цветом подкрашивать уже сделанные прошлые, будущие и сегодняшние элементы.
Круто!
Круто! Воистину "когда знаешь, всё просто".
Спасибо, Александр!
Я восхищаюсь, но сам так и не перейду с 2003 офиса на что-то посвежее. Причина - наличие пользовательской "плавающей" панели с 50-ю однотипными кнопками (для повторяющихся однотипных операций), на которых висят мои макросы. В новом интерфейсе такого не реализовать. Сорри за оффтоп, Николай.
Александр, почему не реализовать? Можно вынести кнопки на панель быстрого доступа или сделать свою вкладку с помощью Ribbon XML Editor или чего-то подобного.
Спасибо!
Как всегда- все наглядно, подробно, доходчиво.
Где еще можно использовать элемент управления "флажок", очень понравился этот "выключатель").
Да где угодно, универсальная штука. В тестах, например, или в калькуляторах для выбора опций (примерно так, как это бывает на сайтах автосалонов при выборе опций машины).
Супер функция!
Получается можно создавать многоступенчатые условия для форматирования с несколькими переключателями для одной таблицы. Как я понимаю будет только один недостаток:связь между переключателями будет по принципу последовательной связи т.е. при отключении самого верхнего переключателя нижние работать не будут. Но, уверен, если правильно настроить условия, то это будет вполне себе удобный инструмент.
Может имеет смысл ввести улучшенную функцию в PLEX?
Может имеет смысл ввести улучшенную функцию в PLEX? |
Ну если со стороны пользователя, то хотелось бы, чтобы при нажиматии на кнопку Plex с именем типа 'создать переключатель для подсветки данных' выскакивало диалоговое окно в котором бы были условия по которым подсвечивать данные и массив данных/диапазон данных в которых этот переключатель работал. Конечно было бы хорошо, чтобы таких переключателей можно было создать неограниченное/достаточное количество. Переключатели более наглядны и удобны (ИМХО)ю
Либо по нажатию кнопки PLEX вводить над ячейкой с автофильтром функцию противоположную имеющейся в PLEX 'Условия автофильтра' т.е. при введении/выборе из выпадающего списка будут подсвечиваться данные в таблице. Как вы понимаете несколько таких переключаетелей могут значительно облегчить жизнь вместо того, чтобы "лазить" по фильтрам или морочиться с условным форматированием. тем более, что подсветка может будет динамической в отличие от условного форматирования.
Как реализовать такое программно я не знаю ибо пользователь. Но полагаю, что это реализуемо. Особенно второй вариант. Мне кажется выпадающий список значений в ячейке переключателе должен формироваться автоматоматически, но чтобы было окно как у автофильтра куда можно ввести значение, которое нужно подсветить (но это уже по возможности),
Извините, если корявенько объясняю. Если нужно дополнительно распишу все и дополню.
Библиотека формул
На практике часто приходится вводить одни и те же формулы и функции в типовых отчетах и вычислениях в Excel. Библиотека Формул позволяет один раз ввести шаблон формулы и затем многократно использовать его в будущем для повторного ввода в расчетах. По-умолчанию содержит более 100 готовых сложных формул и пользовательских функций PLEX из разных категорий:
Кнопка Вставить позволяет вставить выбранную формулу в активную ячейку, указав аргументы с помощью диалогового окна:
Кнопка Изменить в верхней части окна позволяет отредактировать любую пользовательскую формулу, ее аргументы и описания с помощью диалогового окна:
Кнопка Создать позволяет добавить к списку свою формулу (в раздел ПОЛЬЗОВАТЕЛЬСКИЕ) и сохранить ее для будущего использования.
Поддерживается до 7 аргументов для каждой функции с возможностью задать любой тип закрепления ссылки для каждого из них. Если нужно ввести созданную формулу как формулу массива (с Ctrl+Shift+Enter), то включите флажок в правом верхнем углу.
Может ли это повредить мои данные?
Николай, добрый день.
После обновления PLEX пропадают формулы, добавленные в библиотеку самостоятельно.
Как-то можно их сохранить при переходе на новую версию?
Добрый день!
Николай, коллеги, подскажите, пож-та, как с помощью библиотеки формул добавить аргументы функции так, ячейки, на которые ссылаются эти аргументы, не фиксировались знаком '$" ?
Здравствуйте, Николай! Подскажите пожалуйста, как вы получаете именно относительные ссылки на ячейки? AddressLocal даёт абсолютные, а я хочу что-то подобное сделать. PLEX есть, кстати))
Заменяю стандартной функцией Replace знак доллара на пустую текстовую строку
ахахахахаха))) "Когда знаешь — всё просто" )) спасибо большое!
А, если рассмотреть применение такого подхода к умным таблицам, можно ли визуально, в строке формул, добиться такого же результата, как если бы просто вводил длинную формулу руками (то есть [@[Столбец]] - для ячейки поля и [Столбец] - для всего диапазона поля?
Задавал вот тут в теме.
Добрый день, Николай!
Очень активно пользовались на работе библиотекой формул до версии 17.3.
В "Библиотеке" были внесены сложные формулы, зависящие от 4-5 аргументов. При чем эти аргументы в самой формуле использовались достаточно много раз. Некоторые аргументы были относительными, некоторые абсолютными, некоторые с закреплением столбца или строки. Внеся в первую ячейку такую формулу, можно было ее протянуть вправо и вниз и заполнить одним махом кучу данных. Всё были довольны.
После обновления версии до 17.3. случилось такое:
При указании ссылок для аргументов, даже если они внесены в окно вставки как абсолютные ссылки, после нажатия "Вставить" в ячейке оказываются относительные ссылки, которые нельзя корректно протягивать. Изменять потом характер ссылки в самой ячейке, куда внесена формула, очень затруднительно.
Почему бы не указывать характер ссылки для того или иного аргумента именно в окне ввода формулы? Ведь внося формулу из библиотеки мне нужно будет указать абсолютная она или относительная максимум 7 раз (по максимально возможному числу аргументов), а не 7*кол-во повторений этих аргументов в формуле.
Добрый день, Николай!
Не могли бы Вы научить формулу подсчета суммы и количества ячеек с заданным цветом принимать к учету и те ячейки,которые окрасились при применении условного форматирования?
Формула не воспринимает эти цвета.
Николай, здравствуйте!
Исправьте пожалуйста библиотеку формул. Неделю строчил, заполнял библиотеку, а она просто снесла все формулы. Благо восстановление системы помогла.
По всей видимости не дружит с Office 2019. Формулы массивов библиотека вообще не понимает, при изменении формулы и при попытке открыть и изменить другую формулу, открывается предыдущая формула и помогает от этого только полное закрытие Excel.
Вроде мне встречалась в Вашей библиотеке формула получения отступа текста в ячейке.. не могу найти.. Или нет такой?
Учиться надо у лучших. Вашему вниманию подборка пяти, книг для изучения Excel:
Джон Уокенбах. “Microsoft Excel 2016. Библия пользователя”
Книга от признанного мирового эксперта в области MS Excel. С её помощью вы изучите основы — ячейки, формулы, функции, диаграммы. Овладеете полезными средствами, как условное форматирование, спарклайны, автозаполнение, пакет анализа и надстройка Power Query. Откроите мощь сводных таблиц и модели данных Power Pivot. Создадите свой первый VBA-макрос. Для отработки навыков предусмотрены файлы с примерами, которые можно загрузить с веб-сайта книги.
Николай Павлов. “Microsoft Excel. Готовые решения – бери и пользуйся!”
Николай Павлов — один из самых авторитетных сертифицированных тренеров по MS Excel, девиз которого: когда знаешь — все просто ! Книга, набор готовых решений на самые разные рабочие случаи, охватывает весь спектр повседневных задач офисного пользователя. Минимум "воды" и теории, максимум практической пользы. Для отработки навыков, в комплекте идут файлы с примерами.
Билл Джелен и Майкл Александер. “Сводные таблицы в Microsoft Excel 2013”
Если вы раньше не занимались сводными таблицами, после прочтения книги сможете в полном объеме воспользоваться их невероятной гибкостью и аналитической мощью. Авторы делятся бесценным практическим опытом решения реальных задач, помогают избежать типичных ошибок, предлагают уникальные советы и методики, призванные помочь при выполнении бизнес-анализа.
Джон Уокенбах. “Формулы в Microsoft Excel 2013”
Освойте профессиональные методики работы с формулами, описания которых вы нигде больше не найдете. Книга подойдёт, как новичкам, так и опытным пользователям.
Николай Павлов. "Microsoft Excel: Мастер Формул"
Вторая книга Николая Павлова, в основу которой лег одноименный тренинг, главная цель которого — прокачать навык в написании сложных формул. Содержит подробный разбор формул массивов, составления сложных формул и их отладки, разбор полезных, но малоизвестных формул, большое количество трюков и фишек с формулами. Книга рассчитана на средних и продвинутых пользователей MS Excel.
Знакомимся с самым простым, но эффективным инструментом для автоматизации рутинных задач. Что это такое? Для чего? Как работает.
Учимся понимать, что записано в макросе. Как записываются команды в макросе? По каким правилам? Какова структура кода?
Чтобы понимать, что же конкретно записано в той или иной строке макроса, необходимо получить представление об объектах Excel, их методах и свойствах
Пошаговый алгоритм "чистки" программного кода: убираем "мусор", учимся делать код кратким, лаконичным, понятным
Пишем нашу первую программу "с нуля", без использования записи макросов. Заполнения бланка документа - одна из стандартных задач автоматизации
Учимся использовать переменные - это существенно облегчает как понимание написанной программы, также и ее последующую модификацию
Второе практическое занятие. Учимся работать с элементами управления на рабочем листе и с объектной моделью диаграммы
Знакомимся с функциями VBA: что это такое, для каких целей используются, каков синтаксис. Рассматриваем некоторые разновидности функций
Рассматривается программное выполнение метода "Вставить значения" и команда безусловного перехода GoTo
Рассматривается процесс написания простейшей программы для проведения тестирования/анкетирования (учеников, сотрудников, клиентов и т.д.)
Как просуммировать значения только в тех ячейках, которые окрашены в определенный цвет? Или в тех, что выделены жирным шрифтом? Используем свойства оформления в формулах
Рассматриваем приемы и инструменты, крайне необходимые на этапе тестирования и отладки программного кода
Добрый день, Дмитрий. Я случайно увидела ваши видео в ютубе. Посмотрела и мне стало интересно изучить макросы. Перешла по ссылке и просмотрела часть бесплатных видео и мне очень понравилась ваша манера преподавать просто без лишних слов. Скажите, пожалуйста, а как сделать выпадающий.
Здравствуйте В видео "Автоматизация заполнения бланка документа (платежного поручения)" говориться, что можно скачать файлы с макросами, о которых идет речь. Не могу найти. Подскажите, пожалуйста,где скачать Файл?
Благодарю Вас хотя у меня оффис 97 все полчается
здравствуйте, Дмитрий. подскажите пожалуйста такой момент, для того, чтобы сделать макрос с фамилиями, я сделала перечень с фамилиями и суммами.. так вот у меня слово фамилия при сортировке улетает в самый низ))) то есть оно тоже сортируется. что не так в моей таблице?
Спасибо большое.Очень полезная информация
Замечательные уроки, большое спасибо автору. Все очень доходчиво, без лишних заморочек.
Доступно и понятно!Круто.
Можно, но этот код будет посложнее, т.к. придется из Excel взаимодействовать с другим приложением - Outlook. А для этого нужно уметь обращаться не только с объектами Excel, но и с объектами Outlook (которые, конечно, сильно отличаются).
Подобные вопросы я рассматривал на специальном тренинге "Взаимодействие Excel с другими приложениями", подробнее о нем и его содержании можно узнать по этой ссылке.
Дмитрий, доброе утро! А можно добавить еще макрос, чтоб после завершения теста еще и отправлял автоматически через аутлук адресату ответ с тестированием?
Спасибо, Все, разобралась, внимательно смотреть нужно
© Дмитрий Быстров. 2011-2022. Все права защищены.
Копирование материалов без указания источника запрещено
Читайте также: