Добрый вечер,

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

В общем, у меня есть таблица с продажами по годам и месяцам. В строках - менеджеры по продажам.

Вполне стандартная история.

Но после этой таблицы мне нужны столбцы с "Лучшим средним за год", с максимальным средним за год, количество месяцев, в которые брал клиент и т.д. И мне это нуджно одним столбцом, без деления на месяцы и годы.

Как это сделать?

Добрый день.

У меня есть книга, в которой при событии Workbook_Open выскакивает InputBox

В зависимости от введенного значения, меняются фильтры (с помощью Select Case).

Я хочу, не открываю эту книгу (пусть будет "книга с паролем"), из другой книги запустить макрос, который открыл бы ее, ввел нужный пароль и потом скопировал.

Основной вопрос - как мне подставить в InputBox книги с паролем нужно мне значение из макроса, который лежит в другой книги?

Добрый день.

Делаю динамическую сводную таблицу для сравнения сумм продаж по месяцам от года к году (Январь 20, 21 и 22, Декабрь 20,21 и 22) и так далее.

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

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

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

P.S. Вообще, в идеале таблица должна выглядеть как в файле ПримерИдеал, но я не знаю, реально ли  это
 

Добрый день.

Делаю автоматическую таблицу, отображающую процентовку брака.
Сделал в сводной под браком и продажами вычисляемый объект, который считал бы процент (БРАК/ПРОДАЖИ*100).

Но проблема! Мне продажи и брак нужны как целые числа, а процент брака - с сотыми долями. Я не могу настроить объект "Процент брака" отдельно от остальных полей.
В Итогах по строке мне нужны сумма про Браку и Продажам, и среднее значение по Проценту Брака.
Также внизу в итогах по столбцу нужны Отдельные сумму по Продажам и Браку за месяц, и расчет общего процента брака *Суммарный Брак / Суммарные продажи * 100)

И когда готовил пример столкнулся с еще одной проблемой. Я сократил данные в исходной таблице (вкладки Продажи и Брак), но сводная все равно откуда-то подгружает уже удаленную информацию.. обновление не помогает.

Заранее благодарен!

Добрый день!

Есть обычная табличка.
Артикул - Клиент - Количество продаж

Обычная сводная таблица выдаст следующий раскрывающийся список
+Артикул 1          10 шт
----Клиент 1            2 шт
----Клиент 2            5 шт
----Клиент 3            3 шт

А мне надо так
Артикул 1 - Клиент 1 -    2
Артикул 1 - Клиент 2    - 5
Артикул 1 - Клиент 3 -    3

Да, я понимаю, что можно в исходном списке сделать столбец, в котором я СЦЕПЛЮ Артикул и Клиента, и по нему вывести количества в сводной, но неудобен этот вариант.

Как реализовать такое с помощью сводной? Спасибо.

Добрый день.

Периодически сталкиваюсь со следующей проблемой.

Присваю переменной значение:

Workbooks - мы видим там книгу Общие остатки.
Переменные, задающие номер строк и столбцов в объекте Range тоже работают правильно. То есть, адрес ячейки верный, но если я наведу мышку на Cells - они подсвечивают значение этой ячейки, но из другой книги (до этого использовавшейся).

Ну и Object defined Error 1004...

Почему так?

В примере - модуль StockRefresh

Добрый день.

Случилась как-то беда, попал в бесконечный цикл. Вышел с помощью кнопки Break.

Но с тех пор начались чудеса.

Если раньше при возникновении некоторых ошибок макрос не выполнялся - то сейчас он выполняется, и я даже не знаю о существовании ошибок.
Как пример, некорректно указан адрес в Range и присвоение диапазона переменной просто не происходит. Но прога все равно выполняется...

Или я пишу следующее:
Set Specs = Application.Workbooks(UZ).Sheets("Listing").Range(Cells(LastUsedCell.Row + 1, 1), Cells(LastUsedCell.Row + MassivDim + 1, 5))

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

Он по идее должен вставить данные из массива в диапазон после последней заполненной ячейки (а это строка заголовков в первом ряду). Он то правильно вставляет, то на 38 строчку.

Это просто пример, и других глюков полно тоже, я столько времени на это трачу! Устал уже.

Что это за хрень и что делать? Эксель уже переустанавливал с 2010 на 2016. Ничего не изменилось.
Изначально все работало идеально.

Добрый день.

Уже год подгружаю данные из другой таблицы с помощью запроса.

Всегда все было нормально.

Но сегодня что-то случилось. При нажатии кнопки ОБНОВИТЬ ВСЕ пишется "подключение к источнику данных". И так продолжает бесконечно, данные не подгружаются.

В редакторе также невозможно загрузить предварительный просмотр - бесконечная анимация обновления (бегущие кружки).

Ошибок не выдает. Просто бесконечно пытается подключиться к таблице и подгрузить данные.

При этом! Из других таблиц, которые подключены точно таким же образом - все, как и прежде, выгружается. А вот конкретно из этой - нет.
Вручную менял адрес источника данных, перекладывал в другое место - ничего не меняется.

Запрос на подключение выглядит так:

