Помогите написать макрос в Excel

[Andriy]

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

надо "убить" формулы. но не все.

Итак, структура отчета примерно такая:
формула "промежуточные итоги"
формула "промежуточные итоги"
формула "суммесли"
формула "суммесли"
формула "суммесли"
формула "промежуточные итоги"
формула "суммесли"
формула "суммесли"
формула "суммесли"


Надо написать макрос, который убивал бы формулы в ячейках, содержащих "суммесли". Все остальные не трогал.
Подскажите какой-нибудь простой макрос.

[Jacky]

Что-то не особо помогли, да?
В таком случае лучше задать вопрос на каком-нибудь специальном форуме, например, на этом: http://planetaexcel.ru/forum.php?thread_id=12604 (тема для примера).

[Alextiger]

надо "убить" формулы. но не все.
выделяете нужные ячейки (столбцы, строки). "Копировать". Не снимая выделение "специальная вставка" -> "значения"
Наслаждаетесь :)

[Andriy]

выделяете нужные ячейки (столбцы, строки). "Копировать". Не снимая выделение "специальная вставка" -> "значения"
Наслаждаетесь
Вы смеетесь? у меня таких диапазонов примерно 20, не хочется тыркать столько раз..я так всегда делал, но надо автоматизировать. для "убивания" формул у меня есть макрос маленький, я выделял диапазон, нажимал Ctrl+q и макрос оставлял только значение. но была велика вероятность лишнее выделить, а после макроса нет возврата. и это занимало время. макрос все сам делал бы за 5 секунд.

Что-то не особо помогли, да?
В таком случае лучше задать вопрос на каком-нибудь специальном форуме, например, на этом: http://planetaexcel.ru/forum.php?thread_id=12604 (тема для примера).
мне там уже помогли:
http://www.planetaexcel.ru/forum.php?thread_id=29514

[Alextiger]

макрос все сам делал бы за 5 секунд
ну а что есть макрос? Вы включили запись макроса, 1 раз повыделяли ячейки, конвертировали. И он это всё запомнил и будет в точности повторять автоматом :)
А вам там уже не макрос написали а программку на VBA :)

[Aspirant_Cat]

А вам там уже не макрос написали а программку на VBA
Так макрос ведь и есть программка, если не ошибаюсь.

[Alextiger]

Так макрос ведь и есть программка, если не ошибаюсь.
в препарированном виде - да. Только для ее создания пользователю не надо знать язык программирования. Он задает пример действий, а Excel сам пишет программку.

[Andriy]

ну а что есть макрос? Вы включили запись макроса, 1 раз повыделяли ячейки, конвертировали. И он это всё запомнил и будет в точности повторять автоматом
это да, но форма моего отчета может меняться, тогда надо заново записывать макрос. то, что посоветовали на том форуме - просто супер!!
а как Вы предлагаете, да и я сам мог только так написать, это очень коряво.. его записывать долго, и вдруг где надо будет строку вставить? в модуле это не отразится.

в препарированном виде - да. Только для ее создания пользователю не надо знать язык программирования. Он задает пример действий, а Excel сам пишет программку.
вот в таком виде я написал макрос, убивающий формулу в выделенном диапазоне. и то там нашлась ненужная строка:) а сколько их будет в большом макросе?! там же даже движение колесика мыши отображается в модуле макроса.

[Alextiger]

но форма моего отчета может меняться
Тогда другое дело! Но в ТЗ из первого поста это было не оговорено :p

Добавлено через 15 минут
Вопрос: а если уже все цифры рассчитаны, зачем вообще формулы оставлять? Поменять все на значения и всё

[Andriy]

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

Добавлено через 2 минуты
Тогда другое дело! Но в ТЗ из первого поста это было не оговорено
ну теперь я понимаю Ваше недоумение:)

[Alextiger]

соответственно, цифры постоянно пересчитываются и меняются. и их надо убивать
ну и? Пересчитали. Убили все формулы на листе одним кликом. Отдали руководству :)
на след. деть то же самое. Руководству то формулы не нужны. Не понимаю прикола :)

[Andriy]

