Добрый день!
Помогите решить такую, на первый взгляд простую задачу, но я что то голову сломал не могу придумать:
Допустим, есть простой плоский список.
В первых нескольких столбцах какие-то наименования, которые могут повторяться (А,В,С...), в последней - какие-то значения.
Список очень длинный, поэтому для облегчения источника данных он приведен в сводную таблицу, в которой посчитаны суммы строк с одинаковыми значениями и их среднее из последнего столбца (ну как работает обычная сводная таблица), в итоге получается примерно такая таблица:

A	A	24	0,5
A	B	33	1
B	A	48	0,7

...
Теперь из этой таблицы, надо построить сводную таким образом что бы она считала среднее по любому из выбранных разрезов, например, по первму столбцу значение А:
Но в этом случае среднее в сводной таблице будет показывать просто среднее между числами 0,5 и 1, без учета веса каждой из строк (24, 33). А необходимо именно реальное среднее, как если бы все 57 значений (24+33), были взяты из исходной плоской таблицы.
Причем надо что бы это среднее как то отражалось внутри самой сводной таблицы, потому что далее предполагается использование этих данных в сводных диаграммах с разрезами.
Так то я понимаю, что надо просто перемножить два столбца и разделить на сумму строк, типа средневзвешенного, но внутри сводной нельзя производить вычисления, и в итоге среднее показывается неверно.
В самой плоской таблице можно добавлять какие угодно столбцы с вычислениями, если это поможет итоговому результату, но я что то сижу туплю и ничего не могу придумать.

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

Добрый всем день!
Есть такой вопрос: допустим у меня есть какой то набор графиков и показателей, который я аккуратно разместил на одном листе, что бы ничего не уезжало вправо или вниз.
Проблема в том, что когда я открываю этот файл с другого компьютера, у которого, например другая диагональ экрана, то на нем при том же 100% масштабе все эти графики и показатели уезжают за границы экрана.
Есть ли какой то способ сделать так, что бы EXCEL всегда отображал на листе ровно определенное количество столбцов и строк (или хотя бы только столбцов), как бы подстраивая масштаб под окно на любом компьютере, если программа развернута на весь экран?
Заранее спасибо!

Всем привет!
Подскажите, может быть кто сталкивался с такой проблемой:
Есть EXCEL файл, в ячейках которого находятся гиперссылки, которые ссылаются на файлы, которые лежат на рабочем общем сетевом диске Т.
Когда я формирую гиперссылку в файле, она имеет примерно такой вид:
Т:\Папка1\Папка2\Файл.xlsx
Но как только я смотрю адрес этой ссылки в самом EXCEL она уже имеет вид:
File:///рабочийсайт.ru\dfsroot\CO\Userdata\PublicFolder\Папка1\Папка2\Файл.xlsx
При этом файл нормально открывается по такой ссылке.
Как только я вставляю этот файл cо ссылками в Outlook и открываю его из Outlook ссылка вообще приобретает вид:
File:///C:\Users\local.user\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\433YDTYG\Файл.xlsx
И вот тут уже файл отказывает открываться и не может найти файл.
Соответственно все кому я пересылаю это письмо уже не могут открыть файл по этим ссылкам.
Как только не вставлял. все равно ссылки меняются

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

НО все же! Может быть есть способ как победить это? Как сделать что бы ссылка не меняла свое значение? сохраняла адрес тот, что я задаю изначально?
Заранее спасибо!

Вот, спасибо, примерно это я и хотел понять!

Это понятно, округление в это случае, которое предложил человек выше, выглядит проще и предпочтительнее. Но вот мне просто интересно, как Excel интерпретирует данные на уровне выгрузки. Потому что если в общем формате посмотреть на даты, то получаются какие то супер длинные числа, типа 43482,3990162037.
Т.е. довольно широкий диапазон значений будет соответствовать одной и той же дате и времени в плоть до секунды, но при этом базовое значение у них будет отличаться. Дело в том как это значение хранится в базе? можно ли это как то нормализовать на уровне Excel?

Добрый день!
Сориентируйте пожалуйста меня по вот какому вопросу:
Есть отчет, который выгружает даты со временем. Мне необходимо посчитать между ними разницу. Допустим в секундах.
Насколько я всегда знал (может быть я не прав) если просто между двумя такими значениями посчитать разницу (одно минус другое) то он возвращает значение в днях. Стало быть, что бы посчитать секунды, надо итоговое значение *24*60*60. Что я и сделал в приложенном примере.
Результат меня несколько обескуражил. Во первых: значение получается не ровно секунда т.е. 1 а равно, 1,000000234.
Во вторых, в аналогичном случае (разница между датой и временем - секунда) результат получается другой: 0,999999605
Подскажите пожалуйста, что за чертовщина, что я делаю не так? И что мне сделать что бы получить результат ровно в секундах?
Я понимаю, что возможно дело в том,  каким числом задано это значение в общем формате, ну то есть дата типа 15.01.2019 14:59:38 будет равно 43480,62475, и именно между этими числами производится рассчет. Но что мне сделать, что бы получить разницу в секундах? Отчет то однозначно показывает что разница в секундах. Или Excel как то еще может милисекунды считать? В общем, что то я тут поплыл.
Если дело в самом отчете, можно ли сейчас как то нормализовать данные что бы они были именно в секундах?
Заранее спасибо!

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

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