= Excel.Workbook(File.Contents("C:\Users\Иванов\Desktop\LOADING SCHEDULE.xlsx"), null, true)

И вот этот запрос бесконечно выполняется.

В чем может быть проблема?

Добрый день.

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

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

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

Прилагаю два файла: с макросом и источник.

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

Добрый день.

Во вложении файл. На первой вкладке исходные данные. Исходные данные - это некоторая таблица, с которой потом работает другой файл. В этой таблице есть модели товаров (строки с моделями могут дублироваться, различаясь месяцем, в котором будет отгрузка), а также расписание по месяцам, когда товар должен загрузится, когда он загрузился по факту и т.д.

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

Сейчас я решаю задачу подтягивания с помощью промежуточного файла (между файлом исходником и конечным файлом, куда подтягиваются данные), в котором с помощью СУММЕСЛИ по каждому артикулу суммируются значения столбиков по всем месяцам. По определенным причинам этот способ вызывает неудобства



С помощью PQ и функции "Отменить свертывание столбцов" я хочу привести превратить исходную таблицу в такую (образец на второй вкладке), из которой потом можно сделать сводную (примерный образец на третьей вкладке).

И я подобное делал, НО! Исходная таблица имеет такой формат, который я не могу превратить в нужный мне формат (мешает, прежде всего, то, что по каждому месяцу есть три графы: ПЛАН, LOADED и UNLOADED. А заголовок над ними - общий, три объединенных ячейки. При преобразовании в PQ этот заголовок встает над первым столбцом, а как его продублировать над оставшимися двумя - мне непонятно)

В общем, исходник на первой вкладке, помогите, пожалуйста, с помощью Power QUery сделать так, чтобы все выглядело как на второй вкладке. ВОзможно, с помощью Power Query можно сразу сделать так, чтобы - минуя этап сводной - все выглядело сразу так, как на третьей вкладке? Какой-нибудь аналог СУММ ЕСЛИ, может быть...

Добрый день.

Имеется таблица, в которой ВПРом подтягиваются данные из таблицы-источника (в которую данные подтягиваются из нескольких других таблиц). Таблица по месяцам, поэтому каждый месяц приходится передвигать месяца и заново прописывать ссылки на диапазоны.
Было бы удобно, если бы формула, типа
выглядела бы так:
, и потом менял бы как мне заблагорассудится. Но так, как я написал - не получается.

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

Файл-пример не прикладываю, потому что вопрос именно по синтаксису Экселя.

Добрый день.

Прошу помочь в следующем вопросе:

Есть столбики с прогнозируемыми значениями продаж, например:

500 - 600 - 700 - 800 и так далее.
Есть значение остатка на первый месяц (месяц отсчета).

Надо понять, на сколько месяцев хватит товара в момент прихода товара (на дату месяца отсчета) с помощью информации о остатке на месяц отсчета и прогнозируемого прогноза продаж на следующие месяцы?

То есть, если на месяц отсчета товара: 2 100, то

Первый месяц 2 100 - 500 = 1600
2-ой: 1 600 - 600 = 1 000
3-ий: 1 000 - 700 = 300
А в четвертом товара хватит только на 300 / 800 = 0,4

Получается, на момент первого месяца, товара хватит на 3 полных месяца и еще на 0,4, то есть на 3,4 месяца

Есть ли какой-то способ сделать это формулой, помещающейся в одной ячейке?  

Добрый день!

Большая просьба помочь со следующим вопросом.

Во вложении файл-пример со сводной таблицей. В ней можно в фильтре выбирать завод, и таблица будет отображать по каждому артикулу помесячно продажи по выбранным организациям.
Все хорошо, но мне нужно, чтобы напротив артикула стояло количество ВСЕХ продаж этого артикула ПО ВСЕМ организациям, а чуть ниже - сумма продаж этого артикула без учета выбранных организацией(это даже важнее) - все так же помесячно.

Пример, как я хочу, чтобы это выглядело есть на Листе 3.

Скрытый текст

P.S. Скачайте, плиз, файл отсюда, больше 100 Кб не пускает. И так файл сокращал как мог со ста мегабайт
https://yadi.sk/i/uLXIfCAornU51g

Добрый день.

Дано:
Рабочая таблица, в которую подтягиваются данные (с помощью ВПР или ИНДЕКС+ПОИСКПОЗ) о продажах и поступлениях товара из промежуточной таблицы. Рабочая и промежуточная таблица лежат рядом, в одной папке на сетевом диске.

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

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

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

Пляски с бубном включают в себя следующие действия:
- Данные - обновить связи
- открывания и закрывания в различной последовательности рабочих файлов, файлов с исходными данными и промежуточного файла
- перепротягивание формулы (ВПР или ИНДЕКС+ПОИСКПОЗ). То есть, есть столбец, в котором не обновились данные. Я с самого верха заново протягиваю формулу.
- если не помогает, а это реально: протягиваю формулу и ничего не меняется, остаются устаревшие данные, оставшиеся с прошлого обновления (а иногда, о ужас, могут быть вообще какие-то левые непонятно откуда взявшиеся цифры 0_0) - в этом случае, я в одну из ячеек столбца начинаю забивать, например, ВПР вместо ИНДЕКС+ПОИСКПОЗ, или ИНДЕКС+ПОИСКПОЗ вместо ВПР. Ячейка обновляет данные и после этого протягивание даже исходного варианта формулы уже обновляет данные в остальных ячейках.

