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

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

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

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

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

Статьи

Форум

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

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

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

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

Наши сайты

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

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

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

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

Наши друзья

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

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

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

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

Расчет оптического сплиттера
2009-05-15 08:09
Приветствую всех!

Имеется формула определяющая вносимые потери оптических разветвителей с большим количеством выходных портов:

где D% – процент мощности, выво...

Re: Группировка данных без пробелов
2009-05-15 09:11
К сожалению массив данных еще более разросся, и даже эта формулка стала тормозить. может можно это как то осуществить програмно с помощью VBA?

Количество знаков у текста
2009-05-15 13:13
Подскажите, пожалуйста, можно ли в Excel подсчитать статистику знаков у текста, т.е.

фраза содержит столько-то знаков с пробелами...

Знаю, что в Word такая функция есть, но мне очень нужно в Excel это выяснить. Для работы очень нужно.

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

Работа программы на VBA в Office 2007
2009-05-15 14:12
Разработал программу на VBA применительно к Excel 2003. Защитил ее паролем на открытие документа Excel. Запускается программа через небольшую программку на С++ Builder, она вводит пароль в файл Excel на открытие документа. Скрыл в самом документе все с...

Re: Подсчет текстовых значений в столбце
2009-05-15 14:38
Спасибо за сочувствие).
Но там немного другая задача. Я попробую ее  на примере объяснить.
Вот, например, есть список городов, сформированный базой 1с за определенный период (любой выбранный) по выполненным заказам в некоторые города, и экспортированны...

Re: Количество знаков у текста
2009-05-15 15:39
Excel может подсчитать с помощью формулы =ДЛСТР()
См. вложение...

Re: Виртуозы ПОИСКПОЗ
2009-05-15 22:02
При "ручном" формировании строки в нее также не попажает формула СУММы - ее пользователь должен сформировать сам. Попросить его протягивать в эту строку формулу из смежной ячейки не сложно.
В крайнем случае можно добавить проверку: количество чисел в ...

Re: Расчет оптического сплиттера
2009-05-15 22:19
Посмотрите толи это, что Вам нужно?
P.S. Проверьте, пожалуйста, нет ли ошибки в Ваших цифрах:
10*LG(100/33) + LOG2(2-1)*0.4 + 0.2 + 1.5*LG(100/33) = 10*LG(3) + LOG2(1)*0.4 + 0.2 + 1.5*LG(3)= 11.5*0.48 + 0 + 0.2 = 5.74 <> 5.97

