Профессиональные приемы работы в Microsoft Excel

Выпуск 22
от 2009-06-05

Подписчиков: 5950

Рассылка сайта "Профессиональные приемы работы в Microsoft Excel"

Основные разделы сайта

Статьи

Форум

Новости сайта в формате RSS
Новые сообщения форума в формате RSS

Порекомендуйте нашу рассылку друзьям

Обратная связь
Поддержка
(форум рассылки)

Наши рассылки:

Наши сайты

Профессиональные приемы работы в Microsoft Excel
Excel от новичка до эксперта

bonifatiy.narod.ru
В помощь выздоравливающим алкоголикам и наркоманам

Собор Смоленских святых. Жития, иконы, календарь памятных дат Смоленской епархии.

StudentTools.Narod.Ru Для тех, кто любит учиться

Наши друзья

VBAtips.ru -
подсказки и решения на VBA

wordexpert.ru
Профессиональная работа с текстом

planetaexcel.ru
Когда заешь - все просто

Новые сообщения форума "Профессиональные приемы работы в Microsoft Excel"

Re: Как заставить функцию выводить результат с разным форматированием
2009-05-29 11:00
Я так пробовал, проблема в том, что если в ячейке находится формула, то макрос ничего не выделяет

Re: Как найти где тормозит
2009-05-29 11:00
В файле несколько листов? Отключите автоматический пересчет: пересчет текущего листа Shift+F9.
Каких формул больше всего, если ли ссылки на другие книги, если да, они открыты или нет?

Re: Как заставить функцию выводить результат с разным форматированием
2009-05-29 11:15
Надо сделать вспомогательную ячейку, в ней будет формула, а макрос будет брать из нее результат, вставлять как значение в главную ячейку и там наводить красоту.
Вспомогательную ячейку можно скрыть, защитить и т.д.

Re: Каким образо возможно использовать встроенную функцию поиска (внути листа) в VBA
2009-05-29 11:23
В окне VBA есть справа вверху поиск в справке по VBA можете туда забить Cells.Find и почитать.
Можете поиграться с макрорекордером.

Cells.Find(What:="мурманск", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, Sea...

Re: Как заставить функцию выводить результат с разным форматированием
2009-05-29 11:34
С макросом сложнее, когда работы море забудешь нажать кнопочку, значение не обновятся и финита ля комедия.
Решил сделать проще. Из ячейки в несколько ячеек вытащу значения, формат там будет задан, а в Ворд (для чего собственно все это и нужно) буду вст...

Re: Как заставить функцию выводить результат с разным форматированием
2009-05-29 11:40
Можно попробовать выводить результат в несколько смежных ячеек, имеющих фиксированные форматы. А итоговые значения распределять по ячейкам в зависимости от результата вычисления.

Защита листа, пароль и макрос. Как увязать?
2009-05-29 12:19
Суть такова. Есть лист защищенный паролем и есть макрос, который снимает и ставит защиту листа. Но срабатывает он только, когда лист защищен БЕЗ пароля. Хочется, чтобы макрос работал с запароленым листом и сам бы вписывал пароль. Наверно надо куда-то в...

Re: Защита листа, пароль и макрос. Как увязать?
2009-05-29 12:30
На первую часть вопроса сам нашел ответ:

Re: Вычисления относительного отклонения между несколькими числами одновременно
2009-05-29 13:23
_Boroda_, спасибо за быстрый ответ. Пока не уверен, что он меня устроит на все 100%, однако есть над чем подумать.

Re: Как найти где тормозит
2009-05-29 14:32
Это финансовый файл, в нем 18 листов. На одной странице кратко выписки по 9 фирмам с разделением по дням и по существу (затраты, опл поставщикам и проч), они получаются формулами "суммесли(...,дата,...)" из 5 страниц. На второй стр примерно то же, но в...

Группа и cтруктура, изменяемая автоматически
2009-05-29 18:13
Коллеги, есть ли возможность менять структуру таблицы в зависимости от табличных данных, формируемых с помощью формул?
То, что это можно сделать при помощи макросов понятно, а можно ли без них обойтись?
Во вложенном файле пример таблицы и вручную сфор...

Как запаролить просмотр и редактирование макроса.
2009-05-29 21:27
Как вообще запретить создавать макросы и тем более открывать VB в excel и просматривать самое ценное.

Re: Как запаролить просмотр и редактирование макроса.
2009-05-29 21:41
Сам нашел.
"Tools/VBAProject properties"

График с календарем
2009-05-29 23:14
Добрый вечер. Поможете? надо составить график для смен 12 часового раб дня. Т.е смена через 4 дня (1, след 1 через 4 дня; вторая, след вторая через 4 дня)Выбирается месяц и год - получаем готовый график. Реально? можно оттолкнуться от определённой даты...

Re: График с календарем
2009-05-30 05:41
похоже, что примерно так

На листе в столбик несколько десятков фамилий,необходимо чтобы автоматически
2009-05-30 09:06
в произвольном порядке на лист2 копировались ежедневно семь-десять фамилий

Re: На листе в столбик несколько десятков фамилий,необходимо чтобы автоматически
2009-05-30 17:37
Примерно так.
Полученный список автообновляется при каждом пересчете листа.
Способы лечения:
1). Включить ручной пересчет.
2). Сохранить и вставить значениями.
3). Поместить формулу в макрос, пересчитывающий её раз в сутки.