Вопрос: что за нафиг происходит и как это решить?

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

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

Добрый день.

В поисках способа облегчить таблицу заменил весь ВПР на ИНДЕКС+ПОИСКПОЗ.
Но принципиальной разницы не заметил, а порою казалось, что ВПР работал быстрее.

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

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

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

Добрый день.

Следующая формула:

А точнее вот эта ее часть:

как-то удивительно глючит.
Когда DF89 = 0, а сумма всех остальных ячеек равна 1 200, Excel говорит, что равенство 0<1200 - ЛОЖЬ.
А на 0>1200 - говорит ИСТИНА.

В чем может быть проблема?

Добрый день!

Подскажите, пожалуйста, следующее.

Есть большая таблица со значениями продаж по товарам за месяцы. Выглядит примерно так:

Чайник желтый   20   30   25
Чайник синий      25   35   30
Миксер желтый   40   45   50
Миксер синий     35   40    45

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

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

Я хочу присвоить каждому товару некий ID, добавить столбец с ID напротив каждого товара (то есть, напротив всех чайников будет, например, 1, а напротив всех миксеров - 2), и сделать так, чтобы формула высчитывала среднее значения по следующему алгоритму: СРЗНАЧ только тех ячеек диапазона столбцов AA:AD, напротив артикула которых стоит определенный ID.

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

Добрый день.

У меня есть задача: на основании двух таблиц - создать одну большую, но удобную мне таблицу. Для этого я использую, помимо прочего, функцию ГПР.
Я хочу создать шаблон из формул таким образом, чтобы при заполнении первых двух столбцов - все остальное заполнялось автоматически.

Упрощенно выглядит примерно так:

Таблица 1 "Продажи в штуках" (Именованный диапазон)

.........................1 янв............2 янв
Йогурт 1..............1...................3
Йогурт 2..............5...................8

Таблица 2 "Продажи в ценах (Именованный диапазон)

.........................1 янв............2 янв
Йогурт 1..............20..................60
Йогурт 2.............125................200


Новая таблица:

Йогурт 1....1 янв....1 шт.....20 руб.
Йогурт 1....2 янв....3 шт.....60 руб.
Йогурт 2....1 янв....5 шт....125 руб.
Йогурт 2....2 янв....8 шт.....200 руб.

В Новой таблице для столбцов со штуками и ценами я использовал функцию ГПР, которая по значению даты во втором столбце Новой таблице ищет значение в определенной строчке Таблицы 1(или Таблицы 2). Для Йогурта 1 это будет 2 строка диапазонов Таблицы 1 и 2, для йогурта 2 это будет третья строка диапазонов  таблицы 1 или 2. Таблицы.

Ну, например: ГПР($B2;Йогурты_цены;2;ЛОЖЬ)

B2 - адрес даты 1 янв в Новой таблице
Йогурты цены - имя диапазона Таблицы 2 "Продажи в ценах"
2 - строчка соответствующая Йогурту 1.

Но я хочу, чтобы третий аргумент функции ГПР - он же номер строки, считался сам. У меня 25 йогуртов на 25 строках, я не хочу для каждого йогурта вписывать строки. Теоритически. это можно было бы сделать с помощью функциит ПОИСКПОЗ, которая по имени йогурта в Новой таблице, нашла бы его строку в Таблице 1 или Таблице 2. НО - эта функция работает только со столбцом, а я не хочу вводить новые диапазоны. У меня только два диапазона Продажи в ценах и Продажи в штуках, все остальное должно вычисляться на их основе, чтобы я в Новой таблице вводил только название йогурта и дату.

Чтобы вытащить для функции ПОИСКПОЗ первый столбец из многостолбцового диапазона, я создал такую формулу:
=АДРЕС(СТРОКА(Йогурты_цены);СТОЛБЕЦ(Йогурты_цены))&":"&АДРЕС(СТРОКА(Йогурты_цены)+ЧСТРОК(Йогурты_цены)-1;СТОЛБЕЦ(Йогурты_цены))

Первая часть формулы определяет адрес первой ячейки диапазона.
Вторая часть формулы определяет адрес последней строки в первом столбце диапазона.
Между двумя формулами с помощью амперсанта я поставил знак ":".
И формула визуально дает то, что мне нужно: $B$2:$B$29 - это адрес первого столбца диапразона. Но когда я вкладываю все это в функцию ПОИСКПОЗ - появляется ошибка #ЗНАЧ!

Мне кажется, ПОИСКПОЗ не воспринимает это как диапазон.

Просьба помочь мне сделать формулу такой, чтобы ПОИСКПОЗ мог воспринимать ее результат в качестве адрес на диапазон и вычислять по значению номер строки в этом диапазоне, либо подсказать другой способ с таким же результатом.

Таблицу прилагаю. На первом листе исходные таблицы, на втором - искомая.