Re: Виртуозы ПОИСКПОЗ
2009-05-15 22:24
Спасибо,а скажите пожалуйста почему нельзя сделать так,как в приложеном файле??Дело в том,что у меня уже выбирается по признаку Журнал итогов с итоговой строки Журнала учета.Я хотела так же сделать и Склад,но не работает(((.Если не получается,то буду д...

Re: Виртуозы ПОИСКПОЗ
2009-05-15 22:27
Извините,файл забыла прикрепить

Re: Расчет оптического сплиттера
2009-05-15 22:28
Получилось так, но ответ не совпадает с Вашим

Re: Виртуозы ПОИСКПОЗ
2009-05-15 23:15
Судя по почерку - Шпеца творение
Там кроме формул имена диапазонов нужно смотреть, честно говоря глубоко не вникал, но судя по всему у Вас возникла загвоздка с определением позиций единичек. Немного подправил, дальше думаю разберетесь

Re: Виртуозы ПОИСКПОЗ
2009-05-15 23:27
Да ЕГО, ШПЕЦА творенье Спасибо сейчас посмотрю.

Re: Виртуозы ПОИСКПОЗ
2009-05-15 23:32
А надо ли вручную определять границы заказов и расставлять единицы?
Посмотрите приложенный файл - не то ли это, что Вам нужно?

Re: Виртуозы ПОИСКПОЗ
2009-05-15 23:52
хм...очень даже может быть,так даже будет граммотней..Вопрос надобности журнала Расхода мат отпадает...Мне только не понятен столбец Цвето заказ.Как он вычисляется?Номер заказа+цвет?Чтоб определить цветность?Спасибо .

Re: Виртуозы ПОИСКПОЗ
2009-05-15 23:58
Спасибо,я сама разобралась со столбцом Цвето Заказ.Спасибо,я поняла,наверно это лучшее решение!.Спасибо!

Re: Расчет оптического сплиттера
2009-05-16 02:56
Огромное спасибо! То что нужно, по поводу небьющихся ответов с оригиналом узнаю у человека что их решал.

Re: сумма ячеек в столбцах через 1 или 2
2009-05-16 13:26
а можно несколько подробнее?

Re: сумма ячеек в столбцах через 1 или 2
2009-05-16 16:59
Напиши, что не понятно

Re: Списки в Excel
2009-05-16 20:09
Прошу помощи профессионалов!!!
Во вложении по возможности объяснил проблему.
Понимаю, что нужно присваивать имена, но не знаю как автоматизировать присвоение имен.
Сложность в том, что нужно ссылаться на лист со штаткой, которая частенько меняется. Буд...

Re: сумма ячеек в столбцах через 1 или 2
2009-05-17 01:40
если честно-то все).. а если серьезно-я так полазила по нету-вот то что мне нужно имеется как часть сводной таблицы.. я не особо сильна в премудростях екзела. и постигаю их по мере появления задач. в сводной таблице на моем этапе пока нет нужды. но за ...

Re: Списки в Excel
2009-05-17 09:03
Правильно ли я понял, что на листе TDSheet присутствует таблица подразделений, [url=http://msexcel.ru/content/view/22/2/]структура[/url] которой всегда одинакова:
- во втором столбце указывается название подразделения;
- ниже в строках в 12-ом столбце перечисляются сотрудники подразделения (переменн...

Re: Списки в Excel
2009-05-17 09:33
Структура всегда одинакова, но пустые строки в 12-м столбце со временем могут появиться (сейчас все вакансии сокращены, но пройдет время ...)
Начальник подразделения как правило верхний(в случае назначения нового начальника, может сместиться в самый ни...

Re: Списки в Excel
2009-05-17 10:43
hammer7
Попробуйте в книге создать имя (вкладка Формулы-Определенные имена-Присвоить имя), например "начальник" с формулой:
=ИНДЕКС(TDSheet!$B:$D;ПОИСКПОЗ("*Директор департамента*";TDSheet!$B:$B;0);3)
А на листе "Опись" в любой ячейке ввести фор...

Re: Работа программы на VBA в Office 2007
2009-05-17 10:46

Re: Списки в Excel
2009-05-17 11:10
Спасибо, kitman, все правильно работает, но этот начальник должен сам выскакивать в столбце Р листа Опись! и ФИО этого начальника должна быть связана со столбцом О этого же листа.
Кроме Директора департамента есть еще управления, отделы, службы и т.д. ...

Re: Работа программы на VBA в Office 2007
2009-05-17 11:25

Re: Списки в Excel
2009-05-17 13:02
В принципе, задача сводится к:
1. Создать выпадающий список в столбце О, выбрав из листа со штаткой(столбец А) только текстовые значения, без пустых строк и числовых значений.
2. Автоматизировать присвоение имен [url=http://msexcel.ru/content/view/22/2/]группа[/url]м ячеек(подчиненных сотрудников), ...

Макрос
2009-05-17 16:19
Доброго выходного дня всем!

Надеюсь на вашу помощь, ситуация такая:
Как сделать макрос таким образом, чтобы он:
1. Генерировал случайным образом 1000 значений (я так понимаю, что это случмежду (...;...))
2. Копировал первое сгенерированное число в ячей...

Выгрузка проводок из 1Сv.8
2009-05-17 17:40
Добрый день, уважаемы форумчане.
Когда-то в статье про [url=http://msexcel.ru/content/view/34/2/]сводные[/url] таблицы http://msexcel.ru/content/view/34/101/ я давал ссылку на свою обработку для 1Cv.7.7 для выгрузки проводок в текст или xls. Вот ее адрес: http://msexcel.ru/1c2excel.zip
В последнее в...

Re: Списки в Excel
2009-05-17 19:20
Посмотрите вложенный файл - комментарии внутри.

Re: Макрос
2009-05-17 22:49
А зачем для всего этого писать макрос? Если я правильно понял постановку задачи, то это вполне реально сделать формулами. Для этого желательно видеть сам файл.

Re: Списки в Excel
2009-05-17 22:54

Re: Макрос
2009-05-17 23:03
Во вложенном файле это лист "Стресс-тестирование 1". То есть должна генерироваться цена ВС в диапазоне от (1 000 000 000; 1 800 000 000), после сгенерированная цена должна попадать на лист "факторы модели" в строчку "цена продажи" (b16), соответственно...

Re: Списки в Excel
2009-05-17 23:25
Зеленый столбец придется оставить на листе Журнала - иначе не будет гарантии, что охвачены все строки журнала.

Я честно не вижу подчиненных у Шаврина, но Вам, разумеется виднее

В случае вакантной начальственной должности желательно сохранить строку ...

Re: Списки в Excel
2009-05-18 00:05
Шаврин, по-хорошему, должен быть в 37 строке, сдвигая все остальные вниз

Re: Списки в Excel
2009-05-18 00:13

Re: Списки в Excel
2009-05-18 01:15
Ответил по почте.
Чтобы не возникало такой ошибки формулу в последнем столбце ДопТаблицы
=ЕСЛИ($A2="";"";СЧЁТЗ(СМЕЩ(Журнал;$A2;3;ЕСЛИ(ЕЧИСЛО($A3);$A3;ЧСТРОК(Журнал))-[color=b...

Re: Списки в Excel
2009-05-18 01:37
2 Waalan

Если в дополнительном столбце на листе Диспетчер вы сложите числа:
=E4+(10-F4)*10^-2+СТРОКА()*10^-5
или: =дата_заказа + (очередность 3 => 2 => 1=> пустой)*10^-2 + Номер_строки*10^-5
То в новом столбце появяться числа [пока не] отсортиро...

Re: Макрос
2009-05-18 05:10
Формулами слишком муторно, будет очень много имен.
Получися такой макрос. Особо причесывать его не стал, нет времени.

Re: Количество знаков у текста
2009-05-18 08:07
Спасибо Вам просто огромное-преогромное!

Я догадывалась, что это можно сделать с помощью формулы, но не знала какой.

Очень благодарна!

Re: Списки в Excel
2009-05-18 10:46

Re: Списки в Excel
2009-05-18 11:36
Как сделать что бы при изменении заголовка списка подставлялось определёное значение в формулу?

Re: сумма ячеек в столбцах через 1 или 2
2009-05-18 12:27
огромное спасибо! я реально торможу после этой работы, сорри)) я не увидела прикрепленный вами  файлик-образец.)) все получилось.. просто подогнала под свои ячейки. KISS

Re: Макрос
2009-05-18 12:29
Спасибо преогромнейшее!
И не ожидала такой помощи, в итоге в двойне рада

Защита и группировка
2009-05-18 14:14
Привет, всем!
Подскажите выход.   
На листе сгруппированы строки. Нужно защитить лист так, чтобы не отображались формулы в ячейках и нельзя было ничего удалить с листа, но при этом сохранялась возможность просматривать сгруппированные ячейки.
Приме...

Re: Списки в Excel
2009-05-18 17:01

выдает ошибку при вычислении
2009-05-18 18:28
таблица баланса. на каком-то этапе общий итог выходит в таком виде "-2.9E-11". что это за зверь? и как его избежать?

Re: выдает ошибку при вычислении
2009-05-18 18:58
Это число -0,000000000029. Подобные стремящиеся к нулю числа появляются в Excel из-за неточности преобразования десятичного числа в двоичное и наоборот. Способ лечения - использовать при бухгалтерских расчетах функцию
ОКРУГЛ(выражение;2)

Re: выдает ошибку при вычислении
2009-05-18 19:09
Здесь был спам...

Re: выдает ошибку при вычислении
2009-05-18 19:13
И что это?

Re: Списки в Excel
2009-05-18 19:40
да спасибо

Re: Списки в Excel
2009-05-18 20:33

Re: Списки в Excel
2009-05-18 23:56

Re: Защита и группировка
2009-05-19 02:36
1. При защите сгруппированных ячеек Вы не сможете работать с группировкой (она будет неактивной).
2. Для защиты ячеек и скрытия формул следующие шаги:
 2.1. выделить лист, ПРАВОЙ КНОПКОЙ - формат ячеек, закладка - ЗАЩИТА, далее ставим галки в "Защищаем...

Изменение кода столбцов на буквенные!!
2009-05-19 04:44
Добрый день!
Обычно код строк идет 1,2,3,4,5.... код столбцов: A,B,C,D,E....
а у меня в паре документов коды столбцов: 1,2,3,4,5...  Причем, неважно на каком компе открываешь, все равно так...(((
В самом Екселе не нашла.
Будьте добры, выручите!!?

Re: поиск решения
2009-05-19 10:07
Я не мог найти причину ошибки. Обясните пожалуйста, что не так, если не трудно.

Re: Количество знаков у текста
2009-05-19 11:03

Re: Защита и группировка
2009-05-19 11:43
Неужели, правда нет способа обойти это так, чтобы скрыть формулы и запретить удаление, но оставить группировку активной??
Кстати, еще вопросик в то же болото: есть ли способ настроить защиту так, чтобы она включалась по условию. Например защищаемым...

Re: Количество знаков у текста
2009-05-19 12:01
Чтобы изменять цвет шрифта в ячейке заходим во кладку "Формат" выбираем "Условное форматирование", далее задаем условие: значение больше 75, потом  форматирование. В конце применяем "формат по образцу" ко всему требуемому диапазону.

Re: Количество знаков у текста
2009-05-19 12:06
Спасибо огромное! Здорово! Я очень благодарна!

Re: Изменение кода столбцов на буквенные!!
2009-05-19 13:00
 в 2003м экселе. Сервис - Параметры, вкладка "Общие". галка "Стиль ссылок R1C1"

в 2007 скорее всего аналогично

как произвести замену данных в таблице при выборе значений из списка?
2009-05-19 13:59
Здравствуйте!
требуется: при выборе продавца из раскрывающегося списка, данные таблицы (стоимость) менялись на данные таблиц продавцов. Пример таблицы в приложении.

маска в формате ячейки
2009-05-19 14:13
Здравствуйте
Как удалить или отключить маску в формате ячеек. Пример: при вводе 02.02 показывает 02 фев.

Re: Помогите пожалуйста с поиском оптимального решения!
2009-05-19 15:39
Народ! У меня такой вопрос.
Знаете как сделать, чтобы сохранить все поиски оптимального решения, в том случае если на листе этих поисков несколько? Целевые ячейки и ячейки ограничений у всех поисков разные. Как бы шаблоны сделать в случае если значени...

Re: маска в формате ячейки
2009-05-19 16:13
Выделить ячейку, формат ячейки, число, текстовой (или любой другой)

Re: как произвести замену данных в таблице при выборе значений из списка?
2009-05-19 16:24
Вот смотри. Но этот пример при помощи простейших функций. При увеличении таблицы он не подойдет т.к получится большая формула. Писал новичек с ограниченным знанием функций так что не обессуть.

Re: как произвести замену данных в таблице при выборе значений из списка?
2009-05-19 20:01
Если не удалять из списка пустые значения, то так. Если удалять - то нужно делать отдельный массив для списка. Это не сложно, но вроде и так нормально.

как из даты преобразовать в месяц. это не глупость, а действительно проблема:)
2009-05-19 20:27
Добрый вечер! очень прошу помощи... Мне необходимо, ссылаясь на дату в ячейке, получать месяц. Но обыкновенная смена формата мне не подходит, т.к. потом я составляю сводную таблицу, и там он опять разделяет данные по датам, а мне необходимо по месяцам....

Как определить что динамический массив пуст ?
2009-05-19 20:29
Как определить что динамический массив пуст ?
Использование функции Ubound на пустом динамическом массиве даёт ошибку в ситуации когда массив после иннициализации ни разу не использовался. 

Возможность действий над ячейками из пользовательской функции
2009-05-19 20:39
Обнаружил что из пользовательской функции нельзя например раскрасить нужную ячейку, да и вообще как либо их изменить.
Как обойти эти ограничения ?
Как вариант придумал что можно информацию по изменяемым ячейкам (адресса,значения) передавать через стати...

Re: как из даты преобразовать в месяц. это не глупость, а действительно проблема:)
2009-05-19 21:02
=МЕСЯЦ(A1)

если надо все время получать первый день месяца, то
=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

Re: как из даты преобразовать в месяц. это не глупость, а действительно проблема:)
2009-05-19 21:36
Спасибо огромное:) Море благодарностей.

Re: Работа программы на VBA в Office 2007
2009-05-19 22:15

Определение переменных
2009-05-19 22:28
Прошу помощи Знатоков.
В макросе объявлены переменные(заголовки колонок), а их значения находятся на листе книги(две строки: одна с именем переменной, другая с значением, т.е. номером столбца) как в макрос передать значения переменных, т.е. найти имя п...

Re: Защита и группировка
2009-05-20 00:35

Re: маска в формате ячейки
2009-05-20 05:51
Все равно  меняется на формат маски.

Re: как произвести замену данных в таблице при выборе значений из списка?
2009-05-20 11:03
КЛАСС!!!

пробую оба варианта ...

спасибо!

Re: маска в формате ячейки
2009-05-20 12:04
Попробуйте перед первым символом ввести апостроф - ' (находится на клавише с буквой Э в английском регистре), то есть '02.02
Или, как написал _Boroda_, предварительно, перед вводом данных в ячейки, выделить весь диапазон, куда будет производитьс...

Re: маска в формате ячейки
2009-05-20 12:06

Все равно  меняется на формат маски.

Re: маска в формате ячейки
2009-05-20 14:05
Если не получится - скидывай файл

Re: Определение переменных
2009-05-20 17:52
[code]Sub Макрос1()
Dim FoundRng As Range
Dim iValue As Long
       
    'осуществляем поиск по всему листу текста "Заголовок столбца"
    Set FoundRng = Cells.Find("Заголовок столбца", , LookIn:=xlFormulas, LookAt:=xlWhole) 'xlWhole - ячейка целиком
...

Запуск какой либо Windows-программы из Excel средствами VBA
2009-05-21 11:08
Подскажите пожалуйста, каким образом можно запустить из Excel какой-нибудь файл, например файл справки chm, или exe-файл, в ответ на какое-либо событие, например нажитие кнопки.

Re: Работа программы на VBA в Office 2007
2009-05-21 11:38
Большое спасибо за помощь. С запретом запуска я уже разобрался, правда немного по другому.
На одной версии Excel 2007 я испытал, работает хорошо:
[code]If Val(Application.Version) > 11 Then ' Проверяем версию Excel
    MsgBox "Программа не поддерживае...

Re: сумма ячеек в столбцах через 1 или 2
2009-05-21 13:31
продолжение по теме.
с общей суммой данных одноименных ячеек разобралась. усложняем задачу. возможно ли в пределах одной ячейки разместить разность сумм данных одноименных ячеек?? смущает на что должна ссылаться эта функция..
заранее спасибо, Вы...

изъятие значения из листа с БД
2009-05-21 15:13
Добрый день!!!
прошу подсказать или направить в правильное русло
есть прайс лист к нему лист с БД
в прайс листе находятся поля с наименованием товара в соседней ячейке количество, в зависимости от количества товара меняется цена товара.
БД содержит
на...

Re: изъятие значения из листа с БД
2009-05-21 15:24
Посмотрите здесь: Использование функции ВПР (VLOOKUP) для подстановки значений
Там внизу статьи имеются ещё ссылки по теме.

Re: сумма ячеек в столбцах через 1 или 2
2009-05-21 15:51
Приложи конкретный файл

Re: сумма ячеек в столбцах через 1 или 2
2009-05-21 16:02
прилагаю.. хотелось бы в обход ежедневного подсчета прибыли одним разом считать общую прибыль за весь период

сортировка листов
2009-05-21 16:17
Есть макрос:
Sub сортировка()
    Dim i As Integer, J As Integer

    For i = 1 To Sheets.Count - 1
        For J = i + 1 To Sheets.Count
            If UCase(Sheets(i).Name) > UCase(Sheets(J).Name) Then
                Sheets(J).Move Before:=Sheets(i)
...

Re: изъятие значения из листа с БД
2009-05-21 16:44
спасибо заработало!!!

Re: сумма ячеек в столбцах через 1 или 2
2009-05-21 19:28
Конечно возможно.
Лови.

Re: Запуск какой либо Windows-программы из Excel средствами VBA
2009-05-21 20:50
Вешаем на кнопку CommandButton1

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByDirectory As String, _
ByVal nSh...

Re: сортировка листов
2009-05-21 22:02
Попробуйте так

[code]Sub сортировка()
Dim i As Long, J As Long
 
    On Error Resume Next
    For i = 1 To Sheets.Count
        For J = i + 1 To Sheets.Count
            If CLng(Sheets(i).Name) > CLng(Sheets(J).Name) Then
                Sheets(J).Move...

Re: Запуск какой либо Windows-программы из Excel средствами VBA
2009-05-21 22:05
Ещё есть команда Shell

Call Shell("Calc.exe", 1)

Re: Запуск какой либо Windows-программы из Excel средствами VBA
2009-05-21 22:07
Всем большое спасибо, уважаемые!

Re: Запуск какой либо Windows-программы из Excel средствами VBA
2009-05-21 22:11
Shell у меня почему-то не хочет работать. Из-за этого и городил такой огород

Нахождение суммы столюца по заданному фильтру
2009-05-22 00:44
в общем прошу не ругать, юзал поиск не нашел снова ничего.
Суть проблемы: есть сводная таблица нагрузки на листе "Заочка" (см. вложенный файл)
Ассистент вбивает данные по всем преподам на лист "Исходные". На "Заочке" все считается, и получается преподы...

Re: сумма ячеек в столбцах через 1 или 2
2009-05-22 02:42
могу сказать одно-очень красиво! остальное восторг! вы мой ангел-хранитель)))

Re: сумма ячеек в столбцах через 1 или 2
2009-05-22 02:48
Как в моем любимом мультфильме:
Ну, ты, это, заходи, если шо.

Re: Нахождение суммы столюца по заданному фильтру
2009-05-22 03:57
2 способа:

1). Выбираешь в сиреневой ячейке из выпадающего списка фамилию - в строке 2 получаешь результат по этой фамилии.
 Список автоообновляется из листа "Исходные", в котором можно выбрать введенное ранее значение или завести новое вручную.

2) Выб...

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