Подсчет прогресса в Excel

Обновлено: 21.11.2024

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

Сотрудник TechRepublic, которого мы назовем Зи, обратился с просьбой о помощи в расчете дат. Зи, работающий в небольшом исправительном учреждении, имел электронную таблицу Excel со следующей информацией:

Дата записи
Дата запланированного отъезда

Используя эти данные, Зи хотел рассчитать два новых столбца: "длительность пребывания в годах, месяцах и днях" и "процент от общего пребывания".

К счастью, в Excel есть встроенная функция DATEDIF, которая упрощает вычисление прошедшего календарного времени. Я отправил Зи образец решения, и она ответила, что это именно то, что она имела в виду.

В этом кратком руководстве по математике дат я объясню, как я сгенерировал выходные данные, запрошенные Zee. Снимки экрана были сделаны с помощью Excel 2000. Однако процесс идентичен в Excel 97, 2000 и 2002.

Простая часть: возраст в днях
Zee нуждается в том же, что и бизнес-пользователи электронных таблиц, которым необходимо рассчитать возраст неоплаченных счетов в днях. Дата входа Zee аналогична дате выставления счета. Предположим, что дата ввода или дата счета-фактуры хранится в ячейке A2, и вы хотите отобразить в ячейке B2 количество дней, прошедших между этой датой и текущей датой. Для этого введите в ячейку B2 следующий код:
=NOW()-A2

Затем Excel будет вычислять возраст в днях, вычитая значение даты, введенное в ячейке A2, из NOW(), что возвращает текущую системную дату. Отсортируйте свои неоплаченные счета в порядке убывания по этому столбцу возраста в днях, и вы получите отчет о просроченной дебиторской задолженности.

Потребности Zee: "Продолжительность пребывания на данный момент"
Проблема Zee заключается в том, что недостаточно подсчитать количество прошедших дней. Zee хочет выразить этот интервал в формате X лет, Y месяцев, Z дней.

Как же преобразовать целое число, например 800 дней, в соответствующее количество лет, месяцев и дней? Чтобы получить такой результат в первые дни работы с электронными таблицами, вам нужно было проанализировать компоненты месяца, дня и года начальной и конечной дат, а затем пройти через несколько обручей if-теста, чтобы отобразить правильные результаты.

Теперь есть функция разницы дат DATEDIF, которая принимает следующую форму:
=DATEDIF(begin_date,end_date,calendar_unit_option)

Параметры begin_date и end_date должны быть выражениями, возвращающими даты или ссылки на ячейки, содержащие даты. Количество опций для параметра calendar_unit_option заставляет некоторых пользователей Excel избегать использования этой функции. Давайте посмотрим на функции решения Zee, показанные на рис. A.

< /tr>

Рисунок A

Мы использовали функцию DATEDIF для получения результатов этой математики даты.

В листинге A показано, как выглядит формула на рисунке C2 на рисунке A.

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

Zee хочет отображать все три ярлыка, даже если количество лет, месяцев или дней равно нулю. Итак, чтобы убедиться, что результат грамматически правильный, нам пришлось использовать функцию ЕСЛИ для проверки каждого значения. Если значение равно 1, проверка ЕСЛИ возвращает одно из условий в единственном числе год, месяц или день. Во всех остальных случаях, когда функция РАЗНДАТ возвращает 0 или любое число больше 1, проверка ЕСЛИ возвращает множественное число.

Раскопки DATEDIF
Вот что нужно сделать, если вы хотите рассчитать прошедшее время в годах, месяцах и днях, но вам не нужны текстовые метки, подобные Zee. Предполагая, что даты начала и окончания находятся в A2 и B2, просто введите три вызова основных функций в отдельные столбцы. Вот как работают выражения:

