Введите время без двоеточий (ччммсс) и рассчитайте разницу во времени в Excel
Я хочу ввести время без двоеточий как hhmmss и рассчитать разницу во времени с другой ячейкой. Например, я ввожу время начала в одной ячейке и время окончания в другой. Тогда мне нужно рассчитать разницу во времени. Но это должно включать часы, минуты и секунды.
Сложность в том, что я хочу ввести время без двоеточия, но увидеть его с двоеточием в клетке. Для этого я отформатировал ячейки в произвольном формате чисел 00\:00\:00 .
3 ответа 3
Одним из вариантов будет использование комбинации форматирования ячеек с функциями ВРЕМЕНИ Excel.
Отформатируйте ячейки ввода времени (в моем примере A2 и B2) как Text . Ожидаемый формат всегда будет hhmmss , поэтому введите начальный ноль для времени с часами из одной цифры. Тогда вы можете рассчитать по этой формуле:
При этом самые левые 2 символа отображаются как «Часы», средние 2 символа — как «Минуты», а самые правые 2 символа — как «Секунды» и преобразуются в то, что Excel распознает как время. Затем он вычитает одно из другого и отображает результат в hhmmss :
РЕДАКТИРОВАТЬ: видя, что требование не совсем так, как указано в вопросе, я изменил формулу, чтобы учесть ведущие нули, добавив его:
Ужасно менее читабельно, но теперь дополняет значение нулями и использует самые правые 6, поэтому должно работать независимо от того, сколько нулей вы используете.
Я полагаю, что в этом случае вы действительно захотите отформатировать результат как hh:mm:ss .
Вот еще один способ обработки вашего времени так, как вы хотите его ввести:
Я оставил время начала и окончания как неформатированные числа, чтобы сделать действие более заметным. В этом примере ваше начальное время составляет 25 секунд после полуночи или полудня, которое вы вводите как 000025. Как число, оно будет сохранено как 25, даже если ваше форматирование будет выглядеть как 00:00:25.
Этот подход отслаивает часы и минуты, основываясь на их положении в количестве, равном 100. Секунды всегда правильные две цифры, независимо от. Формула в С2:
Функция MOD является противоположностью функции INT. Это дает вам остаток после деления.
Я знаю, что это старый пост, но если (как я это сделал) люди ищут решения через бесчисленные форумы, чтобы сэкономить время, вводя в табели, и не нужно вручную вводить «двоеточие», но иметь двоеточие видимым, а затем быть способен выполнять вычисления . все без использования VBA Script, скрытых ячеек и формул . и т.д . и т.д . Это решение, которое я придумал, — и похоже, что оно работает с ведущими нулями, 24-часовым временем и расписаниями, охватывающими полночь (хотя потребуется корректировка, если вы ожидаете, что расписание будет охватывать несколько полуночей — я не видел в этом необходимости) мое дело).
Кроме того, мне не нужны «секунды», но вы сможете достаточно легко их расширить, если будете следовать концепции:
Пользовательский Формат ваших начальных и конечных ячеек:
Это превращает их в «текстовые ячейки», но выводит цифры, чтобы всегда показывать четыре цифры (даже с ведущей нулей или нулями для американских друзей).
Обратите внимание, что это позволит вводить ЛЮБОЕ четырехзначное число в формате времени. Так что теоретически можно войти в 0768 году, и это стало временем 07:68, которого, конечно, не существует. Так что либо
а) создайте список проверки данных следующим образом:- на отдельном рабочем листе (вкладке) в одном столбце создайте список всех допустимых значений. В моем случае это были 0500, 0515, 0530, 0545 . 0445, поскольку я имел дело только с 15-минутными приращениями. Если вам нужно детализировать до секунд (серьезно?) это будет довольно долго . т.е. 050001, 050002 . и т.д. Или вы можете написать другую формулу просто для создания этого списка, а затем скопировать текстовые данные (только для вставки значений) в новый столбец, чтобы удалить формулы (я бы). - Выберите все эти ячейки с «допустимыми значениями» и на вкладке «Forumulas» панели инструментов выберите «Define Name». Дайте ему имя типа «MonkeyTimeList». - Вернитесь к своему рабочему листу, выберите все ячейки, охватывающие время начала и окончания, и перейдите на вкладку «Данные» панели инструментов, выберите «Проверка данных> Проверка данных> Настройки», в разделе «Разрешить» выберите « List «из выпадающего списка, в разделе» Source «введите имя, которое вы определили, перед которым стоит» = «, поэтому в моем случае» = MonkeyTimeList «. Так как вы нажимаете «ОК», выбираете вкладку «Предупреждение об ошибке», ставите флажок «Показать предупреждение об ошибке . » и в раскрывающемся списке «Стиль» выбираете «Стоп». Не стесняйтесь добавлять сообщение об ошибке типа «Человек-обезьяна говорит, что вы идиот», попробуйте ввести время в ПРАВИЛЬНОМ ФОРМАТЕ!». Затем нажмите «ОК». - Это гарантирует, что только данные из вашего «приемлемого» списка могут быть введены как время.
б) теоретически вы можете использовать условное форматирование, чтобы выделить ячейку, в которую добавлены неверные данные. Это позволит избежать использования отдельного рабочего листа для ваших данных проверки данных. Я не сделал это таким образом, поэтому я говорю теоретически. Это может быть другой учебник .
Таким образом, теперь вы можете ввести время без необходимости искать двоеточие и клавишу Shift каждый раз.
Если вам ДЕЙСТВИТЕЛЬНО нужны секунды в формате ячейки, то пользовательский формат ячейки будет выглядеть следующим образом:
ТАК. СЕЙЧАС ВЫЧИСЛИТЬ ЧАСЫ от времени начала до времени окончания . опять же, это основано только на минутах . но если вы можете следовать логике, то может быть расширено и до секунд.
В третьем столбце (предполагая, что столбец A — время начала, B — время окончания, а C — общее количество часов), введите формулу:
= ЕСЛИ (ЗНАЧЕНИЕ (В2)> = ЗНАЧЕНИЕ (А2), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 3 , 2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 3,2))/60)), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 1,2)), 24, ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 3,2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 3,2))/60)))
Теперь, чтобы объяснить это .
Функция «ЕСЛИ» различает время окончания на 24-часовых часах до полуночи и после полуночи. Если после полуночи, к времени окончания добавляется 24 часа, чтобы можно было продолжить расчет времени окончания и времени начала. Если до полуночи, нет необходимости добавлять 24 часа. Таким образом, для целей расчета время 0100 фактически трактуется как 2500, 0200 — это 2600 и так далее. НОТА. это предполагает, что смена длится не более 23 часов и 45 минут. Если он работает дольше, возможно, вам нужно будет включить столбцы даты в формулу . опять же, мне это не нужно.
Функции MID работают с TEXT (следовательно, с функцией TEXT) и возвращают цифры в определенных местах из указанной ячейки в указанном формате . Soooo, «MID (TEXT (B2,» 0000 «), 1,2)» просматривает ячейку B2 и всегда просматривает ее в формате 4 цифры / буквы и возвращает две цифры, начиная с позиции «1». В моем случае это целые часы. Другой пример: «MID (TEXT (A2,» 000000 «), 5,2)» будет смотреть на ячейку A2 и возвращать две цифры, начиная с позиции «5», что может быть целыми секундами.
Я поместил функцию VALUE перед всем этим, чтобы превратить возвращаемый текст в число, чтобы мы могли работать с ним в вычислениях.
Таким образом, это позволяет нам в каждой ячейке обрабатывать часы, минуты и секунды отдельно. Расчеты с часами просты, так как они уже являются целыми числами. Однако минуты на самом деле являются долями часов или долями 60 минут, поэтому, как только вы извлечете цифры, представляющие «минуты», разделите их на «60», чтобы получить «долю» часа. Как и в случае с секундами, разделите на 3600 (количество секунд в часе), чтобы получить секунды в виде «доли часа».
Теперь, когда времена представлены в виде чисел с дробями, вы можете добавлять или вычитать, как вам нравится. В случае моей формулы, даст общее количество часов между временем начала и времени окончания. Я форматирую ячейку с формулой как Custom> 00.00, которая затем позволяет мне умножаться на почасовые ставки и т.д.
Если кто-то все еще действительно хочет секунды в своей формуле. и застрять, дайте мне знать. Если мне скучно на работе, я могу помочь . но секунды . действительно.
Быстрый ввод даты и времени без разделителей
Если Вам часто приходится вводить даты и время в ячейки, то Вам должна понравиться идея писать их сокращенно, без точек-дробей-двоеточий — просто как число. Чтобы в заданном диапазоне ячеек листа, например, число 250699 автоматически превращалось в 25.06.1999, а 1125 в 11:25.
Для этого щелкните по ярлычку листа, куда будут вводиться даты и время и выберите команду Исходный текст (Source Code). В открывшееся окно редактора Visual Basic скопируйте и вставьте следующий код:
Диапазоны A2:A10 и B2:B10 замените на свои области листа, куда будут подобным образом вводиться даты и время, соответственно.
Ссылки по теме
(файл в котором у меня ошибка прикрепить не могу не нахожу кнопки "прикрепить файл"
А ее тут и нет — это комментарии к приему, а не форум. Лучше создайте тему на форуме и прикрепите ваш файл с макросом — а мы его покритикуем
А зависание макроса происходит, скорее всего, из за того, что вы в коде отключаете обработку событий Application.EnableEvents=False, а после возникновения ошибки она у вас обратно не включается.
Сразу Спасибо за сайт. оч помогает.
а как переделать макрос для времени, если вводится формат 21 . 15 (ну или любой другой знак, всякое бывает), ну а нужен обычный 21:15
dDate = DateValue(Left(StrVal, 2) & «/» & Mid(StrVal, 3, 2) & «/» & Right(StrVal, 2))
12 строка подсвечивается для отладки, когда макрос перестаёт работать.
Это происходит, как я заметил, в случае:
1. Сортировки таблицы, несколько столбцов которой заполнены при помощи данного макроса датой и временем.
2. После ошибочного ввода в рабочем диапазоне макроса даты с разделителями, удаления ошибчной записи и последующей попытки заполнить ячейку не применяя разделители, при помощи макроса.
Добрый день! не в коем случаи не хочу обидеть автора статьи, но у меня есть один трюк именно для того, чтобы лишний раз не нажимать shift и какой либо знак. он делается очень легко и для даты и для времени, без всякого там кода VB, сам я ламер в VB поэтому вечно ищу более легкие пути, так вот я делаю так:
для даты:
в формате ячейки -> все форматы 00\.00\.0000
пример:
12122012 -> 12.12.2012
для времени -> все форматы 00\:00
пример:
1212 -> 12:12
после слеша влево можно поставить любой знак, который предпочтителен для Вас.
Надеюсь Вам понравится такая идея.
если у кого будут вопросы или захотите сказать спасибо, я в facebook Parviz Ruziev
Добрый день, описанный вами прием всего лишь придаст форму но не изменит сути, Excel не будет воспринимать введенное значение как дату.
Пример
| Вы ввели значение | Применили формат | Excel думает что это | А надо чтобы думал что это |
| 12122012 | 12.12.2012 | Дата из далекого будущего, если конкретно то примерно через 33 тысячи лет, Excel не отразит эту дату заменив на ##### | 41255 |
| 1212 | 12:12 | 0:00:00 и сколько то наносекунд | 0,51 |
Посмею предложить свой вариант. )))
В формате ячейки (у меня это D2) выставляем формат типа:
00″.»00″.»0000
На проверку вводимых данных задаем условия проверки:
тип данных – другой; и на соответствие формуле в соответствующей ячейке (у меня это R2).
Формула такого вида:
=ЕСЛИ(ИЛИ(И(ДЛСТР(D2)=7;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ(«0″;ЛЕВСИМВ(D2)));»ДД.ММ.ГГГГ»;))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ(«0″;ЛЕВСИМВ(D2)));»ДД.ММ.ГГГГ»;))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;2;2);»00″;))<=12);И(ДЛСТР(D2)=8;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));»ДД.ММ.ГГГГ»;))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));»ДД.ММ.ГГГГ»;))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))<=12;ИЛИ(ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=1;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=3;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=5;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=7;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=8;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=10;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=12;);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);»00″;))<=31);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=4;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=6;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=9;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=11);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);»00″;))<=30);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);»00″;))=2;ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);»00″;))<=29);ИСТИНА;ЛОЖЬ)));ИСТИНА;ЛОЖЬ)
Не пойму почему так отображает, в выше приведенной формуле смайлики заменить на «)» без кавычек естественно.
Соответственно дата вводится без каких-либо посторонних знаков, только цифры. Формула проверяет что бы введенный диапазон был между 01.01.1970 и 31.12.2020, при вводе большей или меньшей даты – выдает ошибку, при попытке ввести 13 и т.д. месяц – выдает ошибку, при попытке ввести более 31 дня (в январе, марте, мае, июле, августе, октябре, декабре) – выдает ошибку, при попытке ввести более 30 дней (в апреле, июне, сентябре, ноябре) – выдает ошибку, при попытке ввести более 29 дней в феврале – выдает ошибку. Осталось добить проверку високосных лет. )))))
Ну и перевести это все безобразие в дату тоже не проблема, у меня в ячейке I2 идет подсчет даты от введенной в ячейку D2 плюс 2 месяца вот такой формулой:
=ЕСЛИ(ЕПУСТО(D2);»»; ЕСЛИ(ДЛСТР(D2)=7; ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);(СЦЕПИТЬ(«0»;ЛЕВСИМВ(D2))));2); ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));2)))
Сразу отвечу на вопрос зачем так все усложнять – просто задача стояла сделать без макросов.
Доброго времени суток Николай,
все сделал как у Вас написано, работает все прекрасно, вот только при повторном вводе даты в ячейки где уже были введены описанным Вами способом даты, выводится как ответ совершенно другая дата, т.к. из-за присвоенного автоматически формата «Дата», введенное значение воспринимается как код даты. Подскажите пожалуйста как это исправить.
Подскажите пожалуйста, если мне нужно даты во многих столбцах (B; N;O; S) начиная с 3 строки указывать, как это прописать в макросе?
Добрый день. Скажите пожалуйста, как можно установить автоматический ввод даты и времени в нескольких столбцах сразу (с помощью макроса, функция Range(«F3:F50200»)
For Each cell In Target ‘проходим по всем измененным ячейкам
If Not Intersect(cell, Range(«F3:F50200» ) Is Nothing Then ‘если изменененная ячейка попадает в диапазон A2:A100
With cell.Offset(0, 1) ‘вводим в соседнюю справа ячейку дату
.Value = Now
.EntireColumn.AutoFit ‘выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
Next cell
End Sub
Excel не распознает ввод даты на английском. Подскажите, что делаю не так ? )
Если ввожу 10янв14, преобразует в дату 10.01.2014 , формат ячейки становится Custom (все ОК)
Если ввожу 10Jan14, остается в формате General
Пробовал заранее ставить формат ячейки «Date», все равно остается текст 10Jan14
Добрый день, спасибо за сайт и за эту статью. Воспользовалась вашем макросом и вставила его в свой файл «Табель уч.раб. вр.» все получилось. Этим файлом еще пользуются руководители подразделений и хотелось бы для них вставить «подсказку» (скриншот как у вас в начале статьи) как всплывающую подсказку, но не знаю как вставлять анимированные картинки, пожалуйста, помогите сделать.
Добрый день, у некоторых пользователей при вводе данных в разные ячейки макрос выдает ошибку Compile erorr: Synteax erorr
Выскакивать окно Visual Basic при любом редактировании значений.
Дико неудобно, при введении каждого значения приходится закрывать окно VB, дважды нажимая на кнопки.
Один пользователь даже пробовал менять дистрибутивы винды и офиса, переустанавливать – везде одно и то же.
Судя по ошибке у стоит библиотека или скрипт, который позволяет выполнять эту процедуру.
Пожалуйста, помогите исправить.
Приветствую! Вопрос в следующем, подскажите, какой должен быть код, чтобы можно было редактировать в дальнейшем дату в введенной ячейке? Т.е я ввел 150216, он мне автоматом перевел в 15.02.2016, супер, но, если ошибся на день, пытаешься отредактировать на 14.02.2016, дата меняется на 20.04.1949 и в дальнейшем выскакивает ошибка «Run-time Error 13. Type mismatch». После этого код больше не работает и необходимо закрывать и открывать заново документ. Это первый момент.
Второй момент, если вводить дату в формате 15022016, то в ячейке отображаются ###### — бесконечное количество решеток.
Спасибо. Код золотой и очень полезен в работе, но хочется чтобы не было таких ошибок т.к будет активно использоваться в работе. Буду очень благодарен.
Здравствуйте! Очень сильно стараюсь вычислить из двух результатов времени лучший, это для забегов спортсменов, т.е. 12:55:10 и 12:55:45, как определить с помощью формул в столбе №3 лучшее время (12:55:10) что бы сразу из двух результатов писался лучший. Где то я ошибаюсь в формул, что то ставлю лишнее или на оборот не до ставляю За ранее Спасибо большое.
Ребята или Николай, кто-нибудь в общем)) Помогите
Замечательный код, но вот вопрос:
Если я хочу внедрить этот код для подобного способа ввода даты в колонку где у меня уже был изначально какой-то формат ячеек к сожалению, и на какой бы другой формат я их не менял, результата нет положительного, тогда как я могу вернуть «никакой формат ячеек» или может мне что то в коде вашем поменять можно?
ЗЫ более того, я ввожу эти 6 цифр не в саму ячейку, у меня для этого есть код, с помощью которого вылазит диалоговое окно с просьбой ввести данные, этот код ссылается на нужную ячейку где я собственно и хотел бы увидеть дату. Но опять же эти 6 цифр я ввожу в диалоговое окно, для которого написал код.
Если не понятно что я имею в виду говоря про код и диалоговое окно, то вот немного подробнее:
Я настроил свою таблицу для работы максимально удобно, чтобы не возить мышкой и кликать триста раз по ячейкам, я уже в исходном коде листа прописал несколько полезных штук, таким образом, что мне надо только в начале ввести в определенную ячейку данные, после чего запускается код и цепь разных действий в виде диалоговых окон типа:
Диалоговое окно «Введите дату» — это как раз первый запрос — и мне она нужна не текущая, а та что я сам впишу, ибо это дэдлайн проекта. Текущая дата вводится автоматом в другом месте, это я уже благодаря кстати Николаю реализовал пару-тройку лет назад, за что большое спасибо и не только за это)) В общем мне пока приходится вводить дату полностью или писать типа «9 мая» чтобы быстрее было, но мне гораздо удобнее было бы все вводить на нампаде, раз 6 цифр ввел нажал Enter, вылазит следующее диалоговое окно «введите бюджет» ну и так далее, нампад — энтер, нампад-энтер — быстро и удобно, и не надо бегать по строке по разным ячейкам глазами выискивать, тем более риск ввести не туда, а колонок много и перепутать легко.
Это я для полной картины просто написал, чтобы понимали)) а то мало ли, может если способ ввода реализуется с помощью вот таких диалоговых окон, то этот ваш код для ввода даты не годится.
Заранее благодарю всех и Николая в особенности.
КСТАТИ! Раз у меня есть текущая дата, то может вместо ввода 6 цифр, можно было бы написать код таким образом чтобы при вводе например цифры 3 на столько увеличивалась текущая дата и вводилась бы как раз в ячейку дедлайна в виде даты? если так можно реализовать код, то это была бы просто бомба))
Например у меня есть текущая дата 6 мая в ячейке A22, вылазит диалоговое окно с просьбой ввести число на которое будет увеличена дата ячейки A22 и введена в ячейку K22 с учетом прибавления того числа что я ввел в диалоговое окно, например я ввел цифру 3 и в ячейке K22 появилась дата 9 мая, то есть код поссчитал 6+3=9 и ввел нужные данные в ячейку K22 в формате даты «9 мая» или «09.05.2017» — без разницы
Как считаете можно такое провернуть??
Скрипт очень хороший, большое человеческое Вам за него спасибо.
Но, я всё же решил немного его доработать, так как при вводе некоторых значений, которые не нравятся скрипту, он вылетал с ошибкой. А именно, например, при попытке ввести двухзначное число в ячейку, он сообщал об ошибке и предлагал либо нажать End, либо Debug. И после этого не важно, что нажать, скрипт переставал работать до перезапуска Excel, так как не успевала отработать функция Application.EnableEvents = True
С новыми поправками таких проблем не возникает. Делюсь:
Здесь в 4 строке задаётся:
1. название листа,
2. столбцы (в моём случае это 2 столбца С — под номером 3, и E — под номером 5)
3. диапазон (в моём случае со строки 2 по строку 9999)
Если Вам нужен только один столбец, и не нужно указывать конечное значение строк, то код в этой строке можно упростить:
В данном коде:
‘Лист1’ — название листа
3 — номер столбца — ‘С’
>1 — начинать со второй строки (исключаем шапку таблицы)
В данном коде осталась одна недоработка:
Если ввести в ячейку дату как положено с разделителями, а не просто голые цифры, то скрипт изменит введённую дату совершенно на другую. Пока борюсь с этим моментом. Как будет решение, изменю данный пост или напишу дополнение.
Быстрый ввод даты в excel
. (41209) — это без, месяц числом реально помогли вводиться даты и если вы хотите заполнить маркер заполнения чисел, комбинаций текстExcelКак видите все достаточно формата отображения датыВесь курс: урок 1 листа, где находится End If End листа. Он запускается нужный блок, и: Установить на сомпе,Про то, как это количество дней, прошедших или словом иAexbro время, соответственно.
Ввод дат и времени в Excel
заполнение ячеек последовательностью. и числа иливыберите пункт просто! и времени в | Ваша таблица If End If на событие изменения под ним Me.ToggleButtonХХ.Value MSCOMCT2.OCX правильно вычислять, лучше
с 1 января т.д.) и задается: _Boroda_, спасибо вопросAexbro пропускает каждый день,Примечание: формулы на основеПараметрыВ этом уроке мы настройках операционной системы.урок 24 скопируйте код If Target.Column =
листа, конкретно ячеки = True .Возможно на Вашем почитать тут. 1900 года (взято через контекстное меню закрыт: Здравствуйте. Есть таблица например Понедельник, среда, При выборе диапазона ячеек, шаблона, который можно. В разделе научились настраивать форматРаботая с ячейками в| урок 3 и вставьте в
13 And Target.Row — А1.На сколько это компе он ужеПоскольку одни сутки в
за точку отсчета) — правой кнопкойWasilich в которой множество пятница, т. д., которые вы хотите установить. Например можноформулы и списки
Формат даты и времени в Excel
отображения дат и Microsoft Excel, которые | урок 4 основное поле редактора > 3 _SOV1 сократит код, как есть, тогда просто системе отсчета даты до текущей даты. мыши по ячейке: А чё никто
ячеек с временными введите Понедельник и повторить в смежных элем1 ввод вщелкните времени в Microsoft содержат дату или | урок 55 сохраните файл And Target.Row <
: Димит, ваш макрос думаешь? И это добавить элемент к Excel принимаются за
А дробная часть и далее не предложил макрос? значениями.Как ускорить процесс среда из двух ячеек, можно перетащить ячейку и затемАвтозаполнение Excel, а также время, Вы имеетеДумаю, из прошлого урока и закройте редактор 2000 Then If очень даже оказался не единственный момент инструментам.
единицу (см.выше), то (0,65417), соответственно, доляФормат ячеек Тем более, что ввода времени (без ячеек начальный и маркер заполнения вниз заполнить ячейки нижеи снимите флажок разобрали несколько полезных возможность применять к Вам уже известно,
- VBA Target.Offset(0, 0).Value = нужным, только не
- в этом коде.Штурмaн для вычисления даты, от суток (1сутки(Format Cells) ТС сам пытался
- ввода разделителей) с затем перетащите маркер один столбец или или справа отАвтозавершение значений ячеек вариантов их ввода ним различное форматирование. что даты и6 измените ячейку «да» Then If хватило нам тяму Сама инициализация формы,
- : Если можно, то отстоящей от заданной
применить. последующим сложением либо заполнения. по одной строки, элем2 Item3, Item4,. на рабочий лист. Например, Вы можете время в Excel в 7 столбце Target.Offset(0, 1).Value = распространить его действие тоже далеко не хочется поподробнее узнать на, допустим, 20Из всех этих фактовВремя вводится в ячейкиВот пример. Может вычитанием этих данных.
Если Вам часто приходится
Ввод ряда чисел, дат или других элементов
но не вниз и т.д.Выделите ячейки, содержащие данные, В следующем уроке отображать в ячейке хранятся в виде и убедитесь в «» Then Target.Offset(0, на весь столбец. блеск. 1570 строк(правда про MSCOMCT2.OCX - дней, достаточно прибавить следуют два чисто с использованием двоеточия. кому пригодится Например: в А1 вводить даты и несколько столбцов иВыделите ячейку, содержащую сочетание которые вы хотите мы поговорим о только день недели,
порядковых чисел, началом работе макроса
Быстро ввести в ячейку столбца повторяющихся элементов
1) = DateПомогите, плиз! вкючая пустые строки).
где его взять к дате это практических вывода: Напримерstria вводим 1230 видим время в ячейки,
на нескольких строках. текста и номера повторить в смежных 7-ми функциях Excel,
или только месяц отсчета которых принято
842-й End If EndДимит Я думаю, что и как установить? число.Во-первых, Excel не умеет16:45
: Здравствуйте! (12:30), в В1 то Вам должнаИнтеллектуальная кнопка
или начальный номер. ячеек. которые позволяют извлекать и год, или считать некое: Здравствуйте, все сделал If End If: Замените строку раз в 10dlaptevЭту операцию осуществляет функция работать (без дополнительныхПо желанию можно дополнительно Решил сделать в вводим 1700 (видим понравиться идея писать
Быстро ввести столбец повторяющихся элементов в несколько ячеек одновременно
Параметры автозаполненияВыделите ячейки, которые нужноВыделите ячейки, которые нужно нужные параметры из
же только время.0 января 1900 года по вашей инструкции
End SubIf Target.Address = легко можно сократить. Согласен: http://www.yandex.ru/yandsearch?text=MSCOMCT2.OCXРАБДЕНЬ настроек) с датами уточнить количество секунд Excel подсчет налета 17:00) в С1 их сокращенно, безнажмите кнопку, а
заполнить маркер заполнения заполнить маркер заполнения значений дат иПолучить доступ ко всем. К счастью, в
и получилось =)))
«$A$1» Then с Вами Что-то
в частности тут(WORKDAY) ранее 1 января
используя быстрый ввод получаем 4:30. точек-дробей-двоеточий — просто
затем выполните одно.
. времени. На этом возможным форматам Вы
ячейках мы видим спасибо огрмное. очень
: Товарищи, помогите пожалуйста!на я раздухарился сhttp://ms-access.ru/SetupWizard_mscomct2.html. Она позволяет вычислить 1900 года. Но также через двоеточие: времени (2300 =Проблем с вводом как число. Чтобы
из следующих действий:Примечание:Примечание: урок закончен. Всего можете на вкладке не эти числа, помогло.Я абсолютно неIf Target.Column = кодом в этомMasalov дату, отстоящую вперед это мы переживем!16:45:30 23:00) нет : достаточно в заданном диапазонеЗадача
Быстро ввод ряда чисел или комбинаций номер текста
При выборе диапазона ячеек, При выборе диапазона ячеек, Вам доброго иЧисло а привычные дляgera_vip знаю МVB но 1 Then календарике.: Я когда столкнулся или назад относительно ;)
И, наконец, никто неВопрос в следующем:-Как задать формат ячейкам
ячеек листа, например,Действие которые вы хотите
которые вы хотите успехов в изучениидиалогового окна нас даты и: Дабы не создавать очень хачется сделатьв данном случаеSOV1 с такой же начальной даты наВо-вторых, с датами и
запрещает указывать дату без ввода даты А1 и В1 число 250699 автоматическиСкопируйте все содержимое ячейки,
повторить в смежных
повторить в смежных
Excel.Формат ячеек время, которые могут
новую тему, спрошу следующее: 1 — это
: Всем доброго времени
проблемой сделал собственну нужное количество рабочих временем в Excel
и время сразу excel мог определить
«0/:00», но как превращалось в 25.06.1999, включая формул и
ячеек, можно перетащить ячеек, можно перетащитьАвтор: Антон Андронов. В категории храниться в самых здесь.при смене или номер столбца, т.е. суток. форму календаря. (см. дней (с учетом возможно выполнять любые вместе через пробел, переход суток? сделать чтобы в
Быстро ввести серию дат, времени, рабочие дни, месяцы и годы
а 1125 в форматирования, без повторяющихся маркер заполнения вниз маркер заполнения внизПримечание:Дата различных форматах. ВНашел код, который появлении значения в «А»
Такая вот задача: приложение). выходных суббот и математические операции. Именно
то есть т.е Взлет 23:00, ячейке С1 эти
11:25. серии один столбец или один столбец илиМы стараемся какприведены встроенные форматы этом уроке Вы добавляет дату. 7-ом столбце вДимитпри выборе значения
Так как если воскресений и государственных потому, что на27.10.2012 16:45 посадка 01:00 .
значения суммировалось как
Для этого щелкните по
Выберите команду по одной строки, по одной строки, можно оперативнее обеспечивать
дат: узнаете, как вводитьPrivate Sub Worksheet_Change(ByVal
6-ом отображалась дата: Замените строку из списка в
переносить Ваш ексельный праздинков). Использование этой самом деле они
Для ввода сегодняшней даты
итого 2:00. время ярлычку листа, куда
Копировать ячейки но не вниз
но не вниз вас актуальными справочнымиА в категории
даты и время Target As Range) ввода этого значения.If Target.Address =
одной ячейке таблицы файлик на другой функции полностью аналогично
— числа! А в текущую ячейкуМожет кто нибудьСпасибо будут вводиться даты.
несколько столбцов и несколько столбцов и материалами на вашем
Время в Excel, чтобы For Each cellНадо чтоб работало «$A$1» Then
— в другой компьютер, на котором применению функции
вот это уже можно воспользоваться сочетанием поможет пилоту ?jakim и время и
Заполнение ячеек на основании на нескольких строках. на нескольких строках.
языке. Эта страница– встроенные форматы получать требуемое форматирование. in Target ‘проходим это все сона ячейке должна появиться может не оказатьсяЧИСТРАБДНИ раскрывает перед пользователем клавишКонечно поможет. Если: Не знаю как выберите команду начальные сведения вИнтеллектуальная кнопка
Быстрый ввод даты и времени без разделителей
Интеллектуальная кнопка переведена автоматически, поэтому времени:Даты и время в по всем измененным 2-ой строки.If Target.Column = текущая дата. И MSCOMCT2.OCX — календарь(NETWORKDAYS) массу возможностей.Ctrl + Ж пилот прочитает Правила в 2007, а
Исходный текст первой ячейкеПараметры автозаполненияПараметры автозаполнения ее текст можетДля того чтобы применить Excel можно вводить ячейкам If NotЕсли не трудно 1 Then самое главное -
не заработает.описанной выше.Считается простым вычитанием -(или форума и создаст в 2010 всё(Source Code)Нажмите кнопкунажмите кнопку, анажмите кнопку, а содержать неточности и форматирование к ячейке, в виде порядкового Intersect(cell, Range(«A2:A1048576»)) Is объясните как этов данном случае дата не должнаdlaptevВас не в понедельник из конечной датыCTRL+SHIFT+4 свою тему. Крайне очень просто.. В открывшееся окно»ряд заполнения» затем выполните одно затем выполните одно грамматические ошибки. Для достаточно выбрать нужный числа или доли Nothing Then ‘если сделать от А 1 — это больше изменяться, тк: gif красивый, только родили? Нет? Уверены? вычитаем начальную иесли у вас желательно, чтобы еще_Boroda_ редактора Visual Basic
. из следующих действий: из следующих действий: нас важно, чтобы формат в разделе суток, но как
Excel быстрый ввод времени (Формулы)
измененная ячейка попадает до Я т.к. номер столбца, т.е. таблица редактируется в в коде «несъедобный». Можно легко проверить переводим результат в другой системный язык и с примером: скопируйте и вставьтеКопирование только форматирования ячеекЗадачаЗадача эта статья была
Тип Вы сами понимаете, в диапазон A2:A1048576 я не ориентируюсь «А» течение месяца.народ вон коды при помощи функцииОбщий (General)
по умолчанию).
в Excel.200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(A1;»00\:00″)+ТЕКСТ(B1;»00\:00″) следующий код:Нажмите кнопкуДействие
Действие вам полезна. Просим и нажать
это не совсем
With cell.Offset(0, 4)
совсем (( могуSOV1
SOV1 раздает (из недавних):ДЕНЬНЕДчисловой формат, чтобыЕсли скопировать ячейку с
valenelena
или
Private Sub Worksheet_Change(ByVal
заполнить только форматированияСкопируйте все содержимое ячейки,
Скопируйте все содержимое ячейки вас уделить пару
ОК удобно. К тому
‘вводим в соседнюю лишь макрос создать: Огромное Вам спасибо,: Нет желающих помочь?http://relib.com/forums/thread883354.aspx(WEEKDAY)
показать разницу в
датой (протянуть за: в 2010 уКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ТЕКСТ(A1+B1;»00\:00″) Target As Range). включая форматирование и с форматированием секунд и сообщить,. Нужное форматирование будет же при таком справа ячейку дату и то не Димит. Слишком сложно илиШтурмaн
из категории
днях: правый нижний угол меня нет такого
Да, 12:30+17:00 будет Dim vVal DimСкопируйте содержимое ячейки, включая
формулыВыберите команду помогла ли она
применено. Если встроенных подходе к ячейке (чем выше цифра, работает почему-то ((И, чтобы закончить наоборот?
: Пожалуйста, код.Дата и время
Здесь ситуация чуть сложнее. ячейки), удерживая
типа ввода времени. 5:30 StrVal As String формул без форматированияВыберите команду
Копировать ячейки вам, с помощью числовых форматов Вам придется каждый раз
тем дальше ячейка)заранее спасибо! этот вопрос: как
ДимитМожет кому-нибудь пригодится.
. Необходимо не учитыватьправуюКак вводить времяAexbro Dim dDate AsЩелкните
Копировать ячейки. кнопок внизу страницы. будет недостаточно, то
применять определенный числовой .Value = DateДимит
сориентировать макрос на: При изменении А1dlaptevПервый аргумент этой функции субботы с воскресеньямикнопку мыши, то вводим 1230 видим
: _Boroda_, Спасибо за Date If Target.Cells.CountЗаливка без форматирования.Копирование только форматирования ячеек Для удобства также
можно воспользоваться категорией формат. ‘Дата .EntireColumn.AutoFit ‘выполняем
Основные принципы работы с датами и временем в Excel
Видео
: Private Sub Worksheet_Change(ByVal определенный диапазон ячеек. в В1 появится: писал человек явно — ячейка с
Как вводить даты и время в Excel
и праздники. Для можно выбрать - (12:30) ответ, но к > 1 Then.
Копирование только форматирования ячеек
приводим ссылку на
Excel предлагает несколько форматов
автоподбор ширины для
Target As Range)
дата, но если неленивый. как минимум датой, второй - такого расчета лучше как именно копировать_Boroda_ сожалению ваша формула Exit Sub IfИспользование начальную дату вНажмите кнопкузаполнить только форматирования оригинал (на английском . Здесь Вы также для ввода временных
столбца B, чтобы If Target.Cells.Count = до А100? (просто
ячейка В1 пуста
половину кода легко тип отсчета дней воспользоваться функцией выделенную дату:
: Если именно «видим»
дает следующий результат: Not Intersect(Target, Range(«A2:A10»)) первой ячейке, чтобызаполнить только форматирования.
языке) .
Быстрый ввод дат и времени
сможете найти много данных. Если Вы дата умещалась в 1 And Target.Column в первых строках (т.е. первый раз). можно выкинуть, а недели (самый удобныйЧИСТРАБДНИЕсли Вам часто приходится
(а по факту8:00 10:30 02:30 Is Nothing Then заполнить ячейки с.Копирование содержимого ячейки безВыполните одно из указанных всего интересного. примените такой формат,
ячейке End With = 7 And идет шапка таблицы. )Не знаю это то и больше. — 2).
(NETWORKDAYS) вводить различные даты в ячейке все11:30 15:00 03:70 With Target StrVal последующей датами Скопируйте содержимое ячейки, включая форматирования
Как Excel на самом деле хранит и обрабатывает даты и время
ниже действий.Если ни один из Excel автоматически преобразует End If Next Target.Row > 1Димит ли нужно. [image]http://kvvmku.ru/forum/images/smiles/d_smily_tooth.gif[/image]Поскольку время в Excel,из категории в ячейки листа, равно находится число17:30 23:00 05:70
= Format(.Text, «000000»)Нажмите кнопку формул без форматирования
ЩелкнитеВ столбце введите первые вариантов Вас так введенную дату (или cell End SubМожет Then Target.Offset(0, -1): ну можно добавитьPrivate Sub Worksheet_Change(ByValШтурмaн как было сказаноДата и время то гораздо удобнее 1230), то поставьтегде b1-a1 дает If IsNumeric(StrVal) AndЗаполнить по днямЩелкните
Заливка без форматирования несколько букв для и не устроил,
- время) в порядковое ли кто-то подсказать, = Date End на ограничение по Target As Range): Ну ну, интересно выше, такое же
- . В качестве аргументов это делать с формат ячейки 00:00 2:30 Len(StrVal) = 6.Заливка без форматирования. записи. то можно создать
Количество дней между двумя датами
число (долю суток) как его модернизировать, If End SubНу строкам, типа: If Target.Address = что-же там можно число, как дата, этой функции необходимо
Количество рабочих дней между двумя датами
помощью всплывающего календаря:А вот еслигде b2-a2 дает Then Application.EnableEvents =Используйте имя начального дня.Примечания: Если при вводе знаков пользовательский числовой формат. и применит к чтобы при удалении обьяснять тут особоIf Target.Column = «$A$1» Then If выкинуть ? но только дробная указать начальную иЕсли нужно, чтобы в
Вам действительно нужно 3:70 вместо 3:30 False dDate = недели в первойПримечание: совпадает с существующей Его создать достаточно ячейке формат значения из колонки не чего 1 And Target.Row Target.Offset(0, 1).Value =dlaptev его часть, то конечную даты и ячейке всегда была преобразовать во время,где b3-a3 дает DateValue(Left(StrVal, 2) & ячейке для заполнения Узор заливки можно изменить,Чтобы быстро ввести те записи в этом легко, если вДаты
Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.
А, удалялась иМакрос срабатывает на > 4 Then
Сдвиг даты на заданное количество дней
«» Then Target.Offset(0,: to Штурмaн с временем также ячейки с датами актуальная сегодняшняя дата то нужен Ваш 5:70 вместо 5:30 «/» & Mid(StrVal, ячейки с последующей выбрав два или
Сдвиг даты на заданное количество рабочих дней
же данные в столбце, Excel отображает качестве образца использовать(или Времени), установленные дата. А то изменение одной ячейкиSOV1 1) = DateТочно не ленивый возможны любые математические выходных (государственных праздников, — лучше воспользоваться файл. Конечно, вя бьюсь над 3, 2) & рабочие дни (исключая больше начальной ячеек,
Вычисление дня недели
несколько ячеек одновременно, меню список записей, встроенные числовые форматы. по умолчанию. получается если в в 7 столбце: Димит, еще раз End If End :-)
операции, как и больничных дней, отпусков, функцией новой теме. этим уже неделю, «/» & Right(StrVal,
Вычисление временных интервалов
суббота и воскресенье) прежде чем перетащить выделите все ячейки, уже используется в Чтобы создать пользовательскийНа рисунке ниже представлена колонке А (даже и строке 2 большое спасибо! Изначальная If End SubПроще установить MSCOMCT2.OCX с датой -
отгулов и т.д.):СЕГОДНЯvalenelena испробывал десятки вариантов 2)) .NumberFormat =Щелкните заливки маркера. Например введите нужные сведения столбце. числовой формат, выполните таблица вариантов ввода пустой) нажать клавишу и дальше. цель достигнута. Получилось
Быстрый ввод даты на форме в Excel
SOV1 на компе, и сложение, вычитание иПримечание(TODAY): все разобралась . и постоянно прихожу «dd/mm/yyyy» .Value =Заливка дней недели
если вы хотите и нажмите клавишиНажмите клавишу Стрелка вниз,
следующие действия: даты и времени, Del, то тожеМакрос должен находится следующее (может еще
: Спасибо, Димит, что не заморачиваться. т.д.: Эта функция появилась: Спасибо
к этим же CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))
.
заполнение ячеек последовательностью
CONTROL + RETURN. чтобы выбрать совпадающиеВыделите ячейку, к которой которые поддерживает Excel. происходит запись даты в модуле листа.
кому-нибудь пригодится): при откликнулись!Максимум чем можетНюанс здесь только один. в стандартном набореЕсли выделить ячейку сКак обычно, кому надо
результатам End If EndЗаливка ячеек и последующие
чисел, например 2, Этот метод работает
записи и нажмите
необходимо применить требуемый В левой колонке
в соответствующий столбец.
842-й появлении значения вПроблемму решили другим отличаться созданный собственноручно Если при сложении функций Excel начиная датой и установить
быстро — смотримспасибо With End If месяцы года с
4, 6, 8. через все выделенные
клавишу RETURN. числовой формат.
показаны значения, которыеА нужно время: Спасибо. ))
столбце №7 в способом: «знакомые» помогли контрол, это внешним нескольких временных интервалов с 2007 версии. для нее
видео. Подробности иAexbro
If Not Intersect(Target,
помощью начального название
введите 2 и
ячейки.
Примечания:
Откройте диалоговое окно нужно вводить в от времени очищатьА это как? ячейках с 4 написать макрос вставки видом, а функционально, сумма получилась больше В более древнихОбщий формат нюансы — в: jakim, не все Range(«B2:B10»)) Is Nothing месяца в первой
4 из двухЕсли вы не хотите Формат ячеек ячейку, а в таблицу полностью. я просто совсем по 2000 и даты при нажатии все придет к 24 часов, то версиях сначала необходимо(правой кнопкой по тексте ниже: так просто как Then With Target
Автоматический ввод текущей даты в Excel
ячейке ячеек начальную и интеллектуальная кнопка
Excel автоматически завершается только
и перейдите на правой — результатЗаранее спасибо! не в курсе в столбце №13 кнопки «Дата». Но одному. MSCOMCT2.OCX. :-)ИМХО Excel обнулит ее подключить надстройку ячейкеЕсли иметь ввиду российские
может показаться, посмотрите vVal = Format(.Value,Нажмите кнопку затем перетащите маркер
Параметры автозаполнения для тех записей, вкладку преобразования. Стоит отметить,gera_vip ((
в ячейках с этот вариант тоже
Например,дикое количество вот и начнет суммироватьПакета анализаФормат ячеек региональные настройки, то мой ответ Borode «0000») If IsNumeric(vVal)Заполнить по месяцам заполнения.
для отображения при которые содержат текстЧисло
что датам, вводимым: Помогли решить проблему!вот что мне 4 по 2000 интересный. таких строк: опять с нуля.
. Для этого идем- вкладка Excel позволяет вводить я видно не And Len(vVal) =
.Можно быстро заполнение ячеек перетаскивании маркера заполнения,
или сочетание текста. без указания года,Если кому-то пригодится:
надо сделать чтобы — справа отТолько объясните «чайнику»Me.ToggleButton1.Value = True Чтобы этого не в менюЧисло дату очень разными
совсем ясно поставил 4 Then Application.EnableEventsЧтобы заполнить ячейки с последовательностью даты, времени, его можно отключить. и чисел. Операции,В категории
присваивается текущий год,
Private Sub Worksheet_Change(ByVal у меня в
этих ячеек появляется — у нас
Me.ToggleButton2.Value = False
происходило, нужно применитьСервис — Надстройки
- способами — и свой вопрос. Мне = False .Value
последующей ежегодный шагом рабочие дни, месяцы
В меню которые содержат только
Все форматы
а именно выставленный Target As Range)
документе работало то, дата и больше макрос запускается кнопкой,Me.ToggleButton3.Value = False
к итоговой ячейке(Tools — Add-Ins)Общий
понимает их все: необходимо водить время = Left(vVal, 2) с помощью начальную
и годы. Например,Excel числа, даты илив поле
в настройках Вашей For Each cell что вы написали не изменяется.
а как подключить—————————— формат
и ставим галочку), то можно увидеть»Классическая» форма без разделителей и & «:» & дату в первой можно ввести понедельниквыберите пункт времени не завершена.Тип операционной системы. In Target If выше ?Private Sub Worksheet_Change(ByVal данный макрос?Me.ToggleButton43.Value = True37:30:55
напротив интересную картинку:3.10.2006 потом производить вычисления Right(vVal, 2) .NumberFormat ячейке в ячейке иПараметрыЭлементы, которые находятся ввведите необходимый кодЭто не все возможные cell <> «»Извините за откровенную Target As Range)И еще макросЯ уж не:Пакет анализаТо есть, с точкиСокращенная форма с этими данными. = «[h]:mm» EndНажмите кнопку затем заполнить ячейки. В разделе строке не включаются числового формата. В
варианты, которые поддерживает Then If Not
безграмотность в этом If Target.Column = «найти» перестает работать говорю, что такие
nika_adm(Analisys Toolpak) зрения Excel, 27.10.20123.10.06 А в вашем
If End WithЗаполнить по годам ниже или справа
средства разработки в список соответствующих нашем случае мы Excel. Но даже Intersect(cell, Range(«W9:W3000»)) Is вопросе. 7 And Target.Row при защите листа. вещи в цикле
: Есть форма, требуется
. После этого в 15:42 = 41209,65417С использованием дефисов примере мне по End If Application.EnableEvents. Вторник, среда, четвергнажмите кнопку записей. введем: этих вариантов Вам Nothing Then With
Димит > 3 _Димит можно делать, но вводить даты -
Мастере функций вНа самом деле любую
3-10-6 прежнему необходимо вводить»:»
= True EndПримечание: и т.Изменить
Если вы не хотитеДДДД ДД.ММ.СС чч:мм:cc будет предостаточно. cell.Offset(0, -1) .Value: 1 откройте файл And Target.Row <
: Могу только посоветовать вот то, что надо найти компонент,
категории дату Excel хранитС использованием дроби
спасибо Sub
Узор заливки можно изменить,Выделите ячейку, содержащую начальнуюи снимите флажок к записям, которыеи нажмем
Некоторые варианты отображения дат, = Date .EntireColumn.AutoFit Excel
2000 Then If изменить макрос или можно было это
чтобы при активацииДата и время и обрабатывает именно3/10/6
AexbroДиапазоны A2:A10 и B2:B10 выбрав два или дату, время, деньПоказать параметры вставки интеллектуальные введите сравниваемое автоматически
Ввод даты без разделителей
Если Вам часто приходится работать с датами в ячейках Excel, то Вас наверняка задалбывает каждый раз вводить точки или полностью указывать год.
Этот вариант с помощью макроса предоставляет возможность писать даты сокращенно, без точек/дробей — просто как число, т.е. 101019 автоматически превратится в 10.10.2019.
Для этого создайте документ Excel с поддержкой макросов. На открытом листе щелкните правой клавишей мыши по ярлычку листа,
выберите команду «Просмотреть код», в более ранних версиях Word «Исходный текст». В открывшееся окно редактора Visual Basic скопируйте и вставьте следующий код:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(«A2:A10»)) Is Nothing Then
StrVal = Format(.Text, «000000»)
If IsNumeric(StrVal) And Len(StrVal) = 6 Then
dDate = DateValue(Left(StrVal, 2) & «/» & Mid(StrVal, 3, 2) & «/» & Right(StrVal, 2))
.Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))
Диапазоны A2:A10 замените на свои области ячеек листа, куда будут вводиться даты.
Даты до 10 можно вводить одной цифрой, либо с нулём (01, 02 и т.д.), месяц и год должен быть только двухзначной цифрой.
В случае ошибки ввода, если вместо минимум 5 цифр ввести 4 или неправильно указать месяц, то даты будут выводиться неверно и может выйти окошко
В этом случае нажмите «End», сохраните и закройте документ, затем откройте заново и продолжайте вводить цифры дальше.
661 пост 14.8K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Мне кажется большинству рядовых пользователей экселя это вряд ли будет полезно.
Более того, если я правильно помню, что xlsb, что xlsm плохо воспринимаются с мобильного
в начале кода процедуры вставьте директиву
On Error Resume Next
или On Error GoTo 0
и по ошибке вылетать не будет.
Защита от дурака для электрика
Товарищи, я не автор ролика (который с бородой и в белой пластиковой шапке), я просто разместил его ролик у себя в ленте Пикабу. Спасибо за панимане.
Я давненько видел оригинал ролика, но тут с пояснениями на русском. Попытался найти этот ролик тут, но после просмотра 10 страниц решил разместить.
Полезная информация будет не лишней.
Почти год назад погиб от удара током мой хороший знакомый, его вина тоже есть, потому что он поверил электрику, который сказал, что щитовая обесточена, но человека уже нет ‘-(
Мой опыт — Как получить землю в аренду от государства. Часть 2. Подготовка документов. Почти инструкция
С момента выхода предыдущего поста на меня подписалось более 400 человек, чем я был сильно удивлен и рад. Не думал что эта тема так заинтересует людей. Отдельное спасибо анонимному пикабушнику, задонатившему мне немного денежки. Это был первый донат мне, он был очень неожиданным и приятным.
> Подготовка документов
В предыдущем посте мы остановились на том что выбрали, осмотрели участок и вернулись к компьютеру.
Что делать дальше?
Для участия в аукционе нам необходимо пройти аккредитацию на площадке (конкретно в нашем случае на АО «РАД»).
Для этого нам необходима Квалифицированная Электронная Подпись (далее — КЭП) физического лица. Для ее получения нам понадобится:
> Этап 1. Налоговая
Конкретно у меня — древнего динозавра — свидетельство ИНН старого образца, выданное в единственном экземпляре на гербовом бланке. Это свидетельство имеет юридическую силу, однако есть и минус — оригинал данного свидетельства необходимо будет носить с собой на все проверки, что несколько неудобно. Вопрос решается получением свидетельства в электронном виде подписанного ЭЦП налоговой. Это и удобнее, и автоматические проверки на сайтах проходит быстрее.
Для этого идем на сайт налоговой ( https://nalog.ru ) и входим в личный кабинет физического лица, используя вход через госуслуги. Если вы раньше не заходили туда вас попросят зарегистрироваться — регистрируемся и попадаем в личный кабинет.
Первым делом идем в Профиль — Сведения
Далее жмем на «Свидетельство ИНН».
Здесь два варианта — либо у Вас будет как у меня — кнопка «скачать свидетельство».
Либо же у вас будет написано, что «Ваше свидетельство не зарегистрировано в электронных системах. Для его получения необходимо заполнить заявку на получение свидетельства».
А чуть ни же — «Необходимо иметь электронную подпись для получения свидетельства».
«Круг замкнулся» — скажете вы? Нет! Прям в личном кабинете на сайте можно получить Неквалифицированную электронную подпись (НеКЭП), которую можно использовать внутри личного кабинета для получения свидетельства. Для этого идем в «Профиль» — «Настройки профиля» и выбираем «Электронная подпись». Справа выбиваем «Ключ электронной подписи хранится в защищенной системе ФНС России (рекомендовано)», заполняем поля пароля для контейнера и жмем на кнопку «Отправить запрос» .Заполняем данные если потребует, соглашаемся с условиями и ждем генерацию сертификата. Это займет примерно полчаса.
После того как получили НеКЭП — идем обратно в «Свидетельство ИНН», заполняем запрос на получение ИНН и подписываем его при помощи НеКЭП Не волнуйтесь — номер вашего ИНН не изменится.
Жмем уведомления об исполнении запроса. Лично мой исполнили примерно за час, после чего я смог скачать свидетельство в электронном виде подписанного КЭП Налоговой. Оно то нам и нужно. Самое главное в нем — как раз электронная подпись. На открытом документе выглядит это вот так.
> Этап 2. Получение КЭП, или как его еще называют — ЭЦП (электронная цифровая подпись)
КЭП необходимо получать в аккредитованном сервисном центре. Их много, полный список можете посмотреть здесь.
Для себя я выбрал ЭЦП от «Контура» — как по мне у них проще и быстрее.
Идем на сайт https://ca.kontur.ru и выбираем «Для физических лиц» -> «Для личных целей». Далее жмем на «Отправить заявку» и видим вот это:
Для тех кто никогда не пользовался ЭЦП рекомендую выбирать опции на скрине — вам так будет проще:
Защищенный носитель — это такая «специальная флешка», которая правильно называется «токен», на которой будет храниться ваша электронная подпись. Можно конечно и без нее, но необходимо иметь специальные знания чтоб настроить систему для работы без нее.
Лицензия КриптоПро CSP — дело в том что крипто про — это платная программа. А лицензия стоит денег. Конечно, есть и бесплатные варианты — например VipNet CSP (лично я пользуюсь им), однако настройка правильной работы сайтов с Випнетом — это тема отдельной огромной статьи. В общем нужны знания и опыт, и далеко не у всех получится. По этому выбираем криптопро и не паримся.
Далее спускаемся вниз и жмем «Купить онлайн». Нас перекидывает на вот такую страничку.
Заполняем данные как паспорте, жмем «далее» и оплачиваем. После оплаты нас перекинет на сайт https://i.kontur-ca.ru/ , в котором будет находиться ваша заявка на ЭЦП. Нам необходимо ее заполнить. На одном из этапов заполнения вам необходимо будет сфотографировать/отсканировать страницы вашего паспорта и загрузить их — по этому не забываем его. А также будет проходить проверка ИНН на сайте налоговой. Эта проверка иногда не проходит — ничего страшного. Сбоку есть таймер. Ждем 10 минут, и появится кнопка «Загрузить вручную». Жмем а нее и грузим свидетельство, что мы скачали на этапе 1. Жмем далее, выбираем ближайший к вам удостоверяющий центр и отправляем заявку на получение. Ждем пока ее одобрят — вам при этом придет смс оповещение и письмо на почту. После берем паспорт, распечатанное свидетельство ИНН, ноги в руки и едем в удостоверяющий центр окончательно деанониться.
Там наши документы проверят, нас сфотографируют, дадут нам токен, потребуют расписаться в документах. После чего отправят домой.
Дальше нам нужно вставить токен в компьютер и вернуться на сайт https://i.kontur-ca.ru/ , где нам с радостью сообщат о том, что «сертификат готов, настройте свой компьютер.» Кривим лицо и устанавливаем все что потребуется. Мы молодцы? Нет конечно, сдеанонили себя, но тут ничего не попишешь. В итоге мы получаем токен с КЭП, кучу (не)нужного софта на компе.
А теперь важный момент — храните ваш токен в сберегательной кассе в очень надежном месте. Не давайте его никому в руки. Это практически ваш паспорт — только в цифровом мире. Украв его у вас, абсолютно любой сможет прикинуться Вами в интернете, взять на вас кредит, ипотеку, получить госуслуги от вашего имени, заказать шлюх, и Вы уже не отвертитесь что это были не Вы, а Вася Пупкин. Если вы вдруг потеряли или у вас украли токен — немедленно звоните в удостоверяющий центр и отзывайте сертификат! Иначе есть риск остаться без штанов и с большой дыркой в жопе!
> Аккредитация на электронной площадке
Теперь переходим ко вкусному.
Идем на электронную площадку и регистрируемся на ней, если вы этого еще не сделали. Заходим в личный кабинет -> аккредитации. Жмем на кнопочку «Подать заявление».
Выбираем «Приобретение имущества на ЭТП». Направление смотрим в карточке заинтересовавшего аукциона, в нашем случае это «Аренда и продажа земельных участков».
Далее жмем продолжить и заполняем открывшуюся форму.
Заполняем все как паспорте — буква в букву. Внимательно заполняем банковские реквизиты — по ним вам в случае чего будут делать возврат задатка. Далее вам предлагают загрузить скан паспорта и ИНН. Инн мы подгружаем тот, что получили в налоговой. А вот с паспортом есть нюанс — сканировать паспорт нужно в самой первой страницы (той, где нарисован герб), до самой последней страницы (там где находится извещение)! Нельзя пропускать ни одной страницы, даже если на них нет ничего! Если что-то пропустили — аккредитацию не примут.
Все заполнили? Подписывайте заявление своей ЭЦП и отправляйте на проверку.
Проверка занимает до 3-х рабочих дней, однако я заплатил 25000 руб. за услугу экспресс аккредитации, поскольку срок принятия заявок на мой аукцион истекал. Как вы здесь уже догадались — в качестве примеров я использую другой аукцион, не тот на который я подавался. Нет не скажу на какой.
В итоге меня аккредитовали за 3 часа. Но можно и подождать — я не зря в предыдущей статье говорил, что нужно смотреть на сроки.
> Внесение задатка
После того как вас аккредитуют — у вас появится отдельный лицевой счет для данной аккредитации. На него нам нужно внести задатки для участия в выбранных аукционах, если задаток отправляется на счет торговой площадки (см предыдущий пост).
Идем в личный кабинет — лицевые счета. Выбираем нужный и жмем на кнопочку «пополнить баланс».
Вносим необходимую сумму и дальше у нас два путь. Оплачивать либо платежным поручением, что потребует ручного ввода, либо по QR коду. Я выбрал QR — так для меня проще. Однако помните, что оплачивать крайне желательно с того счета, реквизиты которого вы внесли на этапе аккредитации! Нас даже предупреждают, что задатки от третьих лиц не принимаются.
Помните — деньги на счет упадут в течении одного рабочего дня. Не забывайте про сроки!
> Заявка на аукцион
После того как все муки пройдены, мы сможем участвовать в желанном аукционе.
Для этого идем на страничку аукциона, и видим, что сбоку появилась кнопочка «отправить заявку на участие».
Для начала скачиваем необходимые для заполнения документы (см предыдущий пост), заполняем их, сканируем, а после жмем на эту кнопку. Прикрепляем необходимые документы. Паспорт и свидетельство ИНН погрузятся автоматически из профиля.
Подписываем все ЭЦП и видим, что «Ваша заявка на участие №ХХХ зарегистрирована».
Также она появится в разделе личного кабинета.
Осталось только дождаться этапа рассмотрения заявок и собственно — самого аукциона!
> Важное, но не относящаяся непосредственно к аукциону.
> После прохождения аккредитации вас попросят подключиться к Контур Диадок для получения и подписания платежных документов. Это бесплатно. На сайте Диадока можно оставить заявку только юридическому лицу. Чтобы подключиться физику — необходимо позвонить в техподдержку Контура по номеру 8 800 500-50-80, попросить переключить вас в техподдержку Диадока. Сказать что вам необходимо подключить сервис для участия в торгах. Вас спросят Ваш ИНН и есть ли у вас Действующая ЭЦП. Они у нас есть, по этому говорим что да, есть. Нам подключат Диадок и проинструктируют как его настроить. После чего идем в личный кабинет эл. площадки и и пишем в техподдержку, что мы подключили себе контур диадок, просьба прислать приглашение. Его пришлют внутрь диадоке, и вот, мы теперь можем электронно подписывать фин документы.
> Полученной ЭЦП можно не только на площадке, да и вообще везде. Можно на госуслугах привязать ее ко входу, можно на сайте налоговой, можно в приложении банка и так далее.
> Послесловие
Спасибо всем кто прочитал этот длиннопост до конца. Постарался описать все нюансы. Надеюсь ничего не упустил.
Отдельно хотелось бы коснуться законов и правовых норм.
Здесь и ниже скорее потрындеть, чем конкретика.
Касательно всех так волнующих «перекупов», что берут землю, строят хибару, регистрируют ее и продают. Говорю сейчас о конкретно своем регионе, в вашем может быть несколько по другому.
Порылся в интернетах и вот что нашел (касательно ИЖС).
Выкуп земли из аренды в собственность возможет только при наличии на нем жилого строения.
Сходя из этого «недобросовестные барыги» используют такую схему:
1) Строится не добротный дом для проживания, а маленькая постройка без удобств из дешевых и быстровозводимых материалов. Или только «коробка с крышей» с минимальными коммуникациями или вообще без них.
2) После строительства приглашается кадастровый инженер с просьбой внести в технический план эту постройку как жилой дом.
3) Технический план подается в администрацию. Сотрудник видит, что в техническом плане указан жилой дом и следовательно оформляет уведомление о соответствии.
4) Гражданин оформляет «дом» в собственность и подает заявление на выкуп арендованного участка.
5) В течение месяца на участок должна приехать комиссия и проверить — действительно ли построен жилой дом и пригоден ли он для проживания. Однако она обычно не приезжает, а верит подданным документам. Дом же указан как жилой.
6) Без такой тщательной проверки администрация позволяет арендатору выкупить участок по льготной цене.
7) Арендатор, получив на руки все нужные документы, оформляет участок в свою собственность.
8) Теперь уже собственник участка или сносит дешевый дом, или переоформляет его в баню/сарай/гараж, или делает реконструкцию. Многие продают такие участки по рыночной цене и хорошо зарабатывают на такой схеме.
Однако сейчас за деятельностью кадастровых инженеров стали лучше следить, штрафовать и лишать лицензии. Поэтому они редко соглашаются на подобные аферы. Ужесточение коснулось и администрации.
Также, внесли изменения в законодательство — С марта 2020 года для оформления жилого дома в собственность, нужно будет получить от администрации разрешение на ввод в эксплуатацию — п. 4 ст. 8 Федерального закона № 191-ФЗ. Следовательно дом будет осматривать комиссия, чтобы признать его пригодным для проживания. Иногда комиссия из администрации осматривают построенные дома уже на выкупленных участках. В итоге, подать иск в суд о признании дома непригодным для проживания и изъятия участка. Такая ситуация была в Свердловской области — Мэрия подала в суд на екатеринбуржцев, которые купили землю на аукционе, чтобы построить на ней дома.
UPD — данный пункт спорный, есть еще п. 12. Ст 70 Федерального закона N 218, который вроде бы отменяет данные нормы. Спасибо @Bulkin80. #comment_274967206
Также, сейчас есть дополнительный нюанс (хотя здесь я могу откровенно ошибаться) — согласно ст.39.5 ЗК РФ
Выкуп земли возможен
после 5-ти лет использования арендованной земли по целевому назначению (ИЖС, ведения ЛПХ или занятия КФД);
после 5-ти лет использования объекта, выданного в аренду для ИЖС или ЛПХ лицам, занимающим определенные должности;
Также местная администрация вправе вносить дополнительные ограничения.
Так что судя по всему (я надеюсь), нынче провернуть схему с накручиванием цены за аренду выше стоимости участка уже не выгодно (я очень очень надеюсь) — придется пяток лет платить эту цену (опять же, если я не ошибаюсь, я все-таки не юрист). Ну, по крайней мере я на это очень сильно надеюсь (я очень очень очень надеюсь).
На этом все. Спасибо всем!
Когда умеешь работать с инструментом
Полезный совет при травле тараканов
При травле тараканов многие пользуются противотараканными препаратами в виде пасты или крема. Наносить его надо в места появления насекомых. Но вот потом отмывать его не всегда удобно или что ещё хуже состав портит поверхность.
Вот такой выход предлагаю. Наклейте широкий малярный скотч в места где будете использовать препарат. И наносите отраву вкусняшку для тараканов на него. Через несколько дней скотч легко снимается и отмывать ничего не надо.
Как почистить бутылку изнутри
Поджаренный халапеньо
Купил баночку зелёного маринованного халапеньо. По остроте — нормальный, вкус приятный. Но вот жёстким каким-то он мне показался. Красный значительно мягче. Поэтому прозябал он у меня в углу холодильника почти месяц, пока все остальные перцы не закончились.
На безрыбье и рак рыба, поэтому баночка была извлечена на свет божий. Попробовал снова — нет, не нравится мне эта жёстковатость. Кинул его на сковородку в раскалённое растительное масло и пожарил пару минут. Консистенция стала мягче, острота никуда не делась.
А затем я на этом же масле разогрел консервированную фасоль. Получилось очень приятно.
С юбилеем ! Сегодня ровно 45’000 дней с момента начала исчисления дат по версии Microsoft Excel
Все версии Excel для Windows вычисляют даты на основе «системы дат 1900».
Отсчёт ведётся от 0 января 1900 года 00 часов 00 минут.
Если в ячейку Excel вы введёте 0 и выберете формат ячейки «длинный формат даты», то увидите там именно это 🙂
Но и это ещё не всё 🙂
Если из сегодняшней даты, 15.03.2023, вычесть «абсолютный» 0, то вы получите ровно 45’000 дней.
С юбилеем вас, товарищи 🙂
P.S. Официальная справка Microsoft гласит: «В системе дат 1900 даты вычисляются с использованием 1 января 1900 г. в качестве отправной точки. При вводе даты она преобразуется в порядковый номер, который представляет количество дней, за которое прошло с 1 января 1900 г.», но ЗА НОЛЬ принимается именно 0 января 1900 🙂
P.P.S. А узнал я это благодаря табличке по оплате ипотеки, которую веду в Excel. Удалив дату увидел что платить мне через -45’000 дней, но к сожалению платить мне ещё долго 🙂
Полезные трюки при работе в Excel
Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.
Сомнения перед написанием
Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.
Почему я посчитал, что это будет полезно
Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).
Небольшое пояснение
Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:
Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).
Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.
F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)
Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).
Представления
Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.
И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:
Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:
В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:
Кстати, так можно добавить на панель быстрого абсолютно любую команду.
Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.
ПРЕДУПРЕЖДЕНИЕ!
Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).
После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.
Два окна одной книги.
Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:
Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.
Специальная вставка (пропускать пустые ячейки)
Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:
Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:
Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!
Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.
P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.
Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube
Если б мишки были пчёлами… условия в Excel
Решил попробовать формат коротких постов по Excel
Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.
В общем виде выглядит так:
ЕСЛИ (условие; результат если условие верное; результат если условие не верное)
Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце
При множественных условиях иногда удобно применять функции И, и ИЛИ, которые позволяют соединять несколько условий
=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)
Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.
=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)
Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.
А на этом сегодня все, пойду дальше писать про макросы…
Ответ на пост «Как сохранить список запрещенных в России в файл?»
Бро, на данном сайте есть таблица.
вот тебе лайфхак
@1g0rbm ты круто всё описал и, уверен, твой способ рабочий,
но зачем усложнять когда, можно не усложнять и пойти попить кофеек?)
1 минута.
и пример из множества страниц в ответ на некоторые комментарии
ссылка на пост
Какая дата будет через месяц
Рассчитать, какая дата наступит через месяц (или несколько месяцев) можно функциями.
1 способ. Функция ДАТАМЕС (EDATA)
Аргументы этой функции такие:
ДАТАМЕС (Дата; Кол-во месяцев), где
Дата — это начальная дата,
Кол-во месяцев — это число месяцев, которые нужно добавить к дате или вычесть. Для добавления указывается положительное число, для вычитания — отрицательное.
ВАЖНО. При фактическом отсутствии дня в получившемся месяце будет получена предыдущая реально существующая дата.
Пример: Рассчитать дату отгрузки через месяц от даты заказа:
2 способ. Функция ДАТА (DATA)
Аргументы этой функции:
ДАТА(Год; Месяц; День), где
Год — год для даты, который может быть выражен числом или функцией ГОД от указанной даты,
Месяц — месяц для даты, который может быть выражен числом или функцией МЕСЯЦ от указанной даты плюс (минус) нужное количество месяцев,
ДЕНЬ — день для даты — число или функция ДЕНЬ от указанной даты.
ВАЖНО. При фактическом отсутствии дня в получившемся месяце будет получена следующая реально существующая дата.
Пример: Рассчитать дату отгрузки через месяц от даты заказа:
Получается, если нужной даты при расчете нет, функция ДАТАМЕС даст нам предыдущую дату, а функция ДАТА — следующую. Обратите внимание на это существенное отличие!
Бонус. Если дата нужна не просто существующая, но еще и рабочая!
Добавьте к нужной формуле функцию РАБДЕНЬ (WORKDAY) с одновременным добавлением и вычитанием одного дня:
РАБДЕНЬ(дата -1;1) — чтобы сработать в +день, или
РАБДЕНЬ(дата +1;-1) — чтобы сработать в -день.
Пример всех функций в одной таблице:
Быстрое суммирование в Excel
Диаграмма Ганта через условное форматирование
Допустим, у нас есть таблица с указанием сроков задач и шкала с датами в виде заголовков столбцов:
Надо закрашивать ячейки в столбцах с определенными датами, ориентируясь на даты начала и окончания задач. Для этого выполняем:
1) Выделяем весь внутренний диапазон в таблице, идем Главная — Условное форматирование — Создать правило.
2) Выбираем тип правила — Использовать формулу…
3) Заносим формулу: =И(D$2>=$B3;D$2<=$C3), выбираем цвет, жмем ОК:
Это очень простой способ для визуализации временных периодов. Можно также сделать диаграмму Ганта именно диаграммой, но там действий по настройке диаграммы будет немного побольше.
Классный шорткат в Excel — Очередной повод отказаться от мышки!
Очередная отличная комбинация клавиш в Excel:
Да что с тобой не так, Excel
Создаём динамический календарь в Excel с помощью формул и условного форматирования
Если Вам когда-либо приходилось делать в Excel календарь (возможно Вы уже даже успели для этого помучиться с формулами массивов), то, уверен, метод представленный в этом посте Вам определенно понравится.
В видео ниже показано, как можно легко создать полностью динамический календарь в Excel, основанный на простейших формулах ДЕНЬ, ДЕНЬНЕД И ТЕКСТ. Поскольку отдельные дни в подобном календаре представлены в формате дат, данный календарь можно легко расширять самым разнообразным функционалом (подсветка сегодняшней даты, выходных / праздничных дней и даже независимо заданных пользователем дат). В общем, предлагаю посмотреть туториал по созданию подобного календаря:
P.S. В ближайшие недели я планирую записать и выложить видео о том, как на основе данного календаря можно создать автоматических планировщик смен, чтобы отдельные смены сами высчитывались и отображались на календаре
Ответ на пост «Забудьте об объединении ячеек, ведь намного лучше поступать вот так!»
Excel надстройка Эфемерида, которая делает это нажатием одной кнопки:
в выделенных объединённых ячейках снимает объединение и выравнивает по центру.
Как удалить фон картинки в Excel / Word / PowerPoint – Просто!
Если вдруг нужно вырезать фон картинки, а под рукой нет Photoshop-а – не беда! Ведь эту задачу можно легко решить и непосредственно в Excel, Word-е, или же в PowerPoint-е.
Возьмем в качестве примера вот эту картинку:
Вырежем ей фон в Excel, и укажем вырезанным символом руки на показатель выручки в Берлине за июль вот в этом небольшом отчете:
Итак, переходим по пути команд: ВСТАВКА -> Рисунки -> Это устройство
Выбираем на компьютере нужную картинку и вставляем её на рабочий лист:
Вот и наша картинка:
Теперь выбираем её и во вкладке «Формат рисунка» щелкаем по кнопке «Удалить фон»:
Excel сразу пытается угадать, что нужно вырезать, и помечает эти части фиолетовым цветом. Так что теперь просто с помощью карандаша добавления (А) добавляем требуемые области в выбор, а с помощью карандаша удаления (Б) удаляем ненужное:
Оставив лишь требуемое, нажимаем на «Сохранить изменения» и получаем желаемый результат:
Ну и размещаем, как хотели, вырезанный указатель:
Вот и всё! Через правый щелчок вырезанную картинку можно легко сохранить в формате PNG (в этом формате прозрачные области картинки остаются таковыми и не заполняются белым цветом).
В этом посте пример приведен с простой картинкой. Тем не менее представленный инструмент вполне не плохо работает и с фотографиями, вот в этом видео я привел пример и советую его также посмотреть:
Забудьте об объединении ячеек, ведь намного лучше поступать вот так!
Точно уверен, что Вам часто приходилось понервничать с объединёнными ячейками!
Возьмём в качестве примера вот такую таблицу:
Выглядит, вроде, неплохо, вот только большим, на первый взгляд невидимым, минусом является то, что ячейки в диапазонах B2:E2 и B8:E8 объединены!
Поэтому если попробовать разом выбрать, например, все значения одного из месяцев, у нас непременно будет расширен выбор сразу на все столбцы таблички:
Наверняка Вы уже и сами пользовались подобным объединением ячеек – и я Вам хочу дать совет – постарайтесь больше этот инструмент не использовать, так как он может быть источником самых разных проблем — начиная от проблем с шорткатами, вплоть до сложностей при написании кода в VBA.
Но что же тогда использовать, спросите Вы, если вот понадобилось именно подобное центрирование содержимого по нескольким ячейкам? Для это намного лучше использовать центрирование содержимого по центру выделения. Разберёмся.
Для этого сперва отменяю объединение ячеек:
После этого выбираю все требуемые ячейки (я выбрал ячейки сразу как в строке 2, так и 8):
Теперь щелкаем по вот этому символу:
И в открывшемся окне в пункте «по горизонтали» выбираем настройку «по центру выделения».
Нажимаем на ОК и всё готово (на картинке ниже показано, что объединение ячеек не использовано)!
Теперь все выглядит как нам нужно было, и при этом и в функциональном плане всё работает как должно – вот могу спокойно выбирать нужные ячейки:
Всё описанное я также наглядно показал вот в этом видео, советую его посмотреть:
Ипотека – руководство по эксплуатации. Немного философствования, расчета и анализа. Разоблачение мифов
И не подумал бы писать подобное, но последние лет 5 вечерами по этой тематике случаются одни и те же споры с друзьями, и одни и те же вопросы. При том аргументами с их стороны являются кредитные калькуляторы и первые попавшиеся статьи из интернета. Смотрел я на эти статьи, и дико расстраивался. Потому что верить в это мой мозг отказывается. В основном запись для друзей, даже список рассылки сформировал. Заодно и интернет «разбавить». Широкому кругу тоже будет интересно. Если вы еще никогда не брали ипотеку, либо по какой-либо причине считаете ее «рабством» — вам сюда! Советы и критика от товарищей с опытом тоже приветствуется. Сразу скажу: я не имею экономического образования, а всего лишь скромный научный сотрудник. Иначе где-нибудь здесь интеграл применил. Напрашивается он здесь больно сильно. По возможности постараюсь разбавить нудность изложения.
Здесь есть постулаты для понимания. Если, прочитав их, вы с ними согласитесь – дальше статью можно не читать, а пролистать мельком до конца третьего пункта. Там изложены соображения о стратегии и целесообразности досрочного погашения.
Антимиф №1: между кредитами с аннуитетным и дифференцированным платежом по сути нет никакой разницы;
Антимиф №2: досрочные погашения с уменьшением срока кредита и с уменьшением суммы ежемесячного платежа выгодны одинаково;
Антимиф №3: при возможности досрочного погашения кредиты с разными сроками кредитования идентичны.
Если хотя бы по одному пункту не согласны – читаем дальше.
Между кредитами с аннуитетным и дифференцированным платежом по сути нет никакой разницы.
Пару дней назад я сам этого не знал этого факта. Всегда выбирал первый тип кредита, потому что он для меня более «прозрачный» и легкий для прогнозирования. Более того, я считал, что по кредиту с дифференцированным платежом переплата по процентам будет больше, чем по кредиту с аннуитетным. Кто-то утверждал обратное, приводя первую попавшуюся картинку из поиска. А конкретно эту:
От вида этого я ощутил скачек адреналина и прилив энтузиазма. На нем и пишу. А лучше бы новое лобовое стекло для машины искал, или хотя бы половину от полного бака летней солярки перед заморозками откатал.
Вот источник где четко сказано, что оформление кредита с аннуитетным платежом банку выгоднее.
Снова внимание на рисунок: очевидно, что объем переплаты по процентам (площадь оранжевой фигуры) во втором варианте больше.
Стоп! Почему на рисунке и погашение основного долга больше (площадь синей фигуры)? На подобных графиках воспринимать денежные суммы удобнее как площади фигур.
Т.е. автор статьи сравнивает два кредита с разной суммой займа. Либо с одной суммой, но построил графики в разных системах координат. А зачем. Непонятно!
Первую «ловушку» обошли, ограничившись визуальным анализом.
Копнем глубже: что там в расчетах?
Название третьего раздела вышеупомянутой статьи: «Почему выгоднее взять ипотеку с дифференцированными платежами: пример». Даже читать не стану, что они там насчитали. Открываю электронную таблицу и набрасываю вариант.
8 % — процентная ставка;
2 млн. — сумма кредита, по меркам мегаполиса мала, но для городов с населением до миллиона думаю самое оно. Если далее вам покажется, что суммы смешные и не стоят того – вспомните о среднем доходе по стране. И просто умножайте эту и все последующие денежные цифры в 2, 3, 4, 5 раз – примерно поймете ваш результат. Если нет — подскажу;
15 лет — срок кредитования, не отпугивает «рабством», а ежемесячный платеж при таком сроке еще приемлем.
Суммы экономии сейчас не очень значительны, но во времена ставок 12-18% были куда существеннее (к чему, думаю, в скором времени все и вернется).
Формулы расчета ежемесячных платежей по обоим видам кредитов упрощенные из Википедии. Суть и суммы от этого не меняются. При расчете платы по % при аннуитетном платеже использовал просто умножение остатка долга на 1/12 процентной ставки. Действительная формула иная, учитывает количество дней в месяце, но в далеком 2016 году я был младшим научным сотрудником и не считал нужным в это углубляться.
2021 год. И сейчас не считаю нужным. Взяв любой график аннуитетного платежа и посмотрев на столбец «плата по %» понимаешь, что за несущественной погрешностью так оно и есть – переплата в месяц по процентам равна 1/12 процентной ставки от остатка текущей задолженности. Годовая переплата та же. А ведь в годах бывает разное количество дней! Понятно, почему банки считают все по дням.
Собственно формулы: слева аннуитеный, справа дифференцированный.
Вбиваем все в таблицу. Начинаю отсюда и далее много текста. На самом деле все намного проще. Для составления нескольких подобных прогнозов и анализа достаточно 15-30 минут. Пока обратим внимание только на первые два массива данных. Первый массив — кредит с аннуитетным платежом. Переплата попроцентам составляет 1440348 рублей. Второй массив — кредит с дифференцированным платежом, переплата по % составляет 1206667 рублей. Выгода последнего около 240т.р.. О чем-то подобном возможно и писали в статье выше.
Посмотрим графики. Визуализация.
Разница с первым рисунком налицо.
В качестве исходных данных одинаковые сумма кредита, процентная ставка, срок кредитования.
Сплошная синяя линия – ежемесячный платеж по кредиту с аннуитетным платежом.
Сплошная оранжева линия – ежемесячные платежи по кредиту с дифференцированным платежом;
Пунктирная синяя линия – ежемесячные платежи по процентам кредита с аннуитетным платежом.
Пунктирная оранжевая линия – ежемесячные платежи по процентам кредита с дифференцированным платежом.
количество уплаченных процентов – это площадь фигуры, ограниченной линией платежей по процентам и осью времени;
количество выплат по основному долгу – это площадь фигуры между линиями ежемесячного платежа и платежа по процентам;
экономия на процентах – площадь фигуры между двумя линиями платежей по процентам (между синей пунктирной и оранжевой пунктирной).
Далее стоит воспринимать графики именно таким образом. Так намного проще все понять и оценить визуально, не вглядываясь в таблицы.
В чем здесь смысл. Часто слышу от друзей: «ты же по кредиту сначала проценты платишь, а потом только основной долг». Подобные фразы у меня вызывают непонимание. На самом деле так: проценты по кредиту ты платишь всегда и одну и ту же часть – каждый месяц 1/12 (точнее – 31/365, 30/365, 28/365 – зависит от месяца) процентной ставки от суммы остатка основного долга. Другой вопрос: какую сумму ты платишь в счет погашения основного долга? – либо по графику, либо ты можешь платить ту сумму, которую хочешь. Это стоит решать самому, что бы не морочить себе голову выбором кредитного продукта.
По сути кредит с дифференцированным платежом, по сравнению с аннуитетным, это некий вариант досрочного погашения с тем условием, что каждый месяц платишь одну и ту же сумму по основному долгу (взглянем на график – линии ежемесячного платежа и платежа по процентам идут параллельно).
В рассматриваемом варианте изначально дифференцированный платеж больше чем по аннуитеному, ближе к окончанию срока кредитования он падает ниже. То есть по сравнению с кредитом с аннуитетном платежом сначала вы переплачиваете, а потом (в данном случае через 96 месяцев) недоплачиваете по основному долгу.
Отвлекся от сути. Вернемся к цели раздела – все одно!
Возьмем тот же кредит с аннуитетным платежом, но каждый месяц будем вносить досрочно такую сумму, что бы ежемесячный платеж равнялся платежу по кредиту с дифференцированным платежом. Обратим внимание выше на третий массив данных «Аннуитетный с платежом по Дифф». Там добавлен столбец досрочного погашения «разница», где в каждой ячейке вбита формула разности между диффернцированным и ануитетным платежами. Куда ляжет график этой функции? Один в один с кредитом по дифференцированному платежу: черная сплошная линия – ежемесячный платеж, черная пунктирная линия – ежемесячная плата по процентам.
Собственно и переплата по процентам в третьем массиве данных «Аннуететный платеж по Дифференцированному платежу» один в один сходится с переплатой по процентам в первом массиве данных «Аннуитетный». Что и требовалось доказать.
На этом можно было бы остановиться, но я вспомнил, что когда-то формировал платеж из условия, что бы платеж по основному долгу был не менее первоначального платежа по процентам (считал неэтичным платить банку больше, чем себе) – по сути это тот же дифференцированный платеж, но с другой формулой расчета ежемесячного обязательного платежа. Назову это формулой расчета платежа по CraftyHorse. Шучу, оно не стоит того. Посмотрим, что бы из этого вышло. В последующих главах расчеты будут более гибкими.
Итак, четвертый массив данных и график (красные)
Какой график лучше?
Ежемесячный платеж на 2,2т.р. больше чем по дифференцированному с теми же условиями, но плюсом экономия по процентам около 200т.р., минус 2,5 года «рабства». Всего-то. 30 минут потрачено зря. Возможно, для кого-то это стоит того, для большинства думаю не стоит. Все равно интересно. Это сейчас кредитная благодать со ставкой до 8%. А вбивая в эту таблицу ставку 12% и выше, экономия повышается пропорционально.
Здесь есть еще один нюанс: во многих банках ограничена минимальная сумма досрочного погашения. Решается это следующим образом: разница накапливается несколько месяцев и потом вносится одним платежом. Естественно экономия уменьшается за счет «пилообразности» графика, но несущественно.
В сухом остатке совершенно не важно: кредит с аннуитетным платежом, дифференцированным, или со своим, потому что каждый месяц всегда платишь 1/12 процентной ставки остатка долга. Две формулы выше лишь способ организовать порядок выплаты основного долга. Можно поломать шаблоны и заняться формированием ежемесячного платежа самому.
Дочитав до сюда, мой друг сказал: ###ть ты заморочился! Я понял одно – если возьму ипотеку, я тебе все поручу считать.
Досрочные погашения с уменьшением срока кредита и с уменьшением суммы ежемесячного платежа выгодны одинаково.
Уже знакомый кредит (2 млн., 8%, 15 лет). Необходимо внести досрочный платеж. Например, ФНС перечислила налоговый вычет, и заемщик задумывается: внести досрочный платеж, сократив срок кредитования или уменьшив ежемесячный платеж. Допустим, годовой доход заемщика составляет 1млн.р., а первая половина налогового вычета поступает, скажем, через 7 месяцев после покупки квартиры, вторая половина соответственно через 19.
Собственно сам кредит – массив «Плановый». И два массива того же кредита с досрочным погашением – «Досрочный 1» и «Досрочный 2». В первом учитывается досрочное погашение с уменьшением срока кредитования, во втором – с уменьшением ежемесячного платежа.
Что имеем? При досрочном погашении с уменьшением срока кредитования экономия по процентам примерно на 250т.р. больше, чем при досрочном погашении с уменьшением суммы платежа, а срок выплат на 3 года меньше. Но, уменьшая сумму платежа, впоследствии платим меньше по основному долгу. Получается расчеты не эквивалентны. Уравниваем! Разницу платежей между вариантом 1 и вариантом 2 плюсуем к варианту 2 после «вброса» налогового вычета.
Столбцы «платеж фактический» у обоих кредитов совпали. Переплаты по процентам обоих вариантов досрочного погашения сошлись. Графики легли в одну линию. Второй антимиф доказан.
При возможности досрочного погашения кредиты с разными сроками кредитования идентичны.
Знакомые исходные данные, два кредита с аннуитетным платежом:
8 % — процентная ставка;
2 млн. – сумма кредита;
10 и 20 лет – сроки кредитов. Думаю самое оно. Платеж для срока кредитования 20 лет не намного больше, чем для срока кредитования 25 лет. А 10 лет по факту убирается в 5 лет. Вполне жизненные цифры.
Подставляем цифры, формируем массивы, строим графики.
Это все очевидно. Это именно то, что нам показывает кредитный калькулятор онлайн. Переплата по процентам кредита на 20 лет составляет 2014912 рублей. Переплата по процентам кредита на 10 лет составит 911862 рубля. Надо брать? – на самом деле разницы нет!
Небольшое отступление: смотря на эти цифры, я не вижу цифры. Равно как и на работе: огромные массивы данных, более 100 тысяч строк. Для кого-то это просто цифры, для меня это не так: в голове чёткая картина физических процессов и их результат. Можно сравнить с отрывком из фильма «Матрица»: Информации, получаемой из Матрицы, гораздо больше, чем ты можешь расшифровать. Ты привыкаешь к этому. Скоро твой мозг сам делает перевод. Я уже даже не вижу код. Я вижу блондинку, брюнетку и рыжую.
И со мной такое. Настоящему научному сотруднику нет разницы, что подвергать анализу: поток данных, описывающих физические процессы, или финансовые потоки. Глядя в свой массив данных, не этот, а в свой, я вижу не цифры, а их результат. Например, этот.
Есть и масса других вариантов.
Возвращаемся к сути. Разница платежей выше составляет около 7,5т.р.. Что если эту разницу досрочно вносить каждый месяц при 20 летнем сроке кредитования? Пробуем. Появляется третий массив данных «Аннуитетный с досрочным», идентичный второму и с тем же сроком кредитования 20 лет, но со столбцом «досрочн» — досрочное погашение, куда вбита разница платежей. Результат — переплата по процентам та же самая, что и при кредите на 10 лет.
График кредита на 20 лет с досрочным погашением разницей ежемесячных платежей лег аккуратно на график кредита сроком на 10 лет. Снова все правильно.
Печатая все это, завязался очередной бесполезный спор с другом:
— Зачем ты все это считаешь?
— Отвали. Допишу, и вместо этих вопросов будешь спрашивать только как пользоваться Excel.
— Нафига мне Excel? Есть кредитные калькуляторы!
— И что тебе кредитный калькулятор насчитает?
— Он посчитает какой кредит выгоднее.
— И на какой срок выгоднее при одинаковых платежах?
— При меньшем сроке переплата по процентам меньше.
— Бесполезно спорить, прибегнем к консультации третьих лиц. Звони Володе, у него два ипотечных кредита, задай вопрос: при досрочном погашении выгоднее долгосрочный или краткосрочный кредит?
Володя: зависит от стратегии гашения, обычно без разницы. Но лучше брать долгосрочный.
— Молодец, соображает. Можно исключать его из списка рассылки.
Почему долгосрочный? (громко сказано, 20 лет вполне хватит).
А вполне случаются моменты, когда платить нечем. За пять лет даже у меня несколько раз случались такие ситуации. При меньшем обязательном платеже меньше вероятность в тяжелые времена стать жертвой штрафных санкций банка. Но и что бы каждый месяц педантично платить сверх обязательного платежа, нужно иметь выдержку. Хотя и суммы платежей отличаются незначительно, 24266 против 16729, где взять лишние 7,5т.р. для досрочного погашения? Да где угодно. В конце концов, зарплаты иногда растут вместе с ростом цен. Если нет – с друзьями обычно происходит следующий монолог: А вот ты пиво часто пьешь? Сигареты куришь? Откажись от этого частично, и средства найдутся.
Здесь сразу вспоминается текст одной из песен группы Кирпичи, в текстах которой я ощущаю не только шлейф алкоголя, но и интеллект:
«И денег на пиво становится все больше походу
Что вы смеётесь? Пиво – знатная статья pасходов»
Прям в голове заиграла.
А одна из подруг узнав о моей задумке недавно добавила: а конфеты? — тоже знатная статья расходов!
Допустим, негде взять 7,5т.р., а есть только 2,5т.р.. Раз в месяц это мелочь.
Вбиваю в ячейку досрочного погашения 2500р. Получаю:
Ежемесячный досрочный платеж 2,5т.р. дал эффект в около 600т.р. (площадь фигуры между желтой пунктирной и черной пунктирной линиями). Срок кредитования сократился на 5 лет. Глядя на эти графики становится ясно, почему все в основном берут заем на 15 лет. Таких сравнительных графиков можно построить сколько угодно и выбрать из них оптимальный. И корректировать его на протяжении всего срока кредитования увеличивая эффективность. Еще один вариант – хочу и могу каждый месяц платить 30000р. вместо 16792р.. Таблицу приводить уже лишнее, только график и цифры. Имеем следующее:
Переплата по процентам около 654т.р., что втрое меньше первоначальных 2млн. И закрывается кредит через 7,5 лет. Уже интересно.
Назревает логический вопрос: зачем так заморачиваться?
Во-первых: не сильно то и заморачиваешься. При наличии начальных навыков пользования MS Excel это все делается довольно быстро. Даже в моей «бухгалтерии», где все вышеописанное выглядит немного сложнее и изобилует операторами И, ИЛИ, ЕСЛИ, что в конечном итоге значительно упрощает процесс. Всяко интереснее, чем рубиться в PS.
Во-вторых, нужно оценивать эффективность досрочного погашения в разные периоды. То что дает хороший эффект сейчас, через несколько лет может оказаться низкоэффективной финансовой нагрузкой.
Если в настоящий момент цель закрыть кредит у вас стоит на первом месте, а все остальное отложено на потом – можно «вбрасывать» все что есть и не усложнять себе жизнь расчетами. В остальных случаях стоит посчитать. По мне, так грань между шестизначной и семизначной цифрой слишком тонкая.
Определить целесообразность внесения большой суммы? Для начального периода кредита это можно понять из примеров выше. Для позднего — мой случай: можно закрыть вопрос прямо сейчас, но некомфортно остаться без средств ради экономии по процентам в 27т.р.. Лучше еще год поплачу и завершу ремонт пока цены на материалы и технику не взлетели до небес, либо новую машину куплю, либо дачу – мне эти вариант нравятся больше. Переплачиваю за обязательную страховку, скажете вы? Ну плюс еще 4т.р.. На позднем периоде стоит хорошо подумать: вносить большую сумму, либо потратить ее на что-либо поважней.
Сколько платить досрочно каждый месяц? Для начального периода кредита опять же это можно понять из примеров выше. Для позднего — снова мой пример: могу каждый месяц платить на 10 т.р. больше чем сейчас, но эффект до закрытия кредита от этого составит всего 4,2т.р. – оно того не стоит. Это вопрос комфорта и он индивидуален. Из-за досрочного погашения можно вплотную приблизить себя к прожиточному минимуму, а можно гасить в месяц на 5-15т.р. меньше без значительного снижения эффективности, и чувствовать себя намного комфортнее.
Довольно распространенная ситуация: взяв квартиру в ипотеку, многих посещает желание продать какую-либо недвижимость или автомобиль, чтобы снизить платеж и переплату по процентам. В случае с автомобилем — прежде чем продавать его в наше время, нужно трижды подумать («живой» потом днем с огнем не сыщешь, а дилеры вкрай обнаглели). Можно посчитать эквивалентную единовременному гашению стоимостью автомобиля доплату к ежемесячному платежу, с которой вполне можно смириться, но она даст тот же или чуть меньший эффект. Если все же решили продать какой-либо актив — определить стоимость и срок продажи. Например, когда продажа по верху рынка в течение полугода дает меньший эффект, чем продажа по середине и низу рынка в течение месяца. Все описанное определяется мгновенно посредством изменения значений в нескольких ячейках. Остается принять решение.
Расчет в моем случае – с учетом переплаты по процентам и стоимостью ремонта выйти на стоимость квартиры ниже рыночной на определенную сумму. Если вы занимаетесь покупкой недвижимости с целью перепродажи – самое оно.
Игорь Рыбаков в одном из видео говорил, как я понял (не смог это смотреть), что ипотека и кредиты это рабство, куда не стоит влезать. Не согласился. Посмотрел комментарии — понял, что не я один. Хотя он и прав, ужаса в этом нет. Можно обыграть ситуацию так, что это будет выгодно обеим сторонам. Хотя это еще вопрос.. откуда мне знать во сколько на самом деле обходится банку обслуживание моего кредита (аренда офисов, зарплаты сотрудников, бонусы, золотые парашюты и возможно золотые умывальники тоже)?
Все описанное выше справедливо и для потребительских кредитов. Конечно, расчетные суммы снижаются из-за маленьких сроков кредитования, но на коротком отрезке времени они становятся более значимыми, и процентные ставки там покруче – есть над чем подумать.
Большинство конечно же не полезет в такие дебри как я, но, осознав суть, начнет методично вкидывать по чуть-чуть сверх первоначального платежа каждый месяц. Чем раньше – тем эффективнее (на эту тему стоило бы добавить отдельный график, что бы легче понять почему, но я исчерпал лимит в 25 изображений.. кто после первого раздела не заглядывая в таблицы схватывал суммы налету с графиков — поймет о чем я). Герман Греф возможно теперь потеряет немного прибыли. Хотя, чем быстрее люди закрывают текущий, тем быстрее может возникать желание взять новый кредит.
Если у вас нет ипотеки и, прочтя статью целиком, вы не разобрались, то, как говорил один из моих преподавателей: «Ничего страшного. Главное знать, что существуют подобные способы и методы и, когда от этого будет зависеть ваш доход, вы сразу вспомните и очень быстро во всем разберетесь».
Возможно когда-нибудь сделаю универсальный файл для составления прогнозов с подробным описанием. А лучше сразу не игру-убийцу времени, а приложение с рекламой. Где разработчики? Excel не всем под силу.