Excel

Постов: 14 Рейтинг: 44329
135

EXCEL для чайников.2.Время

Развернуть
=”ПРИВЕТ”&” “&”ВСЕМ!”

Мой предыдущий пост https://pikabu.ru/story/_5337342 содержал несколько грубых педагогических ошибок. Почитав комментарии и сделав выводы, я решил двигаться дальше, побольше, так сказать, разъясняя. Готовьтесь, пост будет еще длиннее.

Сегодня я расскажу вам о времени. Ученые и философы испокон веков спорят о его происхождении, и даже о его существовании. В Excel оно точно есть и работать с ним можно и нужно. Итак, что же такое время в Excel? Возьмем число 42997,635216. Что оно может означать? Человек, работающий с датами в Excel, сразу поймет подвох. Дело в том что это дата и время, в Excel, взятые на момент написания поста функцией =ТДАТА() в формате обычного числа. Про форматы поговорим отдельно, сначала нужно разобраться с представлением дат и времени: 42997 – это количество дней, начиная с 1 января 1900 года, (так что если вы историк то работать с датами до 1900 года придется как обычным текстом, без вычислительных выкрутасов), Стоит так же отметить, что сейчас работать с датами можно вплоть до 31 декабря 9999 г. (тут писатели фантасты печально вздохнули и полезли за калькуляторами). Дробная часть 0,635216 это время от целых суток, то есть 1 – это 24 часа. Давайте проверим за Excel, все ли правильно он посчитал: 0,635216*24=15,245184 (15 это часы); 0,245184*60=14,71104 (14 это минуты); 0,71104*60=42,6624 (43 это секунды, округляем до целого). Теперь переводим ячейку с числом 42992,57046 в формат даты и времени и получаем 19.09.17 15:14:43, хотя функция =ТДАТА() уже изрядно убежала за время нашего расчета. Поверьте, нет смысла проверять за Excel. Не нужно тратить на это драгоценное время, он все считает правильно, ошибка может быть только по другую сторону монитора. Многим это покажется смешным, но у меня есть знакомые, которые проверяют Excel на калькуляторе.

Понимание того что целая часть - это дни, а дробное - это часы, минуты, и секунды, очень важно. Функция =ТДАТА() прекрасна и опасна. Ведь она выполняет расчеты относительно текущего времени, и, если это не нужно и об этом забыть, цифры в таблице поплывут. Они будут плыть пока мы не закрепим дату. Нужно взять ячейку с формулой, войти в режим редактирования и нажать F9, либо копировать её и вставить обратно в туже ячейку как значение. Ход времени в таблице остановится, данные перестанут обновляться.

Какие же еще функции есть в Excel для работы со временем? О, их великое множество, благо в основном названия функций в Excel «говорящие».

ТДАТА( ), СЕГОДНЯ( ) – текущее дата и время в первом случае, и дата без времени во втором.

ВРЕМЗНАЧ(“Текст”), ДАТАЗНАЧ(“Текст”) – переводит время либо дату из текста в числовой формат;

ВРЕМЯ(Часы; Минуты; Секунды), ДАТА(Год; Месяц; День) – «собирает» время либо дату из значений;

ГОД(Дата), МЕСЯЦ(Дата), ДЕНЬ(Дата), ЧАС(Дата), МИНУТЫ(Дата), СЕКУНДЫ(Дата) – «вырезаем» нужное из даты;

ДЕНЬНЕД(Дата, Тип) – выдает номер дня недели (наш тип недели 2, американской 1).

КОНМЕСЯЦА(Дата; Число_Месяцев) – это дата последнего дня месяца со смещением на нужное количество месяцев

ДАТАМЕС(Дата; Число_месяцев) – передвигает эту же дату на нужное количество месяцев вперед или назад

НОМНЕДЕЛИ(Дата; Тип) – номер недели с начала года (тип как в ДЕНЬНЕД)

РАБДЕНЬ(Дата; Количество; Праздники) – дата, которая будет или была через заданное количество дней (учитывая или нет праздники)Праздники задаются диапазоном ячеек

РАБДЕНЬ.МЕЖД(Дата; Количество; Выходной; Праздники) – то же самое, но с расширенной настройкой выходных данных. Можно задать строкой где 0-это рабочий день, 1-это выходной, на пример нормальная рабочая неделя выглядит так “0000011”