Я уже написал небольшой простенький макрос, который по одной метке переносит строки (он во вложенном примере), что бы был понятен принцип работы.
Но вот несколько вопросов, на которые, я надеюсь вы мне поможете ответить своим решением:
1. Как осуществить неточный поиск? как видно из примера, метки не очень удобно лежат в таблице, просто перечислены через запятую. Я пока сделал по точному совпадению ячейки. А надо, что бы он, как бы, искал среди текста ячейки нужную метку и так же переносил эту строку даже если там присутствуют другие метки.
2. Решение первого вопроса осложняется следующим требованием: как-то отсеять метки, которые содержат в себе другие метки. Например, есть метка "ОД" а есть метка "ОД_2019", очевидно что при неточном поиске макрос найдет и первую и вторую метку. Но надо что бы если стоит метка "ОД" брал только эти строки а "ОД_2019" не трогал. (тут я полагаю только каким то костылем, типа, идет ли после тега запятая или пробел, но может быть есть красивое решение?)
3. Как в конце отметь все строки которые не были перенесены ни на одну из вкладок и перенести их на вкладку "Прочее"?

Заранее спасибо если кто то потратит время на эту задачку!



 

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

Всем, спасибо у меня получилось через Имена создать что то подобное.
Немного неудобно что формулу необходимо держать в диспетчере имен а не в ячейке, но все равно гораздо лучше чем редактировать каждую формулу.
_Boroda_
В чем плохая мысль?
тот пример с которым я работаю состоит из 1 млн строк и весит 70 мб, так что прикладывать пожалуй не буду, а пример сочинять отдельный пока нет времени, если не аргументируете в чем проблема? Если от этого страдает производительность то готов выслушать. Такие формулы всегда оставляю так как данные с которыми я работаю могут дополняться новыми и потом растягивать каждую формулу - это забей.
поверье эти формулы уже феншуйные сами по себе.

Всем добрый день.
Есть вопрос, по возможнсти использованию внутри формулы текста, записанного в отдельной ячейке. Довольно тяжело сформулировать более поянтно, поэтому объясню на примере, что я хочу:
Допустим у меня есть формулы, частью которых является одно и то же выражение, допустим такие:
=СУММ(ЕСЛИ((Выгрузка!N:N=I2)*(Выгрузка!O:O=C2)*(Выгрузка!P:P=D2)*((Выгрузка!Q:Q=K2)+(Выгрузка!Q:Q=L2));Выгрузка!G:G))
=НАИМЕНЬШИЙ(ЕСЛИ((Выгрузка!N:N=I2)*(Выгрузка!O:O=C2)*(Выгрузка!P:P=D2)*((Выгрузка!Q:Q=K2)+(Выгрузка!Q:Q=L2));Выгрузка!H:H);Q2)
=СУММ(ЕСЛИ((Выгрузка!N:N=I2)*(Выгрузка!O:O=C2)*(Выгрузка!P:P=D2)*((Выгрузка!Q:Q=K2)+(Выгрузка!Q:Q=L2))*(Выгрузка!H:H>P2)*(Выгрузка!H:H<T2);Выгрузка!G:G))
Как видно, несмотря на то что формулы разные, во всех есть одинаковая часть (выделил красным), и эта часть основная. Так как мне необходимо для вычисления праметра использовать порядка 6 таких формул, а мне необходимо посчитать десятки таких параметров втает вопрос, могу ли я сократить написание формулы примерно до такого вида:
=СУММ(ЕСЛИ(X);Выгрузка!G:G))
=НАИМЕНЬШИЙ(ЕСЛИ(X;Выгрузка!H:H);Q2)
=СУММ(ЕСЛИ(X*(Выгрузка!H:H>P2)*(Выгрузка!H:H<T2);Выгрузка!G:G))
а этот X - записать где то в отдельной ячейке?
Таким образом я бы существенно соркратил свое время, так как мне не пишлось бы редактировать каждую формулу, а я бы редактировал только этот аргумент X, находящийся в отдельной ячейке. + существенное сокращение вероятности ошибиться.
Если кто то знает такой способ, подскажите пожалуйста!

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

