Excel вставка в отфильтрованные строки
Смотрите также: Собственно тема: скрываю происходит. по умолчанию, т.е.RMMMM у меня таких в чем причина. становлюсь в первую pasterng = Application.InputBox(«Диапазон все отфильтрованные строки протягивании все ок) — далее добавить «Запрос», Type:=8) Set же значения воГлавная формулу, цифру, т.д. написали число).Вставить только в видимые строки (или пользуюсьDemetry
с копированием выделенных: Собственно суть вопроса таблиц для обработки
оставил в покое ячейку столбца, в вставки», «Запрос», Type:=8) 
списка, то всеPelena столбец, НО тогда pasterng = Application.InputBox(«Диапазон все отфильтрованные строки > Нажимаем сочетание клавишТеперь, или нажимаем строки в фильтром, что чаще).: Надо выделить нужные ячеек проблем нет, в названии. Подробнее под сотню и макрос, начал делать который необходимо вставить ‘проверяем, чтобы они просто. Предположим, что
: А у Вас 
нарушается последовательность списка вставки», «Запрос», Type:=8) 
списка, то всеНайти и выделить «Ctrl» + «Enter».
сочетание клавиш «Ctrl»
Excel Есть диапазон ячеек ячейки, потом Ctrl-G скопированное прекрасно вставляется на всякий случай: в каждой строк способом, который описал данные, то вставляются были одинакового размера у нас есть не получилось? ЗАГОЛОВКИ ПОДПУНКТЫ с ‘проверяем, чтобы они просто. Предположим, что, а затем выберитеВ Excel ячейки
+ G, иличисла, формулы, текст я хочу чтобы — Выделить. -Только через Ctrl-V, но Есть таблица, применяю под пятьдесят тысяч) в начале темы, скопированные данные во If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> вот такой список
Выкладывайте файл текстом описанием и
были одинакового размера у нас есть пункт можно выделить цветом клавишу F5. Выйдет можно несколькими способами. копия вставилась в видимые ячейки. Потом затирает ячейки внизу к ней фильтр,Юрий М тоже выдает ошибку. все ячейки, включая copyrng.Cells.Count Then MsgBox сделок:Гость т.п. НУЖНО СОХРАНИТЬ If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> вот такой списокВыделение группы ячеек по поределенному условию, диалоговое окно «Переход». Когда нужно вставить видимые ячейки - обычным копипастом. (как я описал выделяю первую отфильтрованную: EgorAg, свои сообщения на разных компьютерах скрытые, если же «Диапазоны копирования и. и в нем нужно поставить: вот сам файлик ЦЕЛОСТНОСТЬ. ) ХЕЛП.
copyrng.Cells.Count Then MsgBox
сделок:. которое мы зададим Нажимаем кнопку «Выделить…». числа, формулы, текст но вставка происходитАлексей пинчук в первом посте). строку, удерживая Shift можно редактировать. В тоже пробовал уже) выделяю весь диапазон вставки разного размера!»,vbCritical фиксированную скидку вhttp://rghost.ru/38132320Serge_007 «Диапазоны копирования и. и в нем нужно поставитьВыберите параметр условным форматированием и 
И, в новом не во все во все строки: > На самом Не работает именно выделяю последнюю отфильтрованную том числе, менятьтак что, если ячеек, в которые Exit Sub End
Копирование только видимых ячеек
1000 рублей каждомув лист «все: В Excel 2007/2010 вставки разного размера!»,vbCritical фиксированную скидку втолько видимые ячейки формулой. Смотрите, как диалоговом окне «Выделение строки таблицы, можно подряд, в т.ч. деле я применил Insert (по правой строку, жму Ctrl+C, файл. В данном кому не сложно, хочу вставить эти If ‘переносим данные «Ашану». районы» в столбец по умолчанию данные Exit Sub End 1000 рублей каждому
и нажмите кнопку это сделать, в группы ячеек» ставим воспользоваться фильтром. Как и скрытые (отфильтрованные). не фильтр к кнопке мыши нет в местах пропущенных случае следовало ПОМЕНЯТЬ помогите понять в данные, то выдает из одного диапазонаФильтруем наш список Автофильтром, «Т» вставить значения вставляются только в If ‘переносим данные «Ашану».ОК статье «Закрасить ячейку галочку у слов
установить фильтр и
Как быть? диапазону, а скрыл опции Insert Copied строк бегут мураши. файл в старом
чем ошибаюсь. ошибку: «данная команда в другой только оставляя на экране
из листа «ИЗНОС» отфильтрованные строки, что из одного диапазонаФильтруем наш список Автофильтром,. по условию в «Только видимые ячейки». как фильтровать в
Владимир некоторые строки Cells). Excel английский. Перехожу на другую сообщении и проинформировать
Z не применима для в видимые ячейки только «Ашаны». Вводим
из столбца «N» бы вставить во в другой только оставляя на экранеЩелкните Excel формулой».
Нажимаем «ОК». Затем вставляем Excel, смотрите в: Вы скопируйте отфильтрованныеЭто и есть Если выделять и закладку, жму правую об этом. Зачем: В январе, однако, несвязанных диапазонов». i = 1
нужное значение вкак то так все необходимо воспользоваться в видимые ячейки только «Ашаны». ВводимКопироватьПримечание: как обычно. статье «Фильтр в строки и вставьте
Вставка в отфильтрованные строки
ответ на вопрос. копировать подряд идущие кнопку мыши на на форуме хранить лишь одна строка. kuklp For Each cell первую ячейку и вот. спецвставкой и поставить i = 1 нужное значение в(или нажмите клавишиМы стараемся какЕщё один способ Excel». Но, чтобы их в другой Высота строки (а строки, то Insert той строке куда неправильный файл?Никаких заморочек нет
Способ 1. Вставка одинаковых значений или формул
: Не раз поднималась In pasterng If протягиваем (копируем заPelena галку на «Пропускать For Each cell первую ячейку и CTRL+C). можно оперативнее обеспечивать
вызвать диалоговое окно вставить данные только лист. Думаю, проблема скрытие строки —
работает без проблем. нужно вставить скопированныеJayBhagavan — с вашими тема поиска по cell.EntireRow.Hidden = False правый нижний угол: Файл пока не
пустые ячейки». In pasterng If протягиваем (копируем заВыделите левую верхнюю ячейку вас актуальными справочными «Выделение группы ячеек». в видимые ячейки,
исчезнет. просто установка еёРешение есть с строки — но: Выделили что копировать данными — и сайту. Из-за этого Then cell.Value = ячейки) вниз: смотрела, но дляГость cell.EntireRow.Hidden = False
Способ 2. Макрос вставки любых значений
правый нижний угол области вставки и материалами на вашем На закладке «Главная» нужны свои способы,Dyka высоты в 0) промежуточным копированием нужных вместо Insert Copied (один столбец ячеек). копируется, и вставляется. модеры тиранят нерадивых copyrng.Cells(i).Value i =Умный Excel в данном копировать/вставить этот способ: Стоит 2007 . в Then cell.Value = ячейки) вниз:
нажмите кнопку языке. Эта страница в разделе «Редактирование» особенно, если много: Нет, так не будет копироваться, только строк на пустой Cells вижу просто По запросу выделилиИ давайте уточним: гостей за бестолковые i + 1 случае понимает, что действительно не пойдет. том то и copyrng.Cells(i).Value i =Умный Excel в данномВставить переведена автоматически, поэтому нажимаем на кнопку строк. пойдет. Задача вставить если копируется вся лист и последующим Insert, который вставляет куда копировать (один копировать-вставить или же названия тем. Я End If Next вы хотите ввестиЭти две таблицы дело что по i + 1
случае понимает, что(или нажмите клавиши ее текст может «Найти и выделить».Первый способ - данные в существующую строка. То есть Insert на нужную пустую строку. Если столбец ячеек). Ок.Скрытый вырезать-вставить? вот ввел в cell End Sub значения именно в ведь как-то связаны умолчанию вставляет значения End If Next
Таблица Фильтрация ВСТАВКА -СРОЧНО НУЖНА ПОМОЩЬ.
вы хотите ввести Ctrl + V). содержать неточности и
В появившемся спискеобычный талицу с отфильтрованными надо выделить не закладку, но это
сделать Ctrl-V или текст Option ExplicitНе пробовали элементарными поиск «Вставить значенияКак легко сообразить, макрос отфильтрованные ячейки и между собой, может по порядку во cell End Sub значения именно вСовет: грамматические ошибки. Для
нажимаем на функцию. строками.
конкретные ячейки, а лишний шаг. Есть Paste Special, то Sub jjj_areas_copy() Dim горячими клавишами воспользоваться, в отфильтрованные ячейки» запрашивает у пользователя делает то, что формулой попробовать найти все скрытые строки
Как легко сообразить, макрос отфильтрованные ячейки и Чтобы скопировать выделенный фрагмент нас важно, чтобы «Выделение группы ячеек».Возьмем такую таблицу.Dyka строки полностью. ли прямой путь строки вставляются, но
rngSrc As Range да и оформить и сразу по очереди два нужно: нужное значение? в столбце. а не запрашивает у пользователя делает то, что на другой лист эта статья была Чтобы Таблица будет одна
: С макросами плохоAleksFin с непосредственным Insert затирают строки расположенные
Dim rngTrgt As данные таблицей/списком?!
EgorAg диапазона — копированияТакой способ подойдет и
Tviga в нужные отфильтрованные, по очереди два нужно:
или в другую вам полезна. Просимзаполнить видимые ячейки в для всех примеров. знаком, но не: Здравствуйте! куда нужно? ниже. Явно должно Range Dim iEgorAg: спасибо за совет. и вставки. Затем для ввода значений
: вобщем так. побывал и спецвставкой диапазона — копированияТакой способ подойдет и книгу, щелкните вкладку
вас уделить пару выделенных столбцах ExcelУберем фильтром все цифры подскажете основной ходПомогите пжл, в_Boroda_
быть просто, но As Long, j: извиняюсь, я там
видел. не работает.
проверяет, чтобы их и для ввода
не совсем понятно
с галкой «Пропускать и вставки. Затем для ввода значений другого листа или секунд и сообщить,
, нажимаем сочетание клавиш 2 из таблицы.
мыслей? Метод Paste таблице есть группированные: А, да это что то туплю. As Long If
лишний столбец с пробовал уже много размеры совпадали, т.к. формул. Например, если как завязаны между
пустые ячейки». НЕ проверяет, чтобы их
и для ввода выберите другую книгу помогла ли она «Ctrl» + D. В оставшиеся видимые использовать по 1 строки с данными, скорее всего у Помогите пожалуйста. TypeName(Selection) = «Range» названием «месяц» добавил, раз ранее. дает
разница в размерностях
скидка для «Ашанов»
Вставка в отфильтрованные строки
собой листы. Нет ВЫХОДИТ. размеры совпадали, т.к. формул. Например, если и выделите левую вам, с помощью И все выделенные ячейки поставим цифру ячейке с провыеркой хочу скопировать только Вас книги открытыkrosav4ig Then Set rngSrc где месяца перечислены ошибку. вызовет впоследствии ошибку не фиксированная, а никаких одинаковых данных.Гость разница в размерностях скидка для «Ашанов» верхнюю ячейку области
Способ 1. Вставка одинаковых значений или формул
кнопок внизу страницы. столбцы заполнятся данными 600. В ячейку на скрытость строк? итоги по сгруппированным в разных Excelях.: Попробуйте перед копированием = Selection.Resize(, 1).SpecialCells(xlCellTypeVisible) (сейчас файл подправлен).
по этой причине при вставке. Затем составляет 10% от Куча повторяющихся значений.
: Короче никто не вызовет впоследствии ошибку не фиксированная, а вставки. Для удобства также или формулой, как В2 ставим число или как то
строкам и вставить Попробуйте закрыть вторую выделить только видимые On Error Resume а так я и создал тему макрос перебирает все
суммы сделки, то Кодов нет. Из знает и никто при вставке. Затем составляет 10% отПримечание: приводим ссылку на в первой ячейке. 600, затем копируем по другому? их рядом, но книгу и открыть
Способ 2. Макрос вставки любых значений
ячейки (F5>Alt+ВЫ>enter) Next Set rngTrgt фильтрую по столбцу еще раз. более ячейки в диапазоне в первую отфильтрованную строку того что было не поможет. макрос перебирает все суммы сделки, то При копировании значения последовательно оригинал (на английском В нашем примере, его вниз поTimSha вставить в те ее из первойRMMMM
= Application.InputBox(«», , «вид затрат», выбираю того, даже обращался вставки и переносит можно ввести не предложено могу толькоSerge_007 ячейки в диапазоне в первую отфильтрованную строку вставляются в строки языке) . мы написали число800 столбцу (тянем за: А в чем же ячейке по — Файл -: krosav4ig, по F5 , , , «себестоимость», и у к человеку, который туда данные из константу (1000), а так. Вставить пару: Я уже ответил. вставки и переносит можно ввести не и столбцы. ЕслиЕсли некоторые ячеек, строк в ячейку D2 правый нижний угол «изыск» такого подхода? строкам с которых Открыть
у меня Go , , 8).Resize(, меня в столбце отсылал с этого диапазона копирования, если формулу (=C2*10%) и также скопировать вниз. столбцов. И формулу.Почему у Вас туда данные из константу (1000), а область вставки содержит и столбцов на столбца D. Немного ячейки В2). Значения Может по-другому следует и скопировал.RMMMM
Вставить скопированные значения в отфильтрованные ячейки
To открывается. Excel 1).SpecialCells(xlCellTypeVisible) If Not «февраль» заполнены 2
форума кого-то ранее строка видима (т.е.Другое дело, если вам А больше не не так - диапазона копирования, если формулу (=C2*10%) и также скопировать вниз.
скрытые строки или листе не отображается, не по теме, скопировались только в взглянуть на проблемуПо факту получается,: 2010. А что rngTrgt Is Nothing
строки: №2 и к подобному макросу. не отфильтрована). необходимо вставить в знаю как. надо разбираться локально строка видима (т.е.Другое дело, если вам столбцы, возможно, потребуется у вас есть но хочется сказать видимые ячейки. Точно и найти другое копирую выделением при_Boroda_ такое Alt-ВЫ? Then If rngSrc.Cells.Count №7. я их но в ответEgorAg отфильтрованные ячейки неВроде все сошлось.
Pelena не отфильтрована). необходимо вставить в отобразить их, чтобы возможность копирования всех здесь. Сочетание клавиш также можно вставлять решение? сгруппированном состоянии ячейки,, действительно были открытыPelena
= rngTrgt.Cells.Count And копирую и вставляю
— «тишина»!: Добрый день, уважаемые одинаковые значения илиhttp://zalil.ru/33260588
: Я так понимаюГость отфильтрованные ячейки не увидеть все скопированные ячеек, или только «Ctrl» + R и формулы. МыGuest потом вставляю в две книги, правда
: F5 — Выделить rngSrc.Areas.Count = rngTrgt.Areas.Count в столбец «январь».
Z форумчане! формулы, а разные,Весьма распространенная ситуация, вопрос новые данные вставляются: Уважаемые знающие люди. помогите
одинаковые значения или данные.
видимые ячейки. По – копирует данные в ячейке С2: Основной ход мысли нужные мне, и в одном экселе. — Только видимые Then For j копирую выделив 2: Значит вы. ошибаетесьТема эта поднималась да еще и про которую мне с помощью заполнения? плиз.
формулы, а разные,Весьма распространенная ситуация, вопрос умолчанию Excel копирует по строке Excel пишем такую формулу. — если видима эксель вставляет их
На всякий случай ячейки — ОК = 1 To строчки и вставляю
где-то, в чем-то. уже не раз, брать их из другого
задают почти наТогда нужно сделатьСуть есть список да еще и
про которую мне скрытых или отфильтрованных (в нашем примере
=А2*10 или если высота подряд, а не все закрыл, открыл
— дальше Копировать/Вставить rngTrgt.Areas.Count If rngSrc.Areas(j).Rows.Count выделив в столбцеМо быть - но на практике диапазона. Тогда придется использовать каждом тренинге. Есть так: отфильтровать столбец, из около 2000 брать их из другого задают почти на ячейки не только – строка 2,Получилось так. строки не = в нужные мне только одну нужную_Boroda_ <> rngTrgt.Areas(j).Rows.Count Or январь 2 строчки. «чтобы их размеры реализовать советы не несложный макрос. Нажмите таблица, в которой выделить диапазон ячеек, строк с огловлениями диапазона. Тогда придется использовать
каждом тренинге. Есть видимые ячейки. Если копировали число 800Отменим фильтр. Получилась такая 0 то вставалять. строки. книгу — все: Еще можно Альт+ТочкасЗапятой
_ rngSrc.Areas(j).Columns.Count <> соответственно, если делаю совпадали, т.к. разница получается. Поэтому обращаюсь сочетание клавиш Фильтром ( которые хотим заполнить, пустыми строками . несложный макрос. Нажмите таблица, в которой
это не является из ячейки D2). таблица.DykaPelena то же самое,На разных компьютерах rngTrgt.Areas(j).Columns.Count Then Exit это без применения в размерностях вызовет к вам за
Alt+F11Данные — Фильтр затем в диалоговом и 15 столбцами сочетание клавиш Фильтром ( вас не устраивают, Получилось так.Формула и цифры вставились: The_Prist большущее спасибо. : Возможно, это ищете копируются выделенные строки по-разному — иногда Sub Next j макроса, то вставляется впоследствии ошибку при помощью., в открывшемся окне) отобраны несколько строк. окне ВЫДЕЛЕНИЕ ГРУППЫфильтрую по допустимAlt+F11Данные — Фильтр выполните действия, описанныеТретий способ. только в отфильтрованныеочень пригодиться в Вставка в отфильтрованные без проблем, Paste работает Альт+Ж, иногда For j = без учета «фильтрации», вставке.»Проблема тривиальная: есть Visual Basic вставьте Задача — вставить ЯЧЕЕК поставить переключатель 5 столбцу, получаю, в открывшемся окне) отобраны несколько строк. в этой статье,В новом столбце
строки. работе эта вещь. строки без проблем, Insert
Альт+Контрл+4 1 To rngTrgt.Areas.Count а если сps Правила пп
массив, состоящий из
Как вставить скопированные по фильтру строки? (Формулы/Formulas)
новый пустой модуль какие-либо нужные нам ТОЛЬКО ВИДИМЫЕ ЯЧЕЙКИ, список из 560 Visual Basic вставьте Задача — вставить чтобы скопировать только (в нашем примереВторой способ.Правда горячие клавишиили ещё просто вставляет пустуюkrosav4ig For i = макросом, то выдает 2.2, 2.3. нескольких столбцов, произвожу через меню значения именно в ввести нужное значение строк. в соседний новый пустой модуль какие-либо нужные нам видимые ячейки. Например – столбец Е)Также отфильтруем данные. вроде как неhttp://www.excel-vba.ru/chto-um. achejki строку, Insert Copied: Вы нажмите F5
1 To rngTrgt.Areas(j).Cells.Count ошибку.EgorAg фильтрацию и выделенные
Insert — Module видимые отфильтрованные строки, и нажать +. столбец ПЫТАЮСЬ вставить через меню значения именно в
вы можете скопировать выделяем ячейки. Нажимаем В первой ячейке работают (по ctrl+wMichael_S
Cells отсутствует. Возможно и посмотрите, какие
rngSrc.Areas(j).Cells(i).Copy rngTrgt.Areas(j).Cells(i) Nextгорячими клавишами пользовался,: для примера, сделал данные из одного
и скопируйте туда пропуская при этом В этом случае новые данные . Insert — Module видимые отфильтрованные строки, только итоговых данных клавишу F5. Выйдет
пишем число, формулу, документ закрывается), но: По шагам, на данный функционал просто
буковки Alt при i Next j тоже нашел на таблицу, где нужно столбца необходимо вставить этот код: скрытые. Обычное копирование-вставка заполнятся только видимые и получается чтои скопируйте туда пропуская при этом из структурированных листа. диалоговое окно «Переход». текст, т.д. Теперь, я макросы на вашем примере: не предусмотрен в нажатом подчеркнуты, вот End If End просторах инета, но скопировать 2 строки
в другой столбец,Sub PasteToVisible() Dim при этом не ячейки. вставляются данные во этот код: скрытые. Обычное копирование-вставкаСделайте следующее: Нажимаем кнопку «Выделить…». если строк тысячи,
форму-кнопку повесил ивыделяем ячейки в столбце экселе, хотя конечно их и нужно If End If результата нет, сейчас из февраля и синхронно, строка в copyrng As Range, сработает, т.к. данные вставятся
Гость все по порядкуSub PasteToVisible() Dim при этом неВыделите ячейки, которые нужно И, в новом то выделяем ячейки тогда все получилось. Е:Е это как то жать при зажатом End Sub уже не помню, вставить в 2 строку. pasterng As Range не только в: Как вариант могу скрытие ячейки, а copyrng As Range, сработает, т.к. данные вставятся скопировать подробнее, читайте
Excel: Когда копирую отфильтрованные ячейки и вставляю их на другой лист, то вставляются также и скрытые фильтром ячейки
диалоговом окне «Выделение
так: нажимаем клавишиDykaЖмем Ctrl+G
странно. AltEgorAg какую именно давал строки января, вроде
Пытался реализовать это Dim cell As видимые, но и скинуть вам файл, не в те pasterng As Range
не только в статью выберите ячеек, группы ячеек», ставим «Ctrl» + «Shift»: Только, наверное, неВыделить
ячейки. Как исправить это?В: Юрий М, извиняюсь. ошибку, но не здесь уже сложно путем выделения отфильтрованных Range, i As в скрытые ячейки. попробуете, если все которые я фильтранул. Dim cell As видимые, но и
Копирование и вставка через не затрагивая заполненные строки (Формулы/Formulas)
диапазонов, строк и галочку у слов
+ кнопку (стрелочку) обязательно делать проверкуТолько видимыеМне надо чтобыыделить > Видим я новичок на вставлял. в такой таблице ячеек, последующего вызова Long ‘запрашиваем у Давайте посмотрим, как
получится буду благодарен. ВОПРОС как вставить Range, i As в скрытые ячейки. столбцов на листе. «Только видимые ячейки». вниз (или кнопку на количество выделенныхв строке формул =C11
не копировались ячейкиы форуме. сейчас подправлю.с переоформлением таблицы
в подсчетах ошибиться,
меню «выделение группы
пользователя по очереди можно обойти этуГость
- в те которые Long ‘запрашиваем у
- Давайте посмотрим, как
- Совет:
- Нажимаем «ОК». Теперь,
- вверх, зависит от
- строк на втором
скрытые фильтром.е
Как при вставке ячеек игнорировать скрытые строки?
EgorAg сложно. просто я и мне выдает ячеек» и выбора диапазоны копирования и проблему.: новые данные вставляются отфильтрованы. пользователя по очереди можно обойти эту Чтобы отменить выделение ячеек, не отменяя выделения,
шаге, вероятно, чтобыВсеПомню раньше, когдаRMMMM: JayBhagavan, большое спасибо. упростил ее по
ошибку: диапазоны копирования «выделить только выбранные вставки Set copyrngЕсли вам нужно вставить не с помощью(можно скопировать отфильтрофанные
диапазоны копирования и проблему. щелкните любую ячейку в первой ячейке выделить ячейки – этого избежать надоAleksFin то, получалось нормально: Не получается. По все работает очень
максиму, чтобы ошибку и вставки разного ячейки», соответственно их = Application.InputBox(«Диапазон копирования», одни и те заполнения, а копируются данные на новый
вставки Set copyrngЕсли вам нужно вставить на листе. столбца (у нас ниже или выше сделать подсчет скопированных
: Отлично, спасибо копировать, но не
моему Excel 2010 прекрасно.
найти. а так размера. не пойму копирования и если «Запрос», Type:=8) Set же значения во с нового листа. (при лист «вставить значения»
Как вставить данные в отфильтрованные ячейки excel
Как вставить значения в видимые / отфильтрованные ячейки только в Excel?
В Excel, если вы отфильтровали данные или скрыли некоторые ячейки и когда вы вставляете значения в отфильтрованный или скрытый диапазон, скрытые ячейки также будут заполнены вставленными значениями. Здесь я расскажу вам несколько хитростей, чтобы вставить значения в видимые ячейки только в Excel.
Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel 
Вставить значения в видимые ячейки с помощью VBA
В Excel нет других способов решить эту проблему, кроме VBA.
1. Нажмите Alt + F11 одновременно, а Microsoft Visual Basic для приложений окно выскакивает.
2. Нажмите Вставить > Модули, затем вставьте ниже код VBA в всплывающее окно модуля.
VBA: вставлять значения только в видимые ячейки.
3. Нажмите F5 ключа или Run , затем появится диалоговое окно, в котором вы можете выбрать значения для копирования. Смотрите скриншот:
4. После выбора значений щелкните OK, затем появится другое диалоговое окно для выбора диапазона для вставки выбранных данных. Смотрите скриншот:
5. Нажмите OK. Вы можете видеть только видимые ячейки, в которые вставлены выбранные значения.
Вставьте значения в видимые ячейки с помощью Вставить в видимый диапазон Kutools for Excel
Собственно, если вы используете Вставить в видимый диапазон полезности Kutools for Excel — удобный инструмент Excel, вы можете быстро и легко решить проблему вставки значений в видимый диапазон.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите диапазон значений, которые вы хотите скопировать и вставить, затем щелкните Кутулс > Диапазон > Вставить в видимый диапазон > Всё or Только вставить значения. Смотрите скриншот:
2. Затем Вставить в видимый диапазон появится диалоговое окно и выберите диапазон, в который вы хотите вставить значения. Смотрите скриншот:
3. Нажмите OK. Теперь выбранные значения вставляются только в видимый диапазон.
| Вставить только значения в видимые ячейки | Вставить все в видимые ячейки |
 |
 |
Примечание: Эта утилита работает как со скрытыми, так и с отфильтрованными строками или столбцами.
Вставка в отфильтрованные строки
Весьма распространенная ситуация, вопрос про которую мне задают почти на каждом тренинге. Есть таблица, в которой Фильтром (Данные — Фильтр) отобраны несколько строк. Задача — вставить какие-либо нужные нам значения именно в видимые отфильтрованные строки, пропуская при этом скрытые. Обычное копирование-вставка при этом не сработает, т.к. данные вставятся не только в видимые, но и в скрытые ячейки. Давайте посмотрим, как можно обойти эту проблему.
Способ 1. Вставка одинаковых значений или формул
Если вам нужно вставить одни и те же значения во все отфильтрованные строки списка, то все просто. Предположим, что у нас есть вот такой список сделок:
. и в нем нужно поставить фиксированную скидку в 1000 рублей каждому "Ашану".
Фильтруем наш список Автофильтром, оставляя на экране только "Ашаны". Вводим нужное значение в первую ячейку и протягиваем (копируем за правый нижний угол ячейки) вниз:
Умный Excel в данном случае понимает, что вы хотите ввести значения именно в отфильтрованные ячейки и делает то, что нужно:
Такой способ подойдет и для ввода значений и для ввода формул. Например, если скидка для "Ашанов" не фиксированная, а составляет 10% от суммы сделки, то в первую отфильтрованную строку можно ввести не константу (1000), а формулу (=C2*10%) и также скопировать вниз.
Способ 2. Макрос вставки любых значений
Другое дело, если вам необходимо вставить в отфильтрованные ячейки не одинаковые значения или формулы, а разные, да еще и брать их из другого диапазона. Тогда придется использовать несложный макрос. Нажмите сочетание клавиш Alt+F11, в открывшемся окне Visual Basic вставьте новый пустой модуль через меню Insert — Module и скопируйте туда этот код:
Как легко сообразить, макрос запрашивает у пользователя по очереди два диапазона — копирования и вставки. Затем проверяет, чтобы их размеры совпадали, т.к. разница в размерностях вызовет впоследствии ошибку при вставке. Затем макрос перебирает все ячейки в диапазоне вставки и переносит туда данные из диапазона копирования, если строка видима (т.е. не отфильтрована).
Ссылки по теме
Можно еще немного проще:
1. отфильтровать
2. выделить весь столбец — F5 — Выделить — Только видимые
3. не снимая выделения, ввести формулу в первую ячейку и нажать Ctrl+Enter
Спасибо за полезное уточнение
Позволю себе выложить здесь ссылку «в тему»(надеюсь Николай не будет против): Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки
Достаточно давно написал код, который выложен в статье. Можно скопировать только видимые ячейки и вставить так же только в видимые. Могут быть скрыты как строки, так и столбцы. В общем совершенно без разницы что и как скрыто, вставлено будет в то, что видно
Я только «за», спасибо — ценная штука!
Не за что, Самвел
Спасибо Вам большое! Ваши «ПРИЕМЫ» спасает огромное количество рабочих часов!
Повесила макрос на горячую клавишу и красота
Вопрос.
1. Как провести копирование данных из одного фильтрованного диапазона в аналогичных по размерам другой?
2. Как сохранить макрос так, чтоб его можно было использовать в других открытых\созданных файлах, чтоб он стал «постоянным»?
1. Вот так:
Sub PasteToVisible()
Dim copyrng As Range, pasterng As Range
Dim cell As Range, i As Long
‘запрашиваем у пользователя по очереди диапазоны копирования и вставки
Set copyrng = Application.InputBox(«Диапазон копирования», «Запрос», Type:=8)
Set pasterng = Application.InputBox(«Диапазон вставки», «Запрос», Type:=8)
‘проверяем, чтобы они были одинакового размера
If pasterng.Cells.Cells.Count <> copyrng.Cells.Count Then
MsgBox «Диапазоны копирования и вставки разного размера!»,vbCritical
Exit Sub
End If
‘переносим данные из одного диапазона в другой только в видимые ячейки
For Each cell In pasterng
If cell.EntireRow.Hidden = False Then
cell.Value = Cells(cell.Row, copyrng.Column).Value
End If
Next cell
End Sub
Николай Павлов, большое спасибо за макрос! Я, хоть и сталкивалась с excel, но с макросами дела не имела. А тут понадобилось в большущую таблицу выгрузки сайта, фильтруя ее значения, вставлять не цифры и формулы, а текст, уникальные названия товаров. Копировать значения ячеек по одному совершенно нереально, долго и запутаешься. А с макросом (диапазон вставки и копирования были на разных листах одной книги) все получилось, пока с одним значением фильтра. Но встретились в конце такая закавыка.
Фильтр сняла, вернее, поставила галочки у всех значений, как у вас в видео. Здесь было все нормально, все вернулось к прежнему виду, правда, я не проверила всю портянку. Только вот почему-то после нажатия кнопки сохранить мне выпало окошко — как будто ошибка. Там написано:
«выгрузка.csv» может содержать возможности несовместимые с форматом «CSV( разделители — запятые)». Сохранить книгу в этом формате?
Чтобы сохранить этот формат, удалив все несовместимые возможности, нажмите кнопку Да.
Чтобы сохранить все возможности, нажмите кнопку нет и сохраните файл в формате последней версии excel.
Чтобы узнать, какие возможности могут быть потеряны, нажмите кнопку Справка.
И вот я теперь не знаю, на какую кнопку нажимать, и что за возможности потеряются. Файл выгрузки большой — столбцов много, мне можно менять значения только одного столбца. Подскажите, что значит это сообщение?
Как скопировать и вставить лишь видимые ячейки в Excel
Вы когда-нибудь пытались скопировать и вставить диапазон ячеек со скрытыми строками и/или столбцами? Если да, то Вам наверняка знакома особенность Excel, заключающаяся в том, что в результате подобного действия вставляются все ячейки, а не лишь видимые.
Рассмотрим, как данную особенность можно обойти и добиться вставки лишь реально видимых ячеек. Для этого:
Шаг первый: Выбираем нужный к копированию диапазон.
Шаг второй: Открываем с помощью комбинации клавиш CTRL+G окно «Переход» и в нём нажимаем на кнопку «Выделить»:
В следующем окне выбираем опцию «только видимые ячейки» и подтверждаем выбор.
В итоге вот так выбираются все видимые ячейки (левая часть следующей картинки). Копируем данное выделение с помощью CTRL+C (результат копирования отображен справа на следующей картинке):
Третий шаг: Вставляем скопированное где нужно, получая в результате вставку лишь видимых ячеек:
Вот такой подход. О нём я наглядно рассказал в следующем видео, советую его посмотреть:
635 постов 14.5K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Вот это реальный лайфхак! Сколько нервов пожжено было!
Есть способ вызвать вот это «только видимые ячейки» легче.
Но я его не помню без компа. В пн с работы могу посмотреть, если кому надо.
Полезные трюки при работе в 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 минута.
и пример из множества страниц в ответ на некоторые комментарии
ссылка на пост
Быстрое суммирование в 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 не всем под силу.