ЧИСТРАБДНИ (Дата1; Дата2; Праздники) – возвращает количество рабочих дней между 2 датами (с праздниками или без них)

ЧИСТРАБДНИ.МЕЖД (Дата1; Дата2; Выходной; Праздники) – то же самое, но с произвольным выбором выходных дней. (см. РАБДЕНЬ.МЕЖД)

ДНЕЙ360(Дата1; Дата2) – “Функция ДНЕЙ360 возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если система бухгалтерского учета основана на двенадцати 30-дневных месяцах.” © - взял из справки, в бухгалтерии не силен, ничего добавить не могу, кроме того, что это как-то связано с расчетом равномерности платежей в течении года. В общем, эти бухгалтера даже в году умудрились спереть 5 дней.

ДОЛЯГОДА(Дата1; Дата2; Базис) – это доля года между двух дат. Базис равен 1, если хотите считать по фактическим датам. В противном случае данное значение варьируется от 0 до 3, выбирайте то, что нужно, согласно пояснениям, содержащимся в справке.

Теперь давайте разберемся, как это работает. В столбце А я напишу формулу, а в столбце В, С, D я напишу результат этой формулы в разных форматах, в столбце E напишу комментарии.
EXCEL для чайников.2.Время
Еще раз обращаю внимание на то, что значения в столбцах B,C,D равны друг другу. Рассмотрим небольшой пример работы с рабочими днями. На пример давайте представим, что наша Госдума хочет сделать в 2018 году пятницу или понедельник выходным днем. Но для этого нужно убрать отпуск и праздничные дни чтобы компенсировать недостаток рабочего времени. Сколько же рабочих дней у нас получится? Находим праздничные дни в 2018 году (как оказалось их 14), вбиваем эти дни в таблицу. Я не учитывал переносы праздников с субботы на понедельник, так как в этом случае количество рабочих дней не меняется.
EXCEL для чайников.2.Время
Понедельников у нас в 2018 году на 1 день больше чем пятниц, так что результат разный. Напоследок, давайте сделаем какой-нибудь пример с использованием некоторых приемов. Допустим Вы начальник кадровой службы, у вас есть дата и время прихода и ухода сотрудников на работу. Нужно посчитать общее время, проведенное на работе и посмотреть, нет ли нарушений в распорядке трудового дня. Также есть начало и конец рабочего дня, которые задаются значениями в ячейках.
EXCEL для чайников.2.Время
Теперь посмотрим, какие формулы у нас стоят в ячейках в столбцах D-I, для удобства я их представил немного в другом виде, формулы представлены для 2-й строки, для остальных строк их нужно только «протянуть»

Время на работе (ч) =(C2-B2)*24 – тут мы вычитаем дату прихода из даты ухода, из суток переводим в часы, все просто.

Время на работе в рабочие дни (ч) =(ЧИСТРАБДНИ(B2;B2)*(C2-B2))*24 тут мы учитываем, был ли день рабочий. Я обнаружил, что если применить ЧИСТРАБДНИ с указанием одного дня эта функция в случае рабочего дня выдаст 1 и в случае выходного - 0, далее все как в формуле выше.

Время на работе в выходные (ч) =(НЕ(ЧИСТРАБДНИ(B2;B2))*(C2-B2))*24 тут мы «перворачиваем» функцию ЧИСТРАБДНИ логической функцией НЕ, которая из 1 делает 0 а из 0 делает 1, далее все как выше.

Недоработка/переработка (ч) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);D2-($K$2-$K$1)*24;F2) здесь используем функцию ЕСЛИ. Эта функция имеет 3 аргумента: логическое условие, результат при выполнении этих условий и результат, если логическое условие не выполняется. В данном случае мы проверяем по функции ЧИСТРАБНИ является ли день рабочим, если является вычитаем из фактически отработанного времени норму рабочего времени, которая в свою очередь получается из разницы конца и начала рабочего дня. Затем переводим все в часы, умножая на 24. если день выходной учитываем все время, проведенное на работе как переработку. Обратите внимание что ссылки на ячейки начала и конца рабочего дня мы «закрепили» символами $. Это делается нажатием клавиши F4 при нахождении курсора на ячейке, также есть возможность напечатать данный символ вручную. Смысл «закрепления» ячейки в том, что при протягивании формулы ссылки на «закрепленные» ячейки не будут смещаться относительно перемещения формулы по столбцам и строкам. Можно также закрепить отдельно либо столбец, либо строку, в нашем случае ссылка выглядела как $K2 при закрепленном столбце и как K$2 при закрепленной строке. При протягивании в таком случае меняется только незакрепленный фрагмент адреса ячейки, что бывает весьма полезно в некоторых случаях.