Спасибо но способ не изящный.
под каждую комбинацию условий придется формировать отдельный столбец?
У меня таких комбинация порядка 50 штук.
Потому я и хотел сделать както так, что я, допустим, все условия по какому то параметру просто пишу в столбец, например, рыжий, белый, в крапинку, серый, оранжевый и тд и тд, у меня таких тоже очень много, по некоторым больше 100, но я хотя бы просто мог бы все 100 кинуть в столбец а потом на него сослаться это куда проще.
А когда у тебя 100 условий, и еще 50 комбинаций таких условий... то это по итогу выходит очень жестко. Нужно что то проще.

Добрый день, подскажите пожалуйста, есть ли какой то более изящный способ решения задачи в которой необходимо просуммировать что то по нескольким не единичным условиям.
Пример во вложении.
Надо посчитать сумму значений в колонке D, если Условие 1 = Кот, Условие2 = ИЛИ Рыжий ИЛИ Белый, Условие 3 = ИЛИ Толстый ИЛИ Худой.
Понимаю что можно например решить задачу с помощью БДСУММ, как показано в примере, но тогда надо рисовать целую таблицу условий.
можно сделать вообще дедовским способом с помощью СУММЕСЛИМН и там будет несколько слагаемых и формула будет длинная и не удобная.
А есть какой то более красивый способ решить такую задачу, например с помощью имен? Т.е. я задаю имя диапазону условий Цветов {Рыжий;Белый} и Типа {Толстый;Худой} и как то их использовать в формуле? Если да то как?
Возможно есть другой способ?
Подобная задача вызвана необходимостью проанализировать огромную таблицу данных по множеству различных условий и их комбинаций, и каждый раз рисовать таблицы для условий БД или длинные формулы не хочется. Гораздо удоблее было бы, например решение через Имена, но не понимаю как их использовать в формуле что бы они удовлетворяли условию ИЛИ.
Заранее спасибо.

Спасибо Игорь. А что за волшебство с "Знч"
Это не таблица а некий диапазон и уже сразу с условиями. Что это и как работает?

Спасибо Виктор, у меня все получилось.
Не понял только почему там в формуле 1/ЕСЛИ... но если этот момент убрать, то все работает.
Благодарю!

Есть задача отбросить определенный % значений от диапазона, допустим 10% (либо найти значение х диапазона, удовлетворяющему условию х < 10% от всех значений диапазона)
Мне уже подсказали, что можно воспользоваться формулой =НАИБОЛЬШИЙ(массив,k)
Подскажите как правильно подставить в формулу диапазон, удовлетворяющий трем условиям - A:A=Толстый B:B=Рыжий C:C=Кот
Пример во вложении.

Всем спасибо! Действительно формула НАИБОЛЬШИЙ НАИМЕНЬШИЙ подходит для решения данной задачи.
Осталось понять как правильно определить массив. но это уже как говорят, другая история.

А можете, если не сложно, подсказать, как задать массив с условиями.
Т.е. отобрать в массив только те элементы колонки A, которые соответствуют определенным значениям колонок B и C?

Просто один рабочий (с него по понятным причинам выход в интернет ограничен), второй личный.

У меня рядом два компьютера один с офисом другой с интернетом. Сейчас попробую разобраться с примером Виктора. Спасибо!

да, либо 14 либо 13, смотря до куда округлять. В моем случае это ен принципиально, так как имеется массив с более чем несколько десятков тысяч значений. Мне просто надо "отбросить" от него 10% сверху и 10% снизу. Для этого надо знать от какого значения начать "отбрасывать", а стало быть найти эти значения.

Вы начали писать про сортировку, а потом сказали что все реализуемо формулами. Есть некая формула, которая осуществляет сортировку?
Мне надо проделать эту манипуляцию примерно с 50 различными условиями для массива. Если бы массив был один и условие одно, то я бы вручную нашел эти 10% и не парился. Но так как таких условий много, и каждый раз эти 10% будут иметь разные значения, хочется все сделать формулами.

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

Добрый день друзья. Первый день на форуме так что сильно не пинайте.
Можно ли каким то образом решить следующую задачу, не прибегая к макросам:
Имеется массив (столбец) данных, допустим 1,2,3,4 ..., 100
в отдельной ячейке задаем значение в процентах, например 10%.
Как найти значение массива которое будет удовлетворять следующим условиям: 10% элементов массива, больше искомого значения.
В нашем примере это значение = 90. т.к. больше него находится 10% от всех элементов массива а именно (91,92, ... 100)
Надеюсь объяснил достаточно понятно.
Заранее спасибо.