Спасибо, кажется, помогло.

Работал макрос долго. Часов 5 - потом уже лег спать и утром он закончил работу. Так что не знаю сколько часов он работал точно.

Но заменил насколько я понимаю все формулы с именованными диапазонами на формулы. Причем каким-то образом оставил и сами именованные диапазоны. Попробую их потом удалить.  

Спасибо, на примере это срабатывает.

А вот в моей книге что-то идет не так. Макрос работает, но в итоге не очень вижу какие-то изменения.
Видимо у меня там все перепутано.

В первую очередь выдал мне ошибку про диапазон ссылки - что он не такой как надо.
Попробовал убрать это условием If Not InStr(nm, "NAME?") > 0 Then

при этом имена диапазонов с битыми ссылками визуально не нахожу.  

Спасибо, макрос работает в предложенном мной файле примера.

Но у меня оказался более сложный случай. У меня ссылка на именованный диапазон может встречаться многократно на разных страницах книги.
Плюс сами формулы могут состоять из именованных диапазонов (число в одном именованном диапазоне прибавляется к другому именованному диапазону)

При таких обстоятельствах при первом нахождении имени макрос срабатывает как надо, а на втором уже не может найти ссылку на первоначальный диапазон.

Так что видимо макрос должен как-то запоминать уникальность имени и его диапазон и потом это проверять.  

Здравствуйте, в excel книге есть множество именованных диапазонов, которые ссылаются как правило на разные ячейки (как правило на одну ячейку). Причем именованный диапазоны ссылаются на фиксированную формулу.

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

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

Есть ли возможность сделать это как-нибудь не очень сложно.  

к сожалению, не нашел

Потестировал файл - все-таки не очень все это работает. Эксель должен отслеживать событие selectionChange все время, все время пересчитывать формулы - причем когда изменишь вручную зачеркнутый - все равно не пересчитывает значение формулы, потому что события selectionChange еще не произошло.
Но лучше конечно это, чем ничего.  

Насколько я понял - перехват некоторых событий все же отсутствует.

Учится я не против - не всегда просто получается найти информацию.

Спасибо!
Надо просто добавить "Application.Volatile" в начало формулы. Никогда просто раньше с таким не сталкивался.

P.S. да. Еще надо вставить в код листа
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Me.Calculate
End Sub"

Сейчас попробую как сделать для всех вкладок сразу если это возможно.  

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

Но как с этим бороться, не написано.

Здравствуйте!

Я сделал пользовательскую формулу, которая подсчитывает в диапазоне сумму чисел, причем если в ячейке установлен формат "зачеркнуто"
то эта цифра не учитывается.

Формула работает, но не автообновляется после того, как вручную какое-либо значение меняю на "зачеркнуто". Если использовать формат по образцу, то формула автообновляется.

Включен режим автообновления формул. Но формула пересчитывается только если в нее зайти и нажать на нее вручную.
F9 не работает (и shift + F9 тоже)

Подскажите, пожалуйста, как заставить пользовательскую формулу автообновляться (пересчитываться) .  

Спасибо. Буду пробовать. Экспериментировать.

Еще немного почитал один учебник по вба и у меня сложилось впечатление, что тут может помочь Windows Api. Надо будет попробовать изучить эту тему.

Код у меня тоже заработал - спасибо, уже лучше, чем ничего. Для мозга в любом случае наверное проще выбрать "копировать" из списка чем постоянно проверять какая раскладка активна Так что пока буду пользоваться этим, а там может и Windows Api освою или какое другое решение

Если я вас правильно понимаю, то кажется тут проще использовать фильтр по строкам.
Вначале фильтр по третьему столбцу по определенным комбинациям, а потом фильтр по цвету.
сразу все можно за один раз сфильтровать и удалить ненужное

Здравствуйте, известна проблема, что чтобы скопировать из редактора VBA русский текст, нужно предварительно переключиться на русскую клавиатуру.

Но это напрягает  - все время отслеживать какая раскладка включена.

Попытался сделать горячую клавишу Шифт+Контрл + С чтобы она запускала макрос, который будет нажимать:
1) комбинацию Контрол + 1 (у меня на компьютере она переключает раскладку на русский язык).
2) посылает команду Cntr+ C которая по идее должна копировать в буфер обмена все что выделено.

Но не получается.

Работает только если запускать из книги Эксель, и копирует в буфер обмена какую-то ерунду. А мне надо из редактора ВБА.

вопросы:
1) что я делаю не так, можно ли так решить проблему
2) какие есть альтернативные решения (искал сам, но не нашел решения)

Код, который загружается при открытии книги:

Код при нажатии на контрол+шифт+С:

Прошел курс Условного форматирования  на этом сайте. Очень интересно и полезно.

Поставил себе задачу сделать условное форматирование в табеле учета рабочего времени: чтобы выходные отражались одним цветом, а праздники - другим.
Решил использовать совет Павлова про вечный календарь. В итоге выгружается умная таблица "Производственный_календарь" с праздниками.

Но у меня не получилось в формуле ЧИСТРАБДНИ  сослаться на диапазон с праздниками, который у умной таблице. Не получилось даже на столбец с праздниками (там наверное потому что есть пустые ячейки?)
Получилось только на ограниченный диапазон, который создал сам. Но при изменении праздников в дальнейшем автоматически не получится ссылаться на этот диапазон.

Как в УФ сослаться на умную таблицу?

Спасибо,