Годы. В выражении DATEDIF(A2,B2,Y") параметр "Y" сообщает функции, что она должна вернуть количество полных лет между двумя датами.
Месяцы. Выражение РАЗНДАТ(A2,B2,"ГМ") возвращает количество месяцев между двумя датами без учета лет.
Дней. Наконец, выражение РАЗНДАТ(A2,B2,"MD") возвращает количество дней в интервале без учета месяца и года.

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

Таблица A

OPTION	ВОЗВРАТ
“Y”	Полные годы между даты
“M”	Полные месяцы между датами
“D”	Дни между датами
“YM”	Месяцы между датами, без учета года
«MD»	Дни между датами, без учета месяца и года
«YD»	Дни между датами, без учета года

Продолжительность пребывания Зи на данный момент
Еще одна вещь, которую Зи хотела определить, — это процентная доля пребывания каждого человека в отеле. Другими словами, она хотела узнать результат этого расчета:
(Количество оставшихся дней), деленное на (Общее количество дней запланированного пребывания)

На рис. Б показаны результаты примерных расчетов. Поскольку это руководство в основном посвящено функции РАЗНДАТ, мы использовали выражение
=РАЗНДАТ(A2,C2,"D")/РАЗНДДАТ(A2,B2,"D")

В этой формуле РАЗНДАТ(A2,C2)"D") возвращает количество дней между датой входа и заданной датой. Выражение РАЗНДАТ(A2,B2,D) возвращает количество дней между датой въезда и ожидаемой датой отъезда.

Конечно, в этом случае вы могли бы получить результаты Zee и без функции РАЗНДАТ. Поскольку вычитание двух дат также дает количество дней между ними, формула =(C2-A2)/(B2-A2) даст тот же процент.

Рисунок B

Функция DATEDIF упрощает вычисление процента прошедшего времени.

Отличное владение Excel

Чтобы прокомментировать этот совет или поделиться своим опытом использования решений Excel для бизнеса, оставьте комментарий или напишите нам.

Зарегистрированный пользователь Дата регистрации 21.01.2015 Местоположение ky MS-Off Ver 2013 Сообщений 4

Подсчитать прогресс между двумя датами

Существует ли формула или функция, которая принимает начальную дату в прошлом, конечную дату в будущем и дает вам прогресс между ними на сегодняшнюю дату?

Пример:
Дата начала 01.08.2015
Дата окончания 10.08.2015
Сегодняшняя дата 05.08.2015
Выполнено 50% < /p>

Уважаемый участник форума Дата регистрации 07.02.2013 Расположение Филиппины MS-Off Ver Excel 2007, 2010 Сообщений 533

A1 — ваша дата начала

Ваша дата окончания – B1

Модератор форума Регистрация 27.01.2010 Местоположение Tysons Corner, VA, USA MS-Off Ver 365 Сообщений 20 111

Несколько замечаний:

<р>1. По какой-то причине DATEDIF не задокументирован в Excel, но Microsoft описывает его на своих страницах поддержки. Вы не увидите его в Excel в списке доступных функций, но он есть.

<р>2. Использование DATEDIF с «d» в основном похоже на простое вычитание, поскольку даты хранятся как количество дней. Следующая формула даст те же результаты, что и приведенная выше:

Формула:

<р>3. Если вы делаете что-то по работе и просто хотите включить рабочие дни, а не все дни, используйте

Формула:

Джефф
| | |�| |�| |�| |�| | |:| | |�| |�|
Прочитайте правила
Используйте теги кода, чтобы [code]заключить свой код![/code]

Зарегистрированный пользователь Дата регистрации 21.01.2015 Местоположение ky MS-Off Ver 2013 Сообщений 4

Спасибо за публикацию формул для расчета процента прогресса между двумя датами. Формула =(DATEDIF(A1,TODAY(),"d")+1)/(DATEDIF(A1,B1,"d")+1) отлично работает в моей диаграмме Ганта MSExcel.

Добро пожаловать на форум.

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

Если вы не знаете, как начать новую тему, см. FAQ: Как начать новую тему

Али

Увлеченный самоучка, пользователь MS Excel, который постоянно учится!

Правила форума (обновлены в сентябре 2018 г.): ознакомьтесь с ними здесь.
Как использовать полученный код Power Query: помощь здесь. Подробнее о Power Suite здесь.
Не забудьте сказать "спасибо" тем, кто помог вам в вашей теме. Если хотите, вы также можете вознаградить их, нажав на звезду их репутации внизу слева.

