Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 След.
Два вопроса по VBA . ( TextBox, lLastRow), VBA
 
Доброго времени суток!
У меня похожая проблема, не могу найти решение, при копировании ячеек со значениями все копипастится как в исходнике, а вот при обработке макросом, все из скопированных значений конвертируются в числовые значения и формат ячеек автоматически меняется из общего в числовой. Добавлять апостроф не хочу, т.к. при переносе инфы в другие источники он сохраняется, переводить формат ячеек в текстовый можно, но тоже не хотелось бы (ведь Excel же хранит текстовые данные в общем формате, хотелось бы получить в итоге информацию абсолютно идентичную исходной). А метод Text к Cells у меня вообще выдает ошибку 1004 "нельзя установить метод класса Range". И присвоение формата "Общий(General)" тоже не дает эффекта (применять "до" бессмысленно, применять "после" уже поздно, текст уже конвертируется в число.
Может кто знает решение?
Перебор справа налево, Отрезать кусок текста в ячейке после последней точки в ячейке
 
Видел как-то давно подобный вопрос, порылся у себя в архивах ))
смотрите столбцы (E,F). Логика построена на последней точке, если вдруг получится название содержащее в себе точку, будет крах логики )))
Присвоение переменной имени листа VBA
 
Юрий М, Спасибо огромное! то, чего не доставало самостоятельно нашел на других форумах благодаря знакомым уже пользователям (Ikki), итак мой кусок кода теперь выглядит сл. образом (для информации другим гостям этого форума):
Код
Set WBA = ThisWorkbook
    Set WS1 = ActiveSheet
    Set WS2 = Choose(Right(WS1.CodeName, 2), _
    Month_01, _
    Month_02, _
    Month_03, _
    Month_04, _
    Month_05, _
    Month_06, _
    Month_07, _
    Month_08, _
    Month_09, _
    Month_10, _
    Month_11, _
    Month_12)
само-собой это лишь малая часть кода, но теперь можно переименовывать листы в книге пользователям без опаски сбить код, обновленный код протестил, работает как часики.
Hugo, тоже огромное спасибо, но я еще "очень новичок" в VBA, и многого еще не понимаю и не знаю, но желание разбираться есть, так что разбираться буду. Не совсем понял в вашем коде, что и куда, как дойду знаниями до нужного уровня разберусь )). Быть может я не совсем ясно задачу поставил, суть в том что (на примере того же декабря) у меня в "АО 12" собраны и отсортированы из общей базы ("12") нужные мне данные в зависимости от той недели за которую я составляю отчет, но не все данные для работы кода есть на данном листе, чтобы излишне не грузить книгу формулами (вес ее увеличивается с каждой формулой) мне проще часть данных взять с активного листа ("АО 12"), а недостающие данные взять с исходного листа соответствующего месяца ("12") и потом все это перенести в другую книгу-шаблон отчета именно поэтому я и хотел присвоить переменной WS1 свойства и методы активного листа, а WS2 ориентируясь на два последних знака активного листа определить нужный мне месяц и также присвоить все свойства и методы, чтобы обращаться уже к переменным а не к листам в коде.. Мне остается потом уже в автоматически сохраненном отчете в нужное мне место и с нужным мне именем добавить немного данных с внутренней программы и все, то что раньше я копипастил вручную с двух листов, распределял где профильное где общее в течение часа, теперь у меня делается автоматически в течение нескольких секунд и корректируется еще в течение 5-10 минут. ))) Чего-то я отвлекся... В общем спасибо всем за помощь! Не раз уже убеждался что форум полезный, спасибо и создателю этого сайта!
Присвоение переменной имени листа VBA
 