ну и? Пересчитали. Убили все формулы на листе одним кликом. Отдали руководству
на след. деть то же самое. Руководству то формулы не нужны. Не понимаю прикола
а кто Вам про прикол говорил?!
еще раз:
1. отчет имеет сейчас n число строк и m столбцов.
2. каждый день, если не убивать формулы, отчет обновляется. и его надо высылать руководству
3. формулы надо в высылаемом отчете убивать.
4. структура отчета может поменяться.
5. (прихоть) иногда руководству надо видеть, а как мол средняя ставка считается. эту группу формул убивать не надо. также могут и другие формулы попросить расписать. поэтому приходится убивать только определенные группы формул, что сделать 1 кликом - только так, как я привел в ссылке в сообщении так № 4.

Я понимаю эту приходить руководства. Мне если бы прислали отчет, в котором тупо строки, хотя там есть и суммирование, и вычитание, и нахождение средней, но не было бы формул, не понравилось бы. простые формулы должны быть, чтобы не писать, как что считается напротив наименования строки. ну тот же расчет прибыли должен быть понятен, А минус В. У меня там 6 финрезов от различных операций, операционная прибыль, расходы 3 группы, доходы 2 группы. Затем активы и пассивы, которые тоже определенную структуру имеют. ну в общем нужны им формулы простые, чтобы понимать структуру расчета основных показателей.

[Alextiger]

понятно, любознательное руководство :)

[Andriy]

да это скорее всего не руководство, а один из адресатов. есть у нас филиал в Вашем городе, так там такие сидят, дундуки:)

[gav]

Код полностью решил проблему?

[Andriy]

gav, решил. Это не Вы были?:-)
у меня еще есть проблема, если Вы хорошо разбираетесь, могу обратиться?

если что, вот моя проблема:
http://www.planetaexcel.ru/forum.php?thread_id=29623&thread_id=29623&page_forum=lastpage&allnum_forum=1#post242882

Тут в принципе тоже несложно должно быть. но тут уже надо задавать диапазон наверное, а то все-все скопировать (65000 записей) и вставить в другую книгу эксель сможет только один раз(

Добавлено через 5 часов 7 минут
и эту задачу решили уже..

[Maksimus]

Не хочется создавать отдельную тему, напишу здесь.

Дано: Excel 2010.
Требуется:
1. Сделать так, чтоб в ячейке появлялась текущая дата.
2. Дата должна появляться только после клика на ячейку.
3. Дата д.б. фиксированной (с течением времени не должна самостоятельно изменяться).

Вопрос к корифеям: Реализуемо? Если да, то как?

Заранее спасибо.

[Kayra]

Дано: Excel 2010.
Требуется:
1. Сделать так, чтоб в ячейке появлялась текущая дата.
2. Дата должна появляться только после клика на ячейку.
3. Дата д.б. фиксированной (с течением времени не должна самостоятельно изменяться).
Способ для чайников:
Запускаете запись макроса: в ячейку вводите функцию =СЕГОДНЯ(), копируете из ячейки это число и вставляете в другую ячейку как значение. Останавливаете запись макроса. Только вот можно ли сделать так, чтобы макрос запускался при клике на ячейку, не знаю. Можно создать кнопку, запускающую макрос при нажатии. Можно без марания ячеек, прописать в VBA и привязать к клику кнопки.
Только зачем нужен такой макрос, не проще ли набивать дату на клавиатуре ;)? Ползти в этом случае точно не будет.

[Maksimus]

копируете из ячейки это число и вставляете в другую ячейку как значение
Не, копирование не пойдет.

Только зачем нужен такой макрос, не проще ли набивать дату на клавиатуре ?
Какие все любобытные :)
Хорошо, расскажу в чем смысл. Есть некий журнал в Excel, в котором должны фиксироваться дата и время определенного события (например, получение продукции). Поскольку продукции м.б. много, то вручную набивать дату-время трудозатратно. А вот одним кликом было бы удобно - щелкнул по ячейке, туда автоматом записались дата-время, щелкнул по другой - следующая.

Т.е. решить задачу можно только написанием доп.программы?