Логику формулы не очень понял. Если я правильно отобрал элемент в соответствии с этой формулой
получается 27 элементов (не 42), и они почти все мелкие. Ни одного крупного.

Поэтому первая формула подходила гораздо больше.  

Спасибо.

Думал 2 дня, чуть голову не сломал, а тут решили за 5 минут. Эх, не быть мне программистом.

Формула

1	=ЕСЛИ(R[-1]C>=Шаг;;R[-1]C)+RC[-1]

примерно то, что я искал.  
Хотя мне кажется, что я где-то видел формулу, которая по этому же принципу делила генеральную совокупность на определенное количество элементов. А при этом способе получается явно меньше.

По поводу Power Query - я только недавно узнал об этой штуке и только начал ее изучать. Так что для понимания как это сделано меня похоже отделяют месяцы изучения языка М - буду изучать. Спасибо.  

Здравствуйте, помогите пожалуйста с формулой выборки:

Задача № 1
1) выбрать из столбца цифр те, что больше ШАГа (100 млн) или же те, которые придутся на 100 млн нарастающим итогом
2) как только найдется первый элемент, накопительный итог должен как бы ОБНУЛИТЬСЯ И НАЧАТЬ СЧИТАТЬСЯ ЗАНОВО до того, как какой-т элемент снова не окажется больше ШАГа
Таким образом, выборка будет точно содержать элементы, что больше Шага плюс каждый элемент генеральной совокупности может попасть в выборку

Задача № 2
Желательно, чтобы при решении задачи 1 еще и так получалось, что выбиралось 41 элемент (генеральная совокупность / шаг) (хотя я подозреваю что может так не получаться)

При решении задачи 1 мной была опробована такая методика:
- в одном столбце считался нарастающий итог
- в следующем столбце была формула  (настающий итог)/шаг)  (как бы обнуление, типа число растет когда нарастающий итог приближается к шагу)
- в третьем столбце

Данная методика не дает нужный мне эффект. Хочу другую найти.  

Спасибо за ответ. Буду изучать формулу )  
 
по поводу надстроек - какие-то действия я могу выполнить ими, а дальше уже  формула Эксель - такой вариант мне  подходит.

Есть тест, где получилось так, что верный ответ всегда А. Требуется перемешать значения, чтобы правильный ответ получался в диапазоне от А до Е.  
 
Есть ли способ решить задачу средствами Эксель и надстроек? без макроса?  
 
спасибо.

еще раз спасибо...    
 
однако в формуле массива похоже ошибка была - второй аргумент формулы НАИМЕНЬШИЙ в формуле массива все время выдавал 4, а должен был выдавать 1, 2, 3...    
 
и наконец понял, зачем нужно было "яяя" - потому что искались ложные значения из-за еош...  
 
=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$23;НАИМЕНЬШИЙ(ЕСЛИ(ЕОШ(НАЙТИ("Итого по";$A$5:$A$23));"яяя";СТРОКА($A$5:$A$23)-4);СТРОКА($A$5:$A$23)-4));"")

возможно, это вам поможет (?)  
 
(см. файл)

спасибо... всегда бы так коротко и понятно )

получилось вот что...  
 
но мне кажется, не оптимальная формула

получилось решение, но использую 3 столбца - 2 разных вариант; надо как-то сократить до  1 формулы...

спасибо за вариант со скриптом, но я ВБА совсем не знаю - буду изучать, но хотелось бы решить проблемы встроенными средствами )  
 
пока мне самому вот что придумалось:  
 
число 277346582 после формулы =ТЕКСТ(I4;"# ###") получается в формате    
277 346 582, при этом это уже не число, а ТЕКСТ.  
в соседнюю ячейку вставляю его как значения - а потом пробелы заменяю на точки обычной вставкой/заменой (cntr + H)  
 
Вопрос только, как формулой поменять пробелы на точки, - сам я не особо сейчас пойму как. И все чтобы одной формулой работало. :)

Подскажите пожалуйста, как из (большого) числа, например, 277 346 582 сделать текст формата 277.346.582 -    
 
если ввести формат #"." ###"."### то визуально это будет выглядеть правильно, но мне надо чтобы это был текст.  
 
 
Выше было сказано, что это можно сделать с помощью формул...

попробуйте тут  
http://www.audit-it.ru/finanaliz/  
 
Раньше было вообще бесплатно - забиваешь данные баланса, и программа он-лайн все анализирует. Вроде бы сейчас тоже имеется возможность.

Спасибо, формула сложна - буду медитировать над ней, может быть пойму через недельку :)

Спасибо, формулой не-массива  тоже неплохо получается. У меня, видимо, получилось  сложнее, потому что я разместил таблицу не с краю, а посередине страницы.

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

Здравствуйте, у меня есть таблица, и я хочу выбрать из нее случайным образом несколько строк, например 5.    
 
Я хочу, чтобы по выбранным значениям строк возвращалась вся строка,    
сам я пробовал реализовать это с помощью функций ИНДЕКС и ПОИСКПОЗ - и приходилось записывать в каждую ячейку громоздкую формулу, что неудобно.  
 
Есть ли способ возвратить всю строку сразу?  
 
Видел нечто подобное на ВБА, но я его не знаю.  
 
Мне приходит в голову  объединить как-то все строки в одну (через разделитель?), и возвратить это значение. Однако же я не знаю, как эту информацию потом опять разделить по ячейкам.    
 
Помогите, пожалуйста, как это можно реализовать?