Самый быстрый способ убрать формулу в Excel оставив значения

[@ГалинаБелоусова-и4щ]

Спасибо, Андрей, очень полезная информация, хочется во всём разобраться и применять, вдохновляет!

[@Palich_51]

Хотелось бы отдельный урок с примерами именно на макросы. Можете такой урок записать?

[@andreysukhov]

На счет макросов сейчас обдумываю целую серию видео.

[@yuriyfedoskin]

Блеск! Спасибо!!!

[@ТоктоназаровЖылдызбек]

Добрый вечер. Спасибо Андрей за информацию. Очень интересно. Можно сделать формулы чтобы сумма прописью выходил, я бы хотел Кыргызский сом и.д.

[@andreysukhov]

Да, возможно. Проще всего сделать это макросом, но можно и прописью. Записал себе вопрос и сделаю видео на эту тему.

[@RomanShagrov]

Можно даже формулой. Спасибы Билялу. =ВЫБОР(ЛЕВСИМВ(ТЕКСТ(H6;"000000000,00"))+1;;"Сто ";"Двести ";"Триста ";"Четыреста ";"Пятьсот ";"Шестьсот ";"Семьсот ";"Восемьсот ";"Девятьсот ") &ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");2;1)+1;;;"Двадцать ";"Тридцать ";"Сорок ";"Пятьдесят ";"Шестьдесят ";"Семьдесят ";"Восемьдесят ";"Девяносто ") &ЕСЛИ(--ПСТР(ТЕКСТ(H6;"000000000,00");2;1)1;ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");3;1)+1;;"Один ";"Два ";"Три ";"Четыре ";"Пять ";"Шесть ";"Семь ";"Восемь ";"Девять "); ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");3;1)+1;"Десять ";"Одиннадцать ";"Двенадцать ";"Тринадцать ";"Четырнадцать ";"Пятнадцать ";"Шестнадцать ";"Семнадцать ";"Восемнадцать ";"Девятнадцать ")) & ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="10";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="11";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="12";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="13";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="14";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="15";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="16";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="17";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="18";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="19";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="20";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="30";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="40";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="50";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="60";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="70";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="80";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="90")=ИСТИНА;"Миллионов ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="01";"Миллион ";ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="02";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="03";ПСТР(ТЕКСТ(H6;"000000000,00");2;2)="04")=ИСТИНА;"Миллиона ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");3;1)="1";"Миллион ";ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");3;1)="2";ПСТР(ТЕКСТ(H6;"000000000,00");3;1)="3";ПСТР(ТЕКСТ(H6;"000000000,00");3;1)="4")=ИСТИНА;"Миллиона ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");1;3)="000";"";"Миллионов ")))))) &ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");4;1)+1;;"Сто ";"Двести ";"Триста ";"Четыреста ";"Пятьсот ";"Шестьсот ";"Семьсот ";"Восемьсот ";"Девятьсот ") &ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");5;1)+1;;;"Двадцать ";"Тридцать ";"Сорок ";"Пятьдесят ";"Шестьдесят ";"Семьдесят ";"Восемьдесят ";"Девяносто ") &ЕСЛИ(--ПСТР(ТЕКСТ(H6;"000000000,00");5;1)1;ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");6;1)+1;;"Одна ";"Две ";"Три ";"Четыре ";"Пять ";"Шесть ";"Семь ";"Восемь ";"Девять ");ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");6;1)+1;"Десять ";"Одиннадцать ";"Двенадцать ";"Тринадцать ";"Четырнадцать ";"Пятнадцать ";"Шестнадцать ";"Семнадцать ";"Восемнадцать ";"Девятнадцать ")) & ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="10";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="11";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="12";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="13";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="14";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="15";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="16";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="17";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="18";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="19";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="20";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="30";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="40";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="50";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="60";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="70";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="80";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="90")=ИСТИНА;"Тысяч ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="01";"Тысяча ";ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="02";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="03";ПСТР(ТЕКСТ(H6;"000000000,00");5;2)="04")=ИСТИНА;"Тысячи ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");6;1)="1";"Тысяча ";ЕСЛИ(ИЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");6;1)="2";ПСТР(ТЕКСТ(H6;"000000000,00");6;1)="3";ПСТР(ТЕКСТ(H6;"000000000,00");6;1)="4")=ИСТИНА;"Тысячи ";ЕСЛИ(ПСТР(ТЕКСТ(H6;"000000000,00");4;3)="000";"";"Тысяч ")))))) &ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");7;1)+1;;"Сто ";"Двести ";"Триста ";"Четыреста ";"Пятьсот ";"Шестьсот ";"Семьсот ";"Восемьсот ";"Девятьсот ") & ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");8;1)+1;;;"Двадцать ";"Тридцать ";"Сорок ";"Пятьдесят ";"Шестьдесят ";"Семьдесят ";"Восемьдесят ";"Девяносто ") &ЕСЛИ(--ПСТР(ТЕКСТ(H6;"000000000,00");8;1)1;ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");9;1)+1;;"Один";"Два";"Три";"Четыре";"Пять";"Шесть";"Семь";"Восемь";"Девять");ВЫБОР(ПСТР(ТЕКСТ(H6;"000000000,00");9;1)+1;"Десять";"Одиннадцать";"Двенадцать";"Тринадцать";"Четырнадцать";"Пятнадцать";"Шестнадцать";"Семнадцать";"Восемнадцать";"Девятнадцать"))