Добавлено через 16 минут
Зы. Нужен именно эксель. Любые другие программы не подойдут.

[Hogfather]

Способ для чайников
:facepalm:

(патетично) Мда. И это гордость отечественной науки. Вышел из забоя, глянул, а тут такой позор: элементарную вещь написать не могут...

Maksimus, только ради Вас оторвался от производства. Цените.

Option Explicit
' Обратите внимание! Это функция конкретного листа, она должна быть там, а не в модуле
' Откройте VBA проект, выберите лист и по правой кнопке мыши Viev Code
' В открывшееся окно скопируйте программу. Работает по двойному нажатию
'
' Копирайт (ц) 2013 Hogfather. Олл райтс резервед
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo ErrorEvent
' Только одна ячейка
If Target.Cells.Count > 1 Then
Exit Sub
End If
' Чтобы избежать зацикливания
Application.EnableEvents = False
' Если ячейка пустая -- ставим дату
If Target.Offset(0, 0).Value = "" Or IsNull(Target.Offset(0, 0).Value) Then
Target.Offset(0, 0).Value = Date
End If
ExitNormally:
' Возвращаем всё взад
Application.EnableEvents = True
Exit Sub
ErrorEvent:
MsgBox Err.Description
Resume ExitNormally
End Sub

[Jacky]

Есть еще такой способ, горячими клавишами.

Нажать последовательно ctrl+shift+4 (вставка даты), пробел, ctrl+shift+6 (вставка времени).

[Hogfather]

Дано: Excel 2010.
Есть еще такой способ, горячими клавишами.
Нажать последовательно ctrl+shift+4 (вставка даты), пробел, ctrl+shift+6 (вставка времени).
:facepalm:


Вставка фиксированного значения даты или времени
Статическое значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку делается "снимок" текущей даты, который вставляется в ячейку. Это значение ячейки не изменяется, поэтому оно считается статическим.

1.Выделите на листе ячейку, в которую требуется вставить текущую дату или время.
2.Выполните одно из действий, указанных ниже.
Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).
Чтобы вставить текущее время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).
Чтобы вставить текущие дату и время, нажмите клавиши CTRL+; (точка с запятой), затем клавишу ПРОБЕЛ, а после этого — CTRL+SHIFT+; (точка с запятой).

[Hogfather]

Maksimus, Чтобы работало только в одной колонке достаточно добавить следующий код (выделено жирным)


Option Explicit
' Обратите внимание! Это функция конкретного листа, она должна быть там, а не в модуле
' Откройте VBA проект, выберите лист и по правой кнопке мыши Viev Code
' В открывшееся окно скопируйте программу. Работает по двойному нажатию
'
' Копирайт (ц) 2013 Hogfather. Олл райтс резервед
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo ErrorEvent
' Только одна ячейка
If Target.Cells.Count > 1 Then
Exit Sub
End If


' Только для пятой колонки. В остальных случаях ничего не делаем
If Target.Column <> 5 Then
Exit Sub
End If



' Чтобы избежать зацикливания
Application.EnableEvents = False
' Если ячейка пустая -- ставим дату
If Target.Offset(0, 0).Value = "" Or IsNull(Target.Offset(0, 0).Value) Then
Target.Offset(0, 0).Value = Date
End If
ExitNormally:
' Возвращаем всё взад
Application.EnableEvents = True
Exit Sub
ErrorEvent:
MsgBox Err.Description
Resume ExitNormally
End Sub

[Jacky]

Hogfather, не понял смайл после своей цитаты в предыдущем сообщении. Вы хотите сказать, что..?

Добавлено через 1 минуту
И знаете почему не понял? Потому что указанные мной варианты горячих клавиш работают надежно, а вот эти
Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).
Чтобы вставить текущее время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).
Чтобы вставить текущие дату и время, нажмите клавиши CTRL+; (точка с запятой), затем клавишу ПРОБЕЛ, а после этого — CTRL+SHIFT+; (точка с запятой).
зачастую не срабатывают и сообщений об этом много в интернете. Почему не срабатывают, неизвестно. У меня кстати тоже не работают.