Всем привет, мой вопрос будет все-таки в тему, поэтому пишу здесь.
У меня раньше в книге было 24 листа (12 месяцев и 12 автоотчетов по месяцу) назывались они попарно "12" и "АО 12" (на примере декабря). Раньше у меня для каждого листа был прописан свой код, по которому я работал с двумя парными листами, сейчас же когда я сделал книгу на новый год мне показалось неудобным хранить 12 идентичных кодов и я немного перепилил его под один общий, теперь мой код ориентируется с какого активного листа я его запускаю (по кнопке) и с какого парного ему листа-месяца подтянуть недостающие данные.
Пример куска кода:
Код
Set WBA = ThisWorkbook
Set WS1 = ActiveSheet
Set WS2 = Choose(Right(WS1.Name, 2), _
    WBA.Sheets("01"), _
    WBA.Sheets("02"), _
    WBA.Sheets("03"), _
    WBA.Sheets("04"), _
    WBA.Sheets("05"), _
    WBA.Sheets("06"), _
    WBA.Sheets("07"), _
    WBA.Sheets("08"), _
    WBA.Sheets("09"), _
    WBA.Sheets("10"), _
    WBA.Sheets("11"), _
    WBA.Sheets("12"))
Теперь вопрос, если использовать внутреннее имя листа (codename) как присвоить его переменной, или как присвоить переменной все свойства и методы листа по его внутреннему имени? Дело в том, что начальник просит теперь внедрить мою книгу в отдел всем коллегам, т.к. делать недельный отчет за час или за 5 минут есть существенная разница, а я боюсь того момента что хоть я и предупрежу всех не переименовывать листы книги, кто-нибудь да забудет и переименует (можно конечно поставить защиту, но хотелось бы код перепилить, вдруг еще что вылезет?)
Изменено: GeorgeDark - 26 Янв 2016 04:43:02
Автоматическое формирование номера приказа в зависимости от даты, циклическая ошибка, возникающая только после 5 повторений
 
Хм, возможно Вы сейчас нашли более краткое и лаконичное исполнение моей перегруженной логики, честно сознаюсь я так не умею, я зачастую хожу дремучими путями, меня и учителя ругали за такое неумение упрощать и умение наоборот простое усложнять ))).
я поменял три куска первый в первой и четвертой формуле, а второй и третий в соответствующих формулах:
1) ЕСЛИ(МАКС(K$2:K2)<9;"0"&(МАКС(K$2:K2)+1)&"-Ф";(МАКС(K$2:K2)+1)&"-Ф") на двойную логику (сначала для цифр меньше 9 с двумя ноликами, а затем уже для остальных меньше 99) ЕСЛИ(МАКС(K$2:K2)<9;"00"&(МАКС(K$2:K2)+1)&"-Ф";ЕСЛИ(МАКС(K$2:K2)<99;"0"&(МАКС(K$2:K2)+1)&"-Ф";(МАКС(K$2:K2)+1)&"-Ф"))
2) ЕСЛИОШИБКА(ЗНАЧЕН(ЛЕВСИМВ(J3;2));"") на ЕСЛИОШИБКА(ЗНАЧЕН(ЛЕВСИМВ(J3;3));"")
3) ЛЕВСИМВ(J3;2) на ЛЕВСИМВ(J3;3)
Т.е. это так как я и задумывал свою логику))) но альтернативные решения всегда интересны.
PS: Приятно конечно слышать, но я далеко не гений, мне по знаниям и умениям далеко до людей которые живут на данной "планете", я лишь учусь.
Автоматическое формирование номера приказа в зависимости от даты, циклическая ошибка, возникающая только после 5 повторений
 
Чет я кажется опять перемудрил, попытался разобраться в формулах автора, надоело ))) решил свою логику от дат прописать, т.е. если текущая дата больше или равна максимальной из предыдущих, тогда по-порядку, а вот если меньше чем максимальная, тогда нужно задним числом через слеш-новый порядок, только у меня все заточено под двухзначные числа, если число по изначальному порядку перевалит за 100, нужно будет редактировать формулу чуть-чуть под трехзначные числа для красоты. Основная формула с правильной нумерацией в желтых ячейках, те, что правее - вспомогательные (хотя последняя и похожа на первую, но все же решил не нагружать и без того нагруженную формулу еще более мощной логикой.
Изменено: GeorgeDark - 20 Янв 2016 20:03:34
Закрашивание ячеек не попавших в заданный интервал
 
Rih, Можно, если допустим интервал прописать для каждой строки в столбцах правее, а формулу для условного форматирования примерно как написал Sanja, только вместо констант сослаться на ячейки с указанным интервалом, тут главное с абсолютностью ссылок не запутаться, а правило будет одно общее.
Макрос разделяющий данные
 
Доброго времени суток! Посмотрел пример, ничего не понял, по какой логике разделять? Помните то, что очевидно для Вас, для людей впервые смотрящих на данные вряд ли будет столь же очевидным пониманием. Хоть бы цветом покрасили, и объяснили по какому принципу идет разделение.
Макрос на заливку ячеек цветом
 
Цитата
Сергей Самсонов написал: Я так понимаю это можно сделать только макросом
Доброго времени суток! Не только макросом, насколько я понимаю из описанных требований несколько простых правил условного форматирования тоже можно применить.
Сравнить поля из двух диапазонов, при совпадении вывести третий
 
Мое мнение что нужно вбить в пятый столбец формулу наподобие =ЕСЛИОШИБКА(ИНДЕКС($D$1:$D$100;ПОИСКПОЗ(A1;$C$1:$C$100;0));B1)
Пример не качал лень было, но суть в том что если он находит позицию из первого столбца в третьем то подставляет значение из четвертого, если такой позиции не находится, то возвращает значение из второго. Потом можно просто скопировать пятый столбец и из меню вставки -> "вставить значения" туда куда нужно.
Условное форматирование в сводной таблице
 
Простое правило, основанное на функции ОСТАТ.
Смещение значений через два столбца
 
Вроде так:
Код
=СМЕЩ(Лист1!$A$1;0;((СТОЛБЕЦ(A1)+2)/3)-1)
Присвоить повторяющейся ячейке значение
 
Формула в столбце С, ищет совпадения ранее, если есть, то ищет значение из столбца В по совпадающему значению по столбцу А (по первому найденному совпадению), в противном случае копирует значение из В по строке.
Процент выполнения плана с отрицательными и положительными значениями
 
Не знаю что и как неправильно, написал свои формулы (не подглядывая в ранее написанные) и у меня получилось вот что (зеленым покрасил расхождения со столбцом D). Если что-то неправильно считается напишите по какому алгоритму вы считаете на калькуляторе, проверю, поясню )
Изменить цвет в соответствии со значением выше
 
kosar624, выложите пример с неработающим правилом УФ, посмотрим, подскажем. Возможно создано еще одно перекрывающее правило на эту ячейку.
Если наступил месяц оплаты, то добавить платеж в задолженность
 
Если правильно понял, задолженность считается включительно по месяц оплаты, как только месяц заканчивается оплата в этом месяце минусуется с задолженности так?
Тогда в формуле нужно было в функции ДВССЫЛ двойку +2 для столбцов заменить на +1...
Формула расчетного остатка на начало месяца
 
Цитата
bhelen_1988 написал:
не пойму зачем эта формула. к чему она?
Ну это формула к тому, о чем говорил Максим Зеленский, т.е. она проверяет настал ли новый месяц или еще идет текущий, ее потом можно будет как составную часть к основной прилепить.
Формула расчетного остатка на начало месяца
 
Ну это примерно то, о чем говорил Максим, в ячейке А1 стоит дата отчета, в А10 я прописал формулу которая на данный момент ссылается на ячейку Е1 (в которой написан месяц). Если в Е1 написать Апрель, то в А10 напишется "Формула3".
PS: создал в диспетчере имен именной диапазон "Месяцы".
Изменено: GeorgeDark - 27 Мар 2015 17:22:02
Формула расчетного остатка на начало месяца
 
Ну не знаю правильно ли это будет, но вот формула, как говориться просите помочь, помогаю )
остатки на начало в обоих случаях беру плановые, т.к. факт. остатка на начало нет (его конечно можно высчитать ост.на конец+расх-приход (все по факту)).
Код
=ЕСЛИ(ИЛИ(F3="";F3<=0);ЕСЛИ(B3<0;0;B3)+J3-C3;ЕСЛИ(B3<0;0;B3)+T3-F3)
Но хотелось бы еще раз напомнить, что формулы там считают правильно, просто неверно задается план продаж, посмотрите на ситуацию с генератором 3 по февралю месяцу остаток на начало (факт) 147 ожидаем поставок 0 планируем продать 150 неудивительно что при таких планах он расчетный остаток на конец выдает -3. Еще раз повторюсь что при таких раскладах нужно прикрутить графы необх.запас и заказ, или в заказ привязать формулу которая будет формировать его отталкиваясь от необх. запаса и расч. остатка, т.е. чтобы обеспечить запас в 324 при выполнении плановых показателей, то необходимо от 324 отнять кол-во по расч. остатку 324-(-3) = 327 штук нужно заказать.
Формула расчетного остатка на начало месяца
 
Цитата
bhelen_1988 написал: 1) если факт равен пустому значению, пустой ячейке
факт чего? продаж?
Формула расчетного остатка на начало месяца
 
Цитата
Максим Зеленский написал: конец-факт продаж+факт приход??? смысл?
Вообще я честно говоря не понял bhelen_1988, "если факт равен пустому значению то считаем план продаж" факт чего продаж или остатка? если продаж то фигня получится 17-21=-4. Если факт остатка, то еще куда ни шло 23-21=2. Поэтому просто проставил формулу: если по факту остатков нет то план остатков на начало + приход план - расход план. В противном случае факт остатка на конец + факт прихода - факт расхода (я не знаю для чего, подумал просто, что автор сам подставит в формулу нужные ему ссылки))).  
Формула расчетного остатка на начало месяца
 
Ну это легко:
Код
=ЕСЛИ(ИЛИ(E3="";E3<=0);ЕСЛИ(B3<0;0;B3)+J3-C3;E3+T3-F3)
Если по факту остатки меньше или равны нулю или пустые то расчет ведем по плановым показателям, в противном случае по фактическим.
PS: посмотрел дальше формулу на февраль, там все тоже рассчитано верно, отсаток на начало по факту 17, планируем продать 7 шт, планируемый остаток на конец = 10, именно такое значение и выдает формула в AT.
Изменено: GeorgeDark - 27 Мар 2015 13:26:47
Формула расчетного остатка на начало месяца
 
Ну тогда понятно почему расчетный остаток выдает ноль, смотрите если на конец осталось 17, не было прихода (и не ожидалось) был расход 4 то остаток на начало января был 17+4=21 шт, а в плане продаж как раз и стоит 21, т.е. на начало января планировали продать все под ноль и при этом не ожидалось поставок. Есть еще такие столбцы как "необх. запас" и "заказать" там странные цифры стоят (с половинками), можно только их докрутить в формулу по логике если планируем продать все под ноль, и обеспечить необх. запас, то нужно заказать кол-во этого необх. запаса (хотя цифры там стоят нелогичные), тогда и расчетный остаток будет высчитан на сумму необх. заказа.
Формула расчетного остатка на начало месяца
 
Цитата
bhelen_1988 написал:
остаток  на кон января 17 шт
Это правда? Потому что по логике формулы это остаток на начало января.
Изменить цвет в соответствии со значением выше
 
Ну таких три правила УФ (если вы решили формулой)
Формула расчетного остатка на начало месяца
 
Это чисто случайное совпадение как я понял. Просто так совпало что план продаж минус факт продаж вышел на факт. остаток (21-4=17). По сути формула верная.
Ошибка аналитика в следующем: Если остаток по факту равен 17 а планируем продать 21 то должны запланировать также и приход (недостающих 4 штуки) а план прихода равен нулю (также как и факт), если же смотреть на расчетный остаток 23 штуки, то запланировать должны не менее 6 штук! (поставьте в J3 =6, и у вас расчетный остаток станет 6 [ФО (17) + разница между плановым и факт. приходом (6-0) - разница между плановым и факт. расходом (21-4) отсюда 17+(6-0)-(21-4)]). Поставьте ради интереса план продаж меньше и у вас расчетный остаток возрастет. Дальше файл не рассматривал, потому как считаю что формула все же верная.
PS: А пришли вы все-таки на форум по экселю а не по планированию, поэтому понятно что желающих помочь не так много, если бы вы указали в каком месте вам нужно что сложить что вычесть и что получить, вам бы сразу помогли. А так, Вы говорите что формула некорректно считает, но при этом не знаете как она должна считать, откуда тогда уверенность в ее некорректном счете?
Изменено: GeorgeDark - 27 Мар 2015 07:41:51
Расчет себестоимости единицы реализованного товара по методу FIFO
 