Заполнение ячеек, зная номер строки и нужное значение
2009-05-31 14:04
Здравствуйте!

Помогите, пожалуйста, написать макрос! :

Есть диапозон С5:С145, где должны быть данные. Но их должен вносить компьютер (VBA), на основе следующих:
Эти данные под столбцами А и В. Под столбцом А числовые значения номера строки. По...

Как добавить содержимое одной ячейки к содержимому другой?
2009-05-31 22:40
Здравствуйте!
Имеется две ячейки:
А1: Шланг садовый;
A2: 12345.
Нужно артикул "12345" добавить к наименованию "шланг садовый".
Спасибо за ответ!

Re: Как добавить содержимое одной ячейки к содержимому другой?
2009-05-31 23:11
В А3 (или в любой другой ячейке)
=A1&" артикул "&A2

Re: Как добавить содержимое одной ячейки к содержимому другой?
2009-06-01 00:24
Да, то, что надо! Спасибо за съэкономленное время.)

Как продолжить ЕСЛИ или есть более оптимальный путь
2009-06-01 10:58
Доброго времени суток!

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

Я создала формулу, но только для одного ме...

Re: Как продолжить ЕСЛИ или есть более оптимальный путь
2009-06-01 14:03
cheshiki1, файл скачала... но изменений в нем не нашла 
отправьте, пожалуйста, ещё раз...

Re: Как продолжить ЕСЛИ или есть более оптимальный путь
2009-06-01 14:18
извеняюсь

Re: Как продолжить ЕСЛИ или есть более оптимальный путь
2009-06-01 14:35
отлично работает! Большое спасибо!

Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-01 14:39
Есть задача такого плана:
Существует столбик с датами, столбик с суммами оплат и столбик с типом оплаты (нал, безнал), строк всегда количество разное - часто больше 5000. Нужно создать формулу которая бы суммировала оплаты, например в мае, например без...

Re: Заполнение ячеек, зная номер строки и нужное значение
2009-06-01 14:47
Это обязательно делать макросом?
Посмотрите решение с формулами во вложенном файле.
Если нужно убрать ошибки, то формулу можно усложнить и добавить проверки.

Re: Группа и cтруктура, изменяемая автоматически
2009-06-01 15:26
чего-то думается, что это нереально сделать так как ты хочешь

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-01 15:32
Почитайте справку по сводным таблицам.

Re: Группа и cтруктура, изменяемая автоматически
2009-06-01 15:45
Жалко.
Конечно, я без этого обойдусь, но вышло бы красиво... 

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-01 16:24
Спасибо это как раз то что надо - только возник вопрос в догонку:

"<= СУММПРОИЗВ() - не требует доп. Столбца, но не работает диапазонами, включающими пустые ячейки"
а если в этих "пустых" ячейках стоит "0"  я так понимаю оно работает?

И ещё: я добавил ...

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-01 18:09

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-02 02:22
Виды оплаты - 1) нал, 2) безнал, 3) все.
Если день=0, то считается за весь месяц.
Если месяц=0, то считается за весь год (выбор дня при этом не важен).
Можно добавлять любое кол-во строк.

Re: менять местами между столбцами и строками
2009-06-02 11:16
Попробую обяснить:

1) В List1 столбец С (310)
IF C14>=C15
AD=С14-С15 (=700-695=5)
ELSE
AD=С15-С14
ENDIF

2) В List2 столбец С

C4=AD

3) В List2 столбец D от руки пишутся.
столбец F в зависимости от D вычысляется.
столбец J и G тоже вычысляется.

4) В сле...

Календарь с указанием праздничных дней
2009-06-02 11:27
Всем доброго времени суток! Нужна ваша помощь в составлении календаря. Задание следующее:
1. заполнить первый столбец датами от 1 января 2009 года до 31 декабря 2009 года.
2. в формате даты должен быть указан день недели в коротком виде, например, ВТ ...

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-02 11:53
Немного не понял - это как сделано?
В первый раз вижу такую формулу... если можно обьясните подробнее... и как можно её привязать к другому файлу? Как добавлять больше аргументов (например если добавится дополнительный столбик - "менеджер")

Прогноз на следующие полгода
2009-06-02 12:30
Добрый день!

Подскажите, пожалуйста, как получить прогноз на следующие полгода, если есть данные за 3 года + первые полгода. Также присутствует сильная сезонная зависимость.

Спасибо!

как автоматически вставить в ячейку любое число из определенного диапазона чисел
2009-06-02 13:03
Здравствуйте!! помогите кто нибудь!!
Как автоматически вставить в ячейку любое неповторяющееся число из определенного диапазона чисел.

Re: Календарь с указанием праздничных дней
2009-06-02 13:38
Посмотрите вложение.
Я его адаптировал из производственного календаря, выполненного для своих целей (мне требовалось использование внешних ссылок на этот файл) - но возможно Вам тоже подойдет.

Re: как автоматически вставить в ячейку любое число из определенного диапазона чисел
2009-06-02 13:44
Например, в диапазоне ячеек A1:A100 записать формулу =СТРОКА()+999, где константа 999 определяет начало диапазона неповторяющихся [натуральных] чисел  :

Макрос отправки книги по электронной почте
2009-06-02 15:10
Уважаемые Гуру!
Суть вопроса вот в чем:
После переустановки системы на компьютере не могу запустить макрос отправки книги по эл.почте. Остановка происходит на следующей строке:

            Application.Dialogs(xlDialogSendMail).Show name_Addr_Mail
с соо...

Re: Календарь с указанием праздничных дней
2009-06-02 15:26
спасибо огромное!!
только вот непонятно, какой функцией происходит окрашивание выходных дней? потому как в красный цвет окрашиваются СБ и ВС, а если праздничный день переносится с выходного на понедельник, то он (ПН) уже не окрашивается.

Re: Календарь с указанием праздничных дней
2009-06-02 16:02
За выделение ячеек (цветом и надчеркиванием) отвечает УСЛОВНОЕ ФОРМАТИРОВАНИЕ (см. меню ФОРМАТ).
Ошибка с праздничными понедельниками (черными вместо красных) возникала из-за того, что первым срабатывало условие проверки начала недели, а второе условие...

Re: Прогноз на следующие полгода
2009-06-02 16:04
Попробуйте здесь почитать:

Периодические линии тренда в прогнозировании объемов продаж

Re: как автоматически вставить в ячейку любое число из определенного диапазона чисел
2009-06-02 17:19
Спасибо но в этом случае числа будут увеличиваться на одно и то же число и только в положительную сторону от первоначального, а меня интерисуют разные отличающиеся друг от друга положительные числа. Главное что бы они отличались друг от друга  в большу...

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-02 17:47
См. вложение

Re: как автоматически вставить в ячейку любое число из определенного диапазона чисел
2009-06-02 17:52
Обратите внимание на функцию СЛЧИС() - пример во вложении.
Еще можно составить сложную и длинную последовательность чисел (например, сгененрировать ее с помощью той же СЛЧИС()  ), последовательные члены которой выводить по месяцам - ВПР(...СТРОКА()....

Re: как автоматически вставить в ячейку любое число из определенного диапазона чисел
2009-06-02 18:25
ДА!!! отлично!! все работает как надо. Большое спасибо...
Я пробовал функцию СЛЧИС(), но не знал как правильно ее применить. Еще раз большое спасибо!!!

как отделить текст от цифр?
2009-06-02 22:54
Мне нужно составить функцию которая будет отделять текс от цифр в ячейке

нашел функцию =ПОДСТАВИТЬ(ячейка; ДАННЫЕ; "") но я незнаю как зделать так, чтобы она отделяла в лбой ячейке текст от цифр

Re: менять местами между столбцами и строками
2009-06-02 23:48
Примерно так

Re: как отделить текст от цифр?
2009-06-03 00:05
Всегда ли:
1) текст без пробелов,
2) текст отделен от числа пробелом,
3) сначала идет текст, а потом число,
4) после числа нет текста?

Если на все вопросы ответ "Да", то формула такая:
=ЕСЛИ(ЕОШ(ПОИСК(" ";A2));A2;ЗАМЕНИТЬ(A2;ПОИСК(" ";A2);100;"")),
есл...

Re: как отделить текст от цифр?
2009-06-03 01:11

Re: как отделить текст от цифр?
2009-06-03 01:32
Данные прописаны в имени "Данные" см. Вставка - Имя - Присвоить

Хорошо работает вариант 1, в нем похоже ничего не надо менять. Просто вставь сво значения в столбец А и протяни формулы в столбцах B, C и D на нужное кол-во строк.

Если не получися, то сна...

Re: менять местами между столбцами и строками
2009-06-03 08:13
Спасибо, Вы очень помoгли мне.
Но, скажите и это тоже пожалуйста, (помоему это последный вопрос будеть у меня) как этот цикл продолжать  в LIST1 не только для С (310), и все последующих столбцы (D (311), E (312),...) , и в LIST2 столбец G5, G6,...
Помо...

Простой цикл
2009-06-03 08:43
Доброе утро!
Если кто с таким сталкивался, помогите решить проблему:
1. Есть определенная таблица: столбец A - код, столбец B - наименование.
2. Необходимо перебрать столбец A по [url=http://msexcel.ru/content/view/22/2/]группа[/url]м и раскинуть выбранное на разные листы.
3. Но проблема в том, что ...

Re: Прошу помочь с задачкой. Самому сообразить - слабовато...
2009-06-03 10:02
Читайте справку по функции наибольшее().
Смотрите меню Формат - Условное форматирование.

Автоматическое изменение формата при обновлении сводной таблицы
2009-06-03 10:17
Уважаемые специалисты!
Работаю в экселе2003. Приходится часто добавлять [url=http://msexcel.ru/content/view/34/2/]сводные[/url] таблицы. Сразу после вставки применяю автоформат "Отчёт 6". Он меня полностью устраивает. Проблема в том, что ширина столбцов с данными выставляется автоподбором по ширине ...

Re: Прошу помочь с задачкой. Самому сообразить - слабовато...
2009-06-03 10:21

Re: Календарь с указанием праздничных дней
2009-06-03 10:52
Спасибо еще раз! Вы очень помогли! Конечно, с условным форматированием нужно еще разобрться, ни разу не приходилось сталкиваться с этим. Я так понимаю нужно просто создать правило?

Re: Простой цикл
2009-06-03 12:23
Спасибо!

Re: Списки в Excel
2009-06-03 16:40

Re: Календарь с указанием праздничных дней
2009-06-03 16:54
Да, нужно создать правило относительно значения записанного в данной ячейке или относительно результата формулы, записанной в условном_форматировании этой ячейки.
Например, условие3 в ячейке A2 выполняется, если значение, записанное в ячейке $C2 не ра...

помогите решить задачку
2009-06-03 17:16
Уважаемые ГУРУ помогите пожалуйсто с решением задачки.
Во вложении постарался объяснить проблему и пример.
Заранее благодарю.

Как записать в Excele следующую функцию?
2009-06-03 17:28
Подскажите как записать в Экселе следующую формулу X=(1807+-Корень(3301313-1960000y))/98

Спасибо.

Re: Как записать в Excele следующую функцию?
2009-06-03 17:38
=(1807+КОРЕНЬ(1960000*A1))/98

А что за действие +- ? Плюсминус?

Re: Как записать в Excele следующую функцию?
2009-06-03 17:42
Потому что было изначальное уравнение y=-0,0049x2+0,1807x+0,0184.
И была задача найти x.

Re: Как записать в Excele следующую функцию?
2009-06-03 17:47

Re: помогите решить задачку
2009-06-03 18:46
Так подойдет?
P.S. Обратите внимание на добавленные имена (меню ВСТАВКА / ИМЯ / ПРИСВОИТЬ)

Re: Помогите разобраться - ЕСЛИ, ВПР или всё же что-то ещё...
2009-06-03 19:28
Ага - похоже чё-то начинаю понимать... но вопросы ещё будут - пока просто времни нет.
Спасибо огромное!

Как в excel отобразить формулу как текст ?
2009-06-03 19:37
Например: в ячейке А1 формула "=(1+2)*3" . Существует ли функция , записав которую в ячейке В2, можно отобразить эту формулу как текст "=(1+2)*3" (т.е. get_formula_source_code(A1) )

Re: Списки в Excel
2009-06-03 20:07

Пикник
2009-06-03 21:15
Все дел полно.Все времени не хватает:( А вот психонули и выехали на пикник,делюсь простым рецептом...

Вкусные шашлычки


Нарезать мясо кубиками со стороной 2,5 см. Стебли зеленого лука порезать полосками длиной 3 см.

Куски мяса посолить, посыпать ...

Re: Списки в Excel
2009-06-03 21:18
sayak
Насчет изящества Вашей формулы не мне судить, просто прилагаю уже известную здесь многим альтернативу
P.S. Исправленному верить...

Открытие и закрытие файлов MSExcel кнопкой в MSAccess
2009-06-03 21:29
Помогите, пожалуйста, написать процедуру для открытия определенного файла в MSExcel при нажатии кнопки на форме в MSAccess

Re: График с календарем
2009-06-03 21:45
Спасибо большое, - лучше не придумаеш

Re: Списки в Excel
2009-06-03 21:49
2 sayak Посмотрите вложение.

Re: Списки в Excel
2009-06-03 23:28

Как сделать, чтобы ячейка закрашивалась,если в нее ввели букву &quot;с&quot;
2009-06-03 23:47
Как сделать, чтобы ячейки закрашивались,если в них вводят букву "с". Причем не просто букву "с", а номер типа 01-2336/с, 02-2337-1/с и т.п. А если просто 03-2338, то не закрашивается.

Re: Как сделать, чтобы ячейка закрашивалась,если в нее ввели букву &quot;с&quot;
2009-06-04 00:36
Условное форматирование. Формула: =ЕЧИСЛО(ПОИСК("с";A1))=ИСТИНА
см. пример

Re: помогите решить задачку
2009-06-04 09:41
Спасибо, что-то обрисовывается.
Но вижу что я сразу не указал еще условия:
1)Дата и водитель должны попадать из "рейсов"
2)Необходимо чтобы в "итог" попадала разница между сегодняшним днем и вчерашним.

Установить фильтр на всех листах сразу
2009-06-04 10:48
Вопрос такой, нужно на всех листах книги в первом столбце в [url=http://msexcel.ru/content/view/27/2/]автофильтр[/url]е выбралось "1".
Проблема только в том как это сделать автоматом по всем листам, листы могут иметь разное название.
Сделал макрорекорд...

Re: Установить фильтр на всех листах сразу
2009-06-04 11:20
For i = 1 To Sheets.Count
    Sheets(i).Select
Next i

Мне справка по VBA подсказала вот такое метод перебора всех листов, если перед Next i поставить ваш код ActiveSheet.Range("$A$1:$A$200").AutoFilter Field:=1, Criteria1:="1"
То заработает (у меня зар...

Re: Установить фильтр на всех листах сразу
2009-06-04 11:34
Просто великолепно!
Большое спасибо!

Re: Как в excel отобразить формулу как текст ?
2009-06-04 11:54
http://forum.msexcel.ru/microsoft_excel/kak_poluchit_formulu_iz_yacheyki-t1714.0.html

Присвоили имя формуле "Test" (Вставка-Имя-Присвоить)
=ПОЛУЧИТЬ.ФОРМУЛУ(Лист1!$A$2)

ячейка B2: =Test

поиск решения
2009-06-04 13:58
В прикрепленным файле все условии выполняется.
B123=634268.88 и D123=500000 ,
значить и это условия тоже выпольняется: B123>=D123

Я только довавляю это условия, который уже выпольняется: B123>=D123. Сразу после этого решение не находится.
Помогите по...

Предварительное форматирование таблицы
2009-06-04 14:34
Иногда возникает необходимость в большой таблице удалить пустые строки. Удалять по одной - очень долго и можно пропустить пустую строку. Существует ли способ удалить все разом?

Сравнение столбцов таблицы
2009-06-04 17:17
Добрый день!

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

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

Re: Сравнение столбцов таблицы
2009-06-04 17:44
Совпадений чего в чем?
Пометить цифры первого столбца, которых нет во втором? Или и те и другие?

Расчет пеней
2009-06-04 17:57
Задача стоит такая:
Надо посчитать количество дней просрочки по каждому платежу. При этом: отгрузка с номером счета фаткуры 1 оплачивается только платежом счета фактуры 1. Списание происходит из суммы пеней. Если платеж за отгрузку не покрывает суммы ...

Re: Как сделать, чтобы ячейка закрашивалась,если в нее ввели букву &quot;с&quot;
2009-06-04 20:59
Спасибо за совет! Работает!! 

Re: Списки в Excel
2009-06-04 21:14
вечер добрый,подскажите,пожайлуста,более менее толковую ссылочку на самоучитель ексель,раздел макросы.Буду очень признателен

Re: Помогите составить формулу
2009-06-04 21:16
=ВЫБОР((A1<1000)+(A1<50)+(A1<20)+(A1<10)+(A1<5);A1+A1*0,25;A1+A1*0,35;A1+A1*0,45;A1+A1*0,55;A1+A1*0,7)

Re: Списки в Excel
2009-06-04 22:33

Re: помогите решить задачку
2009-06-04 22:37
1) Подойдет ли обычная ссылка между итоговой таблицей и рейсами: a-la A1=рейсы!A1 => A2=рейсы!A2 ...
2) Попробуйте в примере пояснить, что Вы имеете в виду.

Помогите пожалуйста!
2009-06-04 23:00
Имеется задачка. Треугольник имеет вершины в -1;0, 0;1, 1;0

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

Имеется решение, но там заданы две формулы которые мне не понятны, не могли бы Вы обьяснить откуд...

На сегодня все. До встречи на следующей неделе.