Excel 2010.

[Hogfather]

Jacky,
Элементарно. Зависит от раскладки клавиатуры и способа её переключения.
У меня переключение по Ctrl-Shift и Ваши комбинации не работают. Рекомендованные - работают на двух языках. Проверил.

На "4" на русском регистре ";", на 6 на русском регистре ":", по сути, Вы пытаетесь сделать то же самое, что написано в инструкции, но в русской раскладке.

[Jacky]

У меня переключение по Ctrl-Shift и Ваши комбинации не работают.
Переключение по Ctrl+Shift и у меня не работает, если имеется в виду переключение языка клавиатуры (переключается по Alt+Shift). Тут я видимо не понял содержания цитаты. См. ниже последний абзац.
но в русской раскладке.
В любой раскладке.
Есть нюанс, нажимать цифры надо на основном алфавитно-цифровом блоке, на дополнительном правом цифровом -- работать не будет.

Добавлено через 56 секунд
по сути, Вы пытаетесь сделать то же самое, что написано в инструкции, но в русской раскладке.
Не совсем то же самое. Комбинации отличаются.

Короче говоря, желающие могут попробовать оба варианта, они равноценны, у кого что будет работать.

Добавлено через 15 минут
Hogfather, для полноты картины попробовал сменить у себя в системе переключение языка ввода с Alt+Shift на Ctrl+Shift и при этом комбинации ctrl+shift+4 и ctrl+shift+6 продолжают успешно работать в любой раскладке. Так что остается повторить:

желающие могут попробовать оба варианта, они равноценны, у кого что будет работать.

[Hogfather]

Hogfather, для полноты картины попробовал сменить у себя в системе переключение языка ввода с Alt+Shift на Ctrl+Shift
Аналогично попробовал. У меня работает мой вариант. Ваш -- нет, дело явно не в способах переключения раскладки.

P.S. А тема вообще не о горячих клавишах, а о макросах. Макрос я написал. Dixi.

[Дмитрий В.]

Спрошу тут, чтобы не создавать отдельную тему.
Коллеги, вводная такова. Имеется таблица в Excel, ячейки содержать в основном текст, в них разный цвет шрифта. Ячейки разного цвета идут вперемешку (к примеру, C1 красная, C2 красная, C3 зеленая, D1 синяя).
Внимание, вопрос: есть ли возможность весь текст определенного шрифта перенести налево от определенного столбца (допустим, B)?
Пока в голову пришла лишь такая идея - добавить к ячейкам определенного цвета какой-нибудь символ, например, ! , чтобы при сортировке они вылезли наверх, и переносить кусками. Возможно, есть что-то менее трудоемкой, т.к. столбцов там весьма немало?
P.S. Студенты принесли материалы, сделанные понятно как, вот сижу и думаю, придется ли говорить им все переделывать вручную или все же удастся обойтись без крайних мер.

[Hogfather]

Дмитрий В., в последних Excel есть фильтрация по цвету.

[kravets]

А я потерялся в Word 2003. Частая операция -
- правая кнопка на формуле
- формат объекта
- вкладка "размер"
- поле масштаба "по высоте" установить в 80 %
- ok

Начинаю записывать макрос - вот прямо по этим действиям. А получается невесть что, причем разное...

[Дмитрий В.]

в последних Excel есть фильтрация по цвету.
Спасибо! В 2003 этого не было, а 2007 поставил, и все нашлось.

[Рената]

Спрошу здесь.
В Экселе есть два столбца с кадастровыми номерами участков.
Номера участков в первом и втором столбце могут повторяться.
Мне надо их сличить и удалить повторяющиеся только из одного столбца.
Мне надо чтобы остались в списке только не повторяющиеся номера.
Можно это как-то автоматизировать?

[avz]

Проблем нет, надо пример файла и уточнение, в первом или втором столбце делать не повторяющиеся значения.

[Рената]

avz, вообще без разницы.
Как сюда вставить пример?

[avz]

zxenon@narod.ru

[Рената]

avz, спасибо, я завтра с работы пришлю.

[Dr.X]

А зачем Эксель?

Есть же Python.