Точно, не учел возможность нулевых остатков на начало периода, добавил условие в столбце Q (проверяет если в столбце R ноль, ставит "пусто"), т.к. столбец R это накопительный приход, то в середине столбца после цифровых значений ранее нули не могут возникнуть по определению, отсюда ошибок быть не должно с правильной нумерацией. Также поменял значение ячейки U3 с единицы на число чуть больше нуля, но так чтобы было присутствие нижней границы при реализации малого кол-ва (0,01 и менее).
PS: Это конечно уже не мое дело, пример есть пример, но как так могло получиться что при нулевых остатках на начало, приход по 30370,34 а расход тут же по 27850,00 ? :)
Видимо, неправильно расставлены знаки при определении условий ЕСЛИ
 
Ну и собственно почему ваша формула не работала:
Лишние кавычки, неполное условие для функции И(). Цифры в кавычках преобразуются в текст, поэтому если вам нужно с ними выполнять математические операции, то кавычки тут совсем не нужны )
Код
=ЕСЛИ(A1>=1000;0;ЕСЛИ(И(A1>=600;A1<1000);1000-A1;A1/100*60))
Расчет себестоимости единицы реализованного товара по методу FIFO
 
Ну сейчас у меня уже нет времени вникнуть в Ваш третий вопрос, но хотя бы отвечу на первых два:
1) Среднюю себестоимость с учетом каждой новой поставки мы высчитываем потому что она должна меняться в зависимости от новой итоговой суммы, смотрите:
         Кол-во   Цена   Сумма   Средняя цена
Ост    10          25       250        25
Прих  10          15       150        15                    (итого получится кол-во 20 сумма 400  средняя цена уже 20)
Прих  10          50       500        50                    (итого получится кол-во 30 сумма 900  средняя цена уже 30)
Теперь мысленно прикинем расход по кол-ву в период между первым приходом и остатком ср. цена была 25 сколько бы мы не списали товара его цена была бы 25 например списали 5 ед-ц товара по 25 руб. = 125, и останется у нас кол-во 5 сумма 125 (средняя цена остатка 25, все верно не так ли?) Теперь спишем товар между двумя приходами для удобства 10 ед-ц (первый расход мысленно удаляем чтобы не путаться) итак после первого прихода у нас на остатке 20 штук на сумму 400 (ср. цена как помним 20) отсюда десять штук списываем по 20 руб итого 200 400-200 = 200 делим на 10 штук остатка получается 20 руб опять все верно не так ли? Ну и возьмем расход после второго прихода допустим 5 штук мы помним что ср. цена 30 отсюда расход составит 150. считаем остаток 900-150 = 750 кол-во 25 750/25 = 30, опять все верно. Отсюда и получается что каждый приход меняет ср. цену, расход на изменение ср. цены никак не влияет. (так сказать имитация ФИФО).
2) Циклическая ссылка образуется из-за того, что в формуле в столбце J вы используете значение из N а значения в N рассчитываются с помощью I, а I в свою очередь рассчитываются изначально от J, вот и получается цикл.
Расчет расхода материалов в зависимости от цены.
 
Ух, чет мне заняться нечем было и повыделывался тут с формулами... Кажись я опять чего-то перемудрил, объяснить по-порядку вряд ли смогу, переделать что-либо тоже будет тяжеловато )) Но твою ж за ногу работает именно так как и хотел )))
В общем моя хотелка заключалась в том чтобы он выбирал из всего столбца нужные номенклатурники по ним искал и списывал методом ФИФО (учитывая что номенклатурники могут быть вразброс, а не подряд как в изначальном примере). а после списания учитывал новые остатки, для этого чудовища пришлось добавить пару листов, в первом копируются данные из бух. данных и считаются новые остатки, во втором производится списание только оформлено в виде столбцов, чтобы легче было )). При желании в первом листе можно копировать нижнюю строку и размножить ее вниз сколько угодно (все формулы по строке идентичны), а также копировать два последних столбца (один пустой и один с остатками) и размножать сколько угодно вправо. Также во втором листе, только стоит учесть что формулы для расходов по пятой строке отличаются от тех что ниже.
Страницы: 1 2 3 4 5 6 7 8 9 10 11 След.
Наверх