Есть ли способ получить доступ к проценту вычисляемых ячеек, отображаемому в строке состояния, когда Excel пересчитывает?Я хотел бы отображать индикатор выполнения этого, когда Excel выполняет длительные пересчеты, чтобы нетерпеливые пользователи знали, что что-то происходит.

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

Сообщить о нарушении

Домогательство – это любое поведение, направленное на то, чтобы побеспокоить или расстроить человека или группу людей. К угрозам относятся любые угрозы самоубийства, насилия или причинения вреда другому человеку. Любой контент на тему для взрослых или неуместный для веб-сайта сообщества. Любое изображение, ссылка или обсуждение наготы. Любое поведение, которое является оскорбительным, грубым, вульгарным, оскверняет или демонстрирует неуважение. Любое поведение, которое может нарушать лицензионные соглашения с конечными пользователями, включая предоставление ключей продукта или ссылок на пиратское программное обеспечение. Незапрашиваемая массовая рассылка или массовая реклама. Любые ссылки на вирусы, шпионское ПО, вредоносное ПО или фишинговые сайты или их пропаганда. Любой другой неприемлемый контент или поведение, как это определено Условиями использования или Кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другим жестоким обращением с детьми или их эксплуатацией.

Ответы (4) 

Посмотрите здесь

Если это сообщение отвечает на ваш вопрос, отметьте его как ответ.
Майк Х

Если этот ответ отвечает на ваш вопрос, отметьте его как ответ.

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

На самом деле я не запускаю какой-либо код в то время, когда Excel выполняет свои вычисления, поэтому у меня нет цикла для увеличения индикатора выполнения. Я импортирую файлы данных на несколько рабочих листов, содержащих формулы, и Excel выполняет полный перерасчет, когда я устанавливаю application.calculation=xlCalculationAutomatic в конце подпрограмм VBA. Я пытался пересчитать каждый лист, когда его данные были импортированы, и перед импортом данных для следующего листа, но Excel по-прежнему выполняет полный пересчет в конце, который длится несколько минут, иногда отображая сообщение «Не отвечает» в строке заголовка, что заставляет пользователя думать, что он упал.

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

Что я хотел бы сделать, так это сопоставить ход вычислений, отображаемый в строке состояния, с более заметным дисплеем – может быть, немодальной формой?

Сообщить о нарушении

Рисунок 1. Процент завершения проекта в Excel.

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

Общая формула.

=COUNTA(диапазон1)/COUNTA(диапазон2)

Как использовать функцию Excel COUNTA.

Чтобы определить процент завершения для проекта с набором задач, мы собираемся использовать простой синтаксис формулы, основанный на функции Excel COUNTA.

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

Рисунок 2. Список выполненных задач.

В приведенном выше примере общее количество идентифицированных задач расположено в первом столбце A. Те задачи, которые отмечены как завершенные во втором столбце B, — это уже выполненные задачи.

Затем мы перейдем к пометке третьего столбца C как «Показатель успеха». Здесь будет находиться рассчитанный процент завершения проекта после расчета.

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

=СЧЕТЧАСТЬ(B2:B8)/СЧЕТЧИК(A2:A8)

Рисунок 3. Функция процента завершения в Excel.

Выполненные задачи/Всего задач

Мы подсчитываем выполненные задачи, подсчитывая непустые ячейки, расположенные в диапазоне ячеек B2:B8 нашего примера листа. Мы подсчитываем общее количество задач, подсчитывая непустые ячейки, расположенные в диапазоне ячеек B2: B8 нашего примера рабочего листа. Функция Excel COUNTA по умолчанию подсчитывает ячейки на нашем листе, содержащие текст или числа.

Рисунок 4. Окончательный результат.

Мгновенное подключение к эксперту через нашу службу Excelchat

Наш онлайн-сервис Excelchat всегда к вашим услугам. Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любые ваши вопросы по Excel. Гарантированное подключение в течение 30 секунд и индивидуальное решение для вас в течение 20 минут.

Читайте также: