rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Вычисление ранга

| Категория: Приемы и советы, Формулы и функции |

6

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

rank1.png

  • РАНГ(Число;Ссылка;Порядок) – определение места значения в списке значений (порядковый номер относительно других чисел в списке).
    RANK(Number,Ref,Order)
    Порядок:
    0 (или не указано) – максимальное значение соответствует 1-ой позиции, минимальное – последней.
    1 – минимальное значение соответствует 1-й позиции, а максимальное – последней.

rank2.png

  • ПРОЦЕНТРАНГ(Массив;Х;Разрядность) – процентная норма значения в множестве данных
    PERCENTRANK(Array,X,Significance)

rank3.png

Ответ представлен в числовом формате, применив процентный формат, получаем конечный результат анализа данных:

rank4.png

Расчет количества дней от даты начала текущего месяца до текущей даты и от текущей даты до даты окончания текущего месяца

| Категория: Приемы и советы, Формулы и функции |

15

Как определить сколько календарных дней прошло с начала месяца до текущей даты и сколько дней осталось до окончания месяца?

calcdate1.png
Для решения этой задачи понадобится знание 3-х функций:

  • СЕГОДНЯ() – обновляемая дата.
    TODAY()
  • КОНМЕСЯЦА(Нач_дата;Число_месяцев) – определяет дату последнего дня месяца, заданную смещением по количеству месяцев до или после начальной даты
    EOMONTH(start_date;months)
  • ДЕНЬ(Дата_в_числовом_формате) – определяет число месяца: число от 1 до 31.
    DAY(Serial_number)

Для определения последнего дня текущего месяца нужно в функции КОНМЕСЯЦА задать количество месяцев 0.

calcdate2.png
Дальнейший расчет совсем прост – разница между 2-мя известными датами, совмещая в одну формулу, получаем:

calcdate3.png

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

Определить дату 1-го дня месяца можно по формуле: =ДАТА(ГОД(СЕГОДНЯ();МЕСЯЦ(СЕГОДНЯ());1)

Преобразование текстовых данных в дату

| Категория: Приемы и советы, Формулы и функции |

5

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

ptd1.png

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

  • ЛЕВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с начала строки текста.
    LEFT(Text; Num_chars)
  • ПСТР(Текст;Начальная_позиция;Количество_знаков) – возвращает заданное число символов из строки текста, начиная с указанной позиции.
    MID(Text;Start_num;Num_chars)
  • ПРАВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с конца строки текста.
    RIGHT(Text;Num_chars)

ptd2.png

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

  • ЗНАЧЕН(Текст) – преобразует текстовый аргумент в число.
    VALUE(Text)

По числовым данным определяем Дату и Время:

  • ДАТА(Год;Месяц;День) – получение даты по исходным данным, таким как день, месяц и год.
    DATE(Year;Month;Day)
  • ВРЕМЯ(Часы;Минуты;Секунды) – получение времени по таким исходным данным, как часы, минуты и секунды.
    TIME(Hour;Minute;Second)

ptd3.png

Чтобы получить ответ в формате даты и времени, необходимо к дате прибавить время:

ptd4.png

Полученный результат следует отформатировать, создав пользовательский формат, показывающий дату и время со всеми составляющими ДД.ММ.ГГГГ чч:мм:сс:

ptd5.png

Если совместить результаты промежуточных вычислений в одну формулу, то получится одна симпатичная формула:

=ДАТА(ЗНАЧЕН(ПСТР(B3;7;4));ЗНАЧЕН(ПСТР(B3;4;2));ЗНАЧЕН(ЛЕВСИМВ(B3;2)))+
+ВРЕМЯ(ЗНАЧЕН(ПСТР(B3;12;2));ЗНАЧЕН(ПСТР(B3;15;2));ЗНАЧЕН(ПРАВСИМВ(B3;2)))