Опоздание (мин) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);ЕСЛИ(B2-ОКРУГЛВНИЗ(B2;0)<$K$1;"";ОКРУГЛ(((B2-ОКРУГЛВНИЗ(B2;0))-$K$1)*24*60;0));""). Также, как и выше, мы проверяем рабочий ли у нас день. Затем (это мое любимое) вычисляем время прихода сотрудника, без учета даты. Для этого я отнимаю из даты со временем значение той же даты со временем округленное вниз до целого значения с помощью функции ОКРУГЛВНИЗ. Выражение B2-ОКРУГЛВНИЗ(B2;0) у нас будет иметь значение 8:42 в формате времени, то есть время прихода сотрудника. В принципе мы могли бы написать =ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)), это аналогичное решение, которое собирает время из значений часов, минут и секунд, но первое решение мне нравится больше. Затем сравниваем это время с временем начала рабочего дня, если оно меньше - оставляем ячейку пустой (“”), если же больше - считаем что сотрудник опоздал и высчитываем опоздание в минутах: из времени фактического прихода отнимаем время начала рабочего дня и умножаем на 24 и на 60, чтобы получить минуты, затем округляем полученный результат до целого значения. В случае же если день выходной, то логическое условие функции ЕСЛИ не выполняется и ячейка остается пустая.

Ранний уход (мин) =ЕСЛИ(ЧИСТРАБДНИ(C2;C2);ЕСЛИ(C2-ОКРУГЛВНИЗ(C2;0)>$K$2;"";ОКРУГЛ(($K$2-(C2-ОКРУГЛВНИЗ(C2;0)))*24*60;0));""). Тут все аналогично предыдущему, за исключением того что учитывается время ухода, которое должно быть больше времени окончания рабочего дня.

Вот и все что я хотел рассказать про время, пост получился длинноватым. Надеюсь, Вы меня поняли. Буду думать, о чем рассказать в следующий раз. Помните, сначала Вы работаете в Excel, потом Excel работает за Вас!
1798

Геморрой, нытьё и консерватизм

Развернуть
В середине 2000-ых пришёл молодым специалистом работать в небольшую, но уже давно существующую и известную контору.

В отделе восемь человек. Единственным новеньким был я. Все остальные работали в компании не менее 3-х лет. Всем кроме меня за 35 лет и дальше. Оклад хороший, неплохие бонусы и даже, о ужас, 13-я ЗП. Я был счастлив. В отличие от всех остальных сотрудников. Это вызывало у меня искреннее недоумение.

В итоге, пообтесавшись и пройдя стажировку, я решил задать вопрос напрямую, что же не так. Мой бывший наставник, недавно меня обучавший, не стал скрывать печальной истины: "всё дело в сраных отчетах!!!". Его поддержали все люди, подслушивавшие наш разговор (ах да, забыл уточнить, дело было в курилке). Оказывается, всех жутко бесило составление ежедневных отчетов о проделанной работе. Из-за них все оставались в офисе до 8 или дольше. После недолгих обильно разбавленных матами объяснений я понял, что речь идёт об отчетах, которые лично я составлял за 15 минут.

Оказывается, вместо того, чтобы копировать информацию и делать расчеты в excel, они вручную забивали всю информацию в ворд и блокнот, оперируя при этом калькулятором. Я вообще сильно удивился, как они всё это успевали делать к 8-9 вечера. Я бы не справился и до утра с таким объемом. Естественно я попытался до них донести свет современной истины, но... Был послан, так как салага и т.д. и т.п. Обижаться не стал, просто злорадно хихикал, видя их округлявшиеся глаза, когда в 18.00, как по расписанию, я с чистой совестью сваливал домой.

П.С. Как только я приобрёл небольшой вес и право голоса (2-3 месяца работы после стажировки) все открыли для себя чудеса excel
5945

Альтернативное

Развернуть
Microsoft больше не будет обновлять Paint
http://pikabu.ru/story/microsoft_prekratila_rabotu_nad_velik...
Альтернативное
5363

Альтернативное

Развернуть
Microsoft больше не будет обновлять Paint
http://pikabu.ru/story/microsoft_prekratila_rabotu_nad_velik...
Альтернативное
3213

Я так и делаю

Развернуть
Я так и делаю
4339

Жиллет Эксель

Развернуть
Жиллет Эксель
542

Когда на работе застой и решил немного порисовать в Excel

Развернуть
Когда на работе застой и решил немного порисовать в Excel
2594

Когда начальство не хочет покупать софт

Развернуть
Художник который рисует в Excel.
Когда начальство не хочет покупать софт
1335

Как планировать бюджет

Развернуть
Сталкивались ли вы с проблемой, когда денег уже нет, а получку ждать еще неделю. И дело тут даже не в том сколько вы зарабатываете. Я знаю много людей которые получают довольно приличную зарплату, но к концу месяца еле дотягивают. Просто надо уметь правильно планировать свои траты. Именно для этого я и сделал себе табличку Excel. Основу я взял с сайта одного банка, но почти полностью её переделал. Может кому то она пригодится. Выглядит она следующим образом:
Как планировать бюджет
Графы которые нужно заполнять выделены бледно оранжевым цветом (ну или какой это, я хз), остальное все формулы. В левом столбце забиваем свои доходы. Там у меня зарплата, доход жены, премия и доп. заработок. Это просто для примера, у вас там может быть что угодно. Справа обязательные расходы. Сюда заносим всё то, на что деньги придется точно отдать. Это различные платежи, кредиты, съем жилья и другое. Также, для тех кто откладывает денежку, есть графа капитал. Тут надо указать сколько процентов от доходов вы хотите отложить и эксель сам посчитает сумму. Также вверху надо указать на какой период будет планирование. С какое и по какое число. Лучше указывать от зарплаты и до зарплаты. Например я получаю зп не позднее 10-го, то для себя я обычно планирую с 11-го числа текущего месяца до 10-го следующего. В примере взято с 26 по 25 просто для удобства. Далее внизу идут графы:
На траты: это сколько денег останется после выплаты всех обязательных платежей (Общий доход минус платежи). Все просто.
На день: сколько из оставшихся денег можно тратить в день на планируемый период.
Осталось: сколько денег осталось на траты с учетом уже потраченных
На сегодня: сколько денег осталось на сегодняшний день.
В правой части будем записывать все расходы. Например 26.07 потратили 2000 рублей. Соответственно  остаток составит 322,58. Этот остаток плюсуется в следующий день. И 27го можно потратить 2645. Но потратили например больше, 3000 р. Минус также идет дальше и 28-го можно потратить уже не более 1967 рублей. Но все таки лучше стараться держать себя в пределах дневного лимита, иначе смысла от всего этого не будет.
Вот такая простенькая табличка, возможно кому то будет интересно. Скачать ее можно.
4194

Таких номеров я ещё не видел. Мне прислали чертёж, сделанный в Excel!

Развернуть
Таких номеров я ещё не видел. Мне прислали чертёж, сделанный в Excel!
БМ показал много постов, указав 100% совпадение, но такого не было. Странно.
4104

Зашел в рабочую базу, а тут крик души

Развернуть
Зашел в рабочую базу, а тут крик души
4441

Excel - боль...

Развернуть
Excel - боль, когда приходишь молодым и амбициозным планктоном в компанию, вызываешься сделать табличку с анализом, впистониваешь туда оверлион функций, она вся живет, прыгает, меняется, графики пляшут, красное становится зеленым, файл аж распирает от VBA, ты его оттестил, отполировал, хочешь его презентовать, А ТЕБЕ ГОВОРЯТ "РАСПЕЧАТАЙТЕ, Я ТАК ПРИВЫК".
Excel - боль...
3757

Куда пропали мои расчеты?

Развернуть
Куда пропали мои расчеты? GIF
2569

Денежный учет

Развернуть
https://yadi.sk/d/fTQXOcK1gaLqx
Денежный учет