[@Yegor312]

В Яндекс таблицах не нашел, где макросы. В Гугл таблицах аж 4 клавиши дает для макросов

[@uvl77]

сочетание Alt + Ctrl +V не работает в excel 2003

[@andreysukhov]

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

[@uvl77]

@@andreysukhov специальная вставка есть... но только из меню, .. быстрых клавиш нету. P.S. а старые версии офиса популярны на старых компах, работающих на дровах и каменном угле.

[@RomanShagrov]

@@uvl77 ms office 2003 по своим возможностям уступает современным OpenOffice и LibreOffice (они полностью бесплатны), в которых требования к системе на много меньше, чем у современного ms office и, в то же время, они умеют спец. вставку. Если вам не нужны макросы на vba, то должно подойти. Попробуйте.

[@uvl77]

@@RomanShagrov если вы хотите использовать компьютер как печатающую машинку (набрал тест и тут же распечатал на принтере), то LibreOffice - это ваша программа. Имел счастье работать в ней. В LibreOffice файлы не полноценно совместимы с файлами MS Office, разметка текста едет и тп. В общем "Г". Если необходимо работать с чужими файлами и свои файлы отдавать другим, то вы просто привязаны к MS Office.

[@uvl77]

P.S. последняя версия OpenOffice выпущена 25 января 2011 г., первая версия LibreOffice выпущена 25 января 2011 г. По сути LibreOffice это продолжение OpenOffice. И тем не менее она далека от того, чтоб полноценно заменить MS Office.

[@Sidorov711]

Клёво!

[@ГеннадийПелагеин]

спасибо

[@RomanShagrov]

Сначала смотрел и думал: "Что? Да какая проблема переназначить сочетание клавиш?". Потом полез в настройки excel 2019 и... удивился... А нету теперь такой возможности :( Попробовал ваш способ с макросами, но получаю ошибку :( Отладка выделяет строку Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False с сообщением о 1004 run-time ошибке и текстом "Метод PasteSpecial из класса Range завершен неверно". Сижу в шоке... На ровном месте такая ошибка? о_О Ну не может ее тут быть. В общем, дали вы мне стимул с интересом провести пару вечеров )

[@RomanShagrov]

Разобрался ))) Я не записывал в макрос копирование ячейки, а только вставку, в этом и проблема. Автору думаю тут всё понятно. А для тех, кто не понял, поясню... Тут надо отредактировать код, чтобы если буфер пустой, то ничего не делать. Ошибка именно из-за пустого буфера обмера, т.к. когда после Ctrl+C (имею ввиду, когда вокруг ячейки "бегают муравьи") нажимаешь Esc, буфер очищается. Иными словами, нужно в if обернуть или (что не стоит делать, если не понимаешь хорошо последствия) в On Error Resume Next. PS: почему я так сделал... Привычка от Linux и OpenOffice, где при нажатии Ctrl+Shift+V вставляет просто текст, без какого-либо форматирования (в винде в некоторых программах тоже работает, например, в ThunderBird при написании письма).

[@andreysukhov]

Рассмотренный в видео макрос не идеален в плане кода, как и все, что создает по умолчанию макрорекордер, но все же при его запуске ошибка выводиться не должна и условие там в принципе лишнее. Ведь фактически макрос работает так - мы выбираем ячейку или диапазон, а потом его запускаем. Соответственно макрос обрабатывает значения выделенного диапазона, копируя их в буфер обмена (Selection.Copy) и затем вставляя на прежние места в виде значений (Selection.PasteSpecial c параметрами). То есть неважно будет ли ячейка содержать данные или нет, она все равно будет скопирована.

[@RomanShagrov]

@@andreysukhov Я без претензии писал (на всякий пояснил). Ошибки нету, только звук воспроизводится о недоступности действия (такой же звук бывает когда есть диалоговое окно и кликаешь по главному окну программы). А ошибка возникает, когда залезаю в код макроса и там жму F5, чтобы продолжить выполнение макроса. В момент, когда залезаю в код, макрос приостановлен (то есть, звук говорит, что произошла ошибка, но эксель ее не показывает, но макрос ставит на паузу). Моей целью было сделать, чтобы по Ctrl+Shift+V было поведение аналогичное Ctrl+v, Ctrl, C (то есть вставка значения) и чтобы не было привязки к диапазону. То есть, если я где-то в другой программе скопирую несколько строк, выделю ОДНУ ячейку в эксель и вставлю из буфера, то вставка будет произведена в активную ячейку (Application.ActiveCell) и ячейки расположенные под ней; а если в экселе скопирую диапазон и потом вставлю значения, то диапазон будет перезаписан значениями из этого же диапазона. Я хотел именно этого достичь, но забыл, что буфер очищается, поэтому и появился первый комментарий. А потом вспомнил и написал второй комментарий. Забыл про личную книгу макросов. Благодаря вашему видео вспомнил про нее ))) За что огромное спасибо!!! PS: Позвольте вопрос? Вдруг вы с таким сталкивались... Давно ищу ответ, но так и не нашел. Бывает выделяешь несколько строк (или ячеек и потом Shift+Space, горизонтальная полоса при этом прокрутки большая, а не маленькая, т.е. справа нет кучи ячеек, которые эксель считает использованными), жмешь Ctrl+C, появляются "муравьи" и... эксель словно зависает. Тыкаешь по другим открытым книгам, а они не реагируют (два монитора). Долбишь Escape, переключаешься в другую программу, долбишь там Escape и/или Enter, возвращаешься в эксель и он отвисает (бывает несколько раз это проделать надо). В чем может быть причина? Система свежая (вирусов 146% нет), винда 10, эксель 2019, все лицензионное, оперативы 32 Гб, ноут 2021 г.в. Аналогичное поведение в эксель 2016, винда 10, ноут 2012 г.в. У коллеги тоже такое бывает. По опросу знакомых, они тоже с таким поведением сталкивались. Файлы всегда xlsx, то есть макросов нет. Наличие надстроек роли не играет - и с ними и без них такое поведение возникало. А иногда в такие моменты эксель и вовсе закрывается и запускается снова (благо автосохранение спасает, по большей мере). Я даже дебаггер держал запущенным, процесс монитор, журналы изучал. Ни за что зацепиться не могу :( В саппорт писал, либо без ответа, либо бред, не имеющий отношения к теме. Честно, мне даже стыдно этот вопрос писать... Айтишник с 20+ лет стажем, а понять и отловить такое поведение не могу :'(

[@andreysukhov]

Да я претензии и не видел:) Я думал, что речь идет о макросе под описанную в видео задачу, поэтому пояснил, что условие вроде бы там лишнее. С описанной вами проблемой не сталкивался. Возможно потому, что обычно выделяю конкретные диапазоны, а Ctrl/Shift + Пробел выделяют весь столбец/строку, а это очень большой массив и я обычно стараюсь такого избегать. На проблему может влиять очень много параметров, например: - сколько значений в диапазоне? Ведь в столбце более 1 000 000 строк, а в строках более 16 000 ячеек. - формулы это или статические значения - есть ли форматирование и какое. Задано оно только на ячейки со значениями или на весь столбец/строку. Например, условное форматирование - крайне ресурсоемкая штука и вот как раз с ней бывали проблемы. В итоге приходилось условное форматирование в готовом документе преобразовывать в статическое. Даже общее количество данных в книге и их тип будут влиять на производительность, поэтому попробуйте проанализировать каждую подобную ситуацию - сколько листов в книге, какие там данные (просто диапазоны, формулы, умные таблицы, сводные, диаграммы). Мне кажется, что проблема возникает как раз в "тяжелых" документах.

[@RomanShagrov]

@@andreysukhov Мы поняли друг друга и это главное ))) Жму руку! ))) Да, я это все понимаю. Но прикол в том, что такое бывает на абсолютно любых листах. книгах, таблицах. Например, есть на листе умная таблица на 10 столбцов, по Shift+Space эксель выделяет только в диапазоне этой таблицы, т.е. строка из 10 столбцов. Большинство ячеек - формулы вида =[@[Цена ]]*[@[К-во]] . Форматирования нет (точнее то, которое по умолчанию для умных таблиц задается). Вроде тут данные не тяжелые, но даже на такой простой таблице подобное поведение возникает. Иногда эксель грузит проц на 20-40%%, иногда загрузка 0%. Памяти обычно 10-20Гб свободной есть, эксель больше 300 Мб не кушал (не припомню, чтобы видел больше). В общем, тут нет никакой привязки к данным. Отсюда и растут сложности, чтобы загуглить или как-то описать на форуме, в саппорт единообразно (не знаю как правильнее это назвать, структурировано что-ли) не возможно. В саппорт ничего вразумительного не подсказали (мол, проблема скорее всего в вашей ОС, ПО, настройках, железе). На нескольких форумах тишина. Спросил из соображений "а вдруг". Ну да ладно. Не так часто возникает это, жить можно (к слову, в LibreOffice такого никогда не было). Когда что-то тяжелое эксель выводит сообщение, что буфер переполнен (или что-то такое, не помню). Спасибо! )))

[@Serdityy]

Жаль,что нет таких мануалов для работы с ios xl

[@vardanvachyan5823]

Все толко говорят