Полезняшки Excel

На этой странице представлена коллекция простых и изящных инструментов работы в Excel. То, что по-английски называется tips & tricks. В некоторых случаях я даю ссылку на первоисточник. Это означает только то, что именно там я впервые встретил описание метода.

Начиная с версии 2010 в Excel появилась гибкая функция ЧИСТРАБДНИ.МЕЖД, которая позволяет рассчитать количество рабочих дней для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней.

Если вам нужно найти номер первой непустой ячейки в строке (столбце), или значение первой непустой ячейки, можно воспользоваться одним малоизвестным свойством функции ИНДЕКС возвращать не одно значение, а массив.

В то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА/ЛОЖЬ в числовые 1/0, СУММПРОИЗВ не делает этого. Чтобы справиться с проблемами используйте операцию минус минус, или поместите все критерии в один аргумент, используя умножение (*) для Булевого критерия И и сложение (+) – для ИЛИ.

Если вы не начинающий пользователь, вас может заинтересовать книга Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В частности, вы узнаете о Булевой алгебре, операции минус минус, функции ДВССЫЛ, таймере, ссылках R1C1, датах до 1900 г.

Подробно о, возможно, самой полезной функции Excel — ВПР. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня

Всё о сводных таблицам см. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. В частности, раскрыты следующие темы:

Группировка данных сводной таблицы в Excel 2013
Управление панелью Поля сводной таблицы
Сортировка данных сводной таблицы в Excel 2013
Фильтрация данных сводной таблицы в Excel 2013
Вычисляемые поля и вычисляемые элементы в Excel 2013
Сводные диаграммы в Excel 2013
Условное форматирование в сводных таблицах
Сводная таблица на основе нескольких листов или диапазонов консолидации
Сводная таблица на основе внутренней модели данных
Внешние источники данных для создания сводной таблицы
Интерактивная веб страница на основе книги Excel
Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ
Советы по работе со сводными таблицами
Макросы в сводных таблицах
Обработка данных с помощью модуля PowerPivot
Надстройка Power View в Excel 2013
VBA в сводных таблицах
Кубы данных OLAP

Функции массива — не самые простые для понимания, однако они дают такие возможности, что дух захватывает. Рекомендую книгу Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Если вы хотите защитить лист, но оставить возможность обновлять сводную таблицу, стандартные методы Excel не помогут (странно, так как анонсировано, что должны помочь…). Рекомендую небольшой код VBA для частичной защиты листа.

Серьезный недостаток при создании сводных таблиц на основе больших наборов исходных данных — это ожидание завершения обработки всех данных таблицы при добавлении в нее нового поля. Настоящей пыткой становится добавление в сводную таблицу нескольких больших полей данных! Включите флажок Отложить обновление макета, расставьте все поля, как требуется, и лишь потом обновите сводную таблицу.

Уже стало традицией, что Microsoft от версии к версии что-то улучшает, а что-то ухудшает (по крайней мере, делает нечто не однозначное). Так, начиная с 2013 г. нельзя указать Excel’ю сформировать вторую сводную таблицу на основе нового кеша. Как обойти это ограничение, читайте в заметке Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?

Начиная с версии 2010 г. в Excel добавлены срезы — удобный и наглядный инструмент работы со сводными таблицами; срез — это тот же фильтр, только вынесенный в отдельную область. А с 2013 г. добавлен особый вид срезов — временные шкалы, позволяющие визуализировать работу с датами.

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

Начиная с версии 2010 г. в Excel появились спарклайны — маленькие диаграммы, помещающиеся в одну ячейку листа. Спарклайны позволяют обнаружить тенденции, периодические (сезонные) изменения, выделить максимальное (минимальное) значение.

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

В версии Excel Professional Plus доступна надстройка Inquire. Она позволяет: проанализировать активную книгу, сравнить две книги, отобразить в виде схемы связь книги, листа, ячейки; очистить ненужное форматирование. Последнее особенно ценно, если вам достался файл от начинающего пользователя, любящего раскрашивать целые столбцы или строки.

В настоящее время в большинстве вычислений используются десятичные дроби. Однако в некоторых случаях вы можете столкнуться с простыми (обыкновенными) дробями. Это может быть учебный пример, или диаметр вентиля (в долях дюйма), или, наконец, вес золотого слитка (в долях унции). 🙂 Excel предлагает 9 стандартных форматов для обработки обыкновенных дробей. Кроме того, вы можете создать большое число пользовательских форматов по своему усмотрению.

Если вам встретились незнакомые единицы измерения, например, баррель, oz, акр, узел, фунт, пинта, функция ПРЕОБР легко переведет их в привычные метры, литры, граммы…

Когда ваша книга Excel содержит множество листов, для удобства навигации вы, возможно, захотите создать оглавление (указатель). Вы можете сделать его вручную или воспользовавшись кодом VВA.

Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. По умолчанию Excel создает не обычные ссылки, типа =С4, а в виде формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вам это неудобно, или вы хотите иметь возможность копировать формулу вдоль столбца, научите Excel ссылаться на ячейку в сводной таблице, как на обычную.

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

Функция ДВССЫЛ одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми.

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

Стандартные средства Excel не позволяют создавать контекстные выпадающие списки. Например, вы хотите, чтобы состав списка зависел от содержимого ячейки, находящейся слева от ячейки со списком. Создайте именованные массивы и сошлитесь на них с помощью функции непрямого действия ДВССЫЛ (INDIRECT).

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

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

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

Финансовые функции в Excel кажутся очень сложными. Чтобы их понимать, нужно разбираться в предметной области — что, собственно говоря, рассчитывают эти функции. Прочитайте заметку Сравнение аннуитетных и дифференцированных платежей в погашение ипотечного кредита, и для вас приоткроется завеса над некоторыми финансовыми функциями, в частности: ПЛТ, ОСПЛТ, ПРПЛТ, ПС, КПЕР, СТАВКА.

Если область данных, на основе которой построена диаграмма, расширилась, при этом часть данных ушла за границы видимой области листа Excel, отредактируйте строку формул, и на диаграмме появятся все новые данные.

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

Если вас напрягает необходимость постоянно изменять область диаграммы после каждого добавления данных, замените в диаграммах ссылки на ячейки ссылками на именованные динамические диапазоны. Этот трюк позволяет также создавать диаграммы, отражающие определенное число последних записей (даже если добавите несколько новых)

Стандартные средства Excel в круговых диаграммах позволяют использовать только один набор данных. Если вы хотите сравнить два набора данных, воспользуйтесь несложным трюком, предложенным Д. Холи и Р. Холи в книге «Excel 2007. Трюки».

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Однако само условное форматирование использует некие правила для выделения. Вместо того, чтобы анализировать цвет ячеек, достаточно применить эти правила в функциях СУММЕСЛИМН или БДСУММ.

Около двух лет тому назад мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Оказалось, что файл содержал объекты, и мне удалось их удалить с помощью небольшого кода VBA. У этой истории совсем недавно появилось продолжение. Посетитель блога оставил комментарий к той заметке, указав что стандартный Excel-файл, начиная с версии 2007, является zip-архивом. Так что для удаления «паразитного» содержимого достаточно открыть файл как архив и удалить соответствующую папку.

Если вам нужно подсчитать число вхождений подстроки (символа) в текст, можете использовать нехитрый VBA код или стандартные средства Excel на основе функции ПОДСТАВИТЬ()

Многим известна бухгалтерская «заморочка», связанная с расхождением на копейку в итогах суммирования. Как известно, Excel суммирует на основе точных значений, хранящихся в ячейке, а отражает в соответствии с выбранным вами форматом, например, с точностью до копейки. Чтобы преодолеть эту проблему предлагается изящная формула.

Как подсчитать число ячеек, содержащих символ (букву)? Воспользуйтесь формулой массива.

Вы думаете, что для моделирования с помощью метода Монте-Карло Excel`ю требуется какая-нибудь надстройка? Совсем не обязательно… Простейшие модели можно реализовать с помощью обычных таблиц и функции СЛЧИС. Правда, будьте осторожны, так как эта функция пересчитывается при каждом изменении на листе, и если вы захотите реализовать 100 000 сценариев, то Excel может «загрустить»… Но все же, для решения задач моделирования методом Монте-Карло лучше воспользоваться специализированной надстройкой, например, Crystal Ball. Читайте Моделирование методом Монте-Карло в Crystal Ball для Excel и Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel

Для вычисления среднего значения диапазона данных в Excel есть функция СРЗНАЧ. Она игнорирует пустые ячейки, но, к сожалению, учитывает ячейки с нулевыми значениями. Используйте элегантную и простую формулу массива для вычисления среднего, не учитывающего нулевые значения.

Откройте для себя мир формул массива. Ваши возможности в использовании Excel значительно расширятся.  Если вы впервые знакомитесь с этой темой, начните с Введения в формулы массива, а затем изучите Некоторые примеры использования формул массива.

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

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

Если при открытии Excel-файла появляется предупреждение о циклической ссылке, существует простой способ найти ячейку, ответственную за это «безобразие».

Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0! Формула массива позволяет преодолеть это ограничение.

Ранее я описал, как транспонировать столбцы в строки с сохранением связи новой области со старой с помощью функции ДВССЫЛ. А недавно обнаружил значительно более простой и изящный способ, основанный на функции массива {=ТРАНСП(массив)}

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

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

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

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

Умышленные или непреднамеренные действия способны исказить эффект, наблюдаемый на диаграммах. «Честные» графики должны показывать только то, что действительно содержится в данных. Эдвард Тафти предложил формулу для расчета фактора лжи, измеряющего искажения при визуализации данных.

Некоторые пользователи любят украшать свои диаграммы массой дополнительных элементов, например, горизонтальными и вертикальными линиями сетки, подписями данных и т.п. Живой классик инфографики Эдвард Тафти, наоборот, предлагает минимизировать количество элементов диаграммы.

В Excel нет стандартной функции суммирующей отдельно значения в четных и нечетных строках. Иногда возникает и более сложная задача — например, сложить значения в каждом пятом столбце. Рекомендую для этих целей функции массива. Проверьте мощь этих функций. Они не так сложны, как об этом принято думать. 🙂

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

Широко известна функция проверки правописания в Word или Outlook. Гораздо реже пользователи применяют проверку формул в Excel. В то же время такая проверка позволяет выявить довольно большое число ошибок.

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

Затенение пространства между линиями графика. Если вы хотите закрасить пространство между двумя линиями графика, сделать это «в лоб» не получится. Однако реализовать задуманное вам поможет использование на одной диаграмме, как линий, так и областей с накоплением.

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

Заменить цифры в подписях осей ординат на пользовательское содержание вы можете с помощью дополнительного ряда данных в виде точечной диаграммы.

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

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

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

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

Пузырьковые диаграммы позволяют наглядно представить зависимость трех переменных. Могут использоваться при  построении матриц типа BCG или в инфографике.

Преобразование массива в столбец или строку. Если необходимо преобразовать массив в столбец или строку, можно воспользоваться функцией Индекс.

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

Создать сводную на основе данных с нескольких листов. Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Excel создаст сводную, но ее возможности будут существенно ограничены по сравнению со стандартными методами. Как обойти эти ограничения см. здесь.

Скрыть / показать ленту. Чтобы скрыть ленту дважды щелкнете по любой вкладке ленты. Чтобы временно отобразить ленту, щелкните нужную вкладку. Чтобы вернуть ленту, дважды щелкните на  нужной вкладке.

Прекратить торможение. Если Excel тормозит, и никаких видимых причин к этому нет, проверьте нет ли на листе паразитных объектов. Пройдите по меню: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты. Если таких объектов много и Excel виснет, примените макрос.

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

Вызвать окно «Формат ячеек» при помощи клавиатуры (это быстрее, чем любой иной метод): нажмите CTRL+1

Использовать мастер сводных таблиц. В Excel2007 разработчики Microsoft почему-то решили отказаться от мастера сводных таблиц и упрятали его так далеко, что сразу и не найдешь. Для использования мастера сводных таблиц выведите его на панель быстрого доступа. Подробности здесь.

Используйте функцию СТРОКА для создания заполнителя. Иногда возникает задача создать список, содержащий структурированную последовательность, например, для секторов, рядов и мест в театре / стадионе или для стеллажей на складе. Подробности здесь.

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

Вручную измените цену делений на оси времени. Возможно, вы уже сталкивались с тем, что шкала времени на графиках иногда отражается «криво». Excel автоматически создает цену деления равную, например, 7 мин 12 сек. Креативненько, неправда ли!? 🙂 Установите цену деления сами, не доверяя столь ответственную работу мозгам Excel`я.

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

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

Научите функцию ДЕНЬНЕД возвращать буквенное значение. Не знаю, как вас, а меня нервирует, что функция ДЕНЬНЕД возвращает число… например, 4. Куда как приятнее – «четверг». Эту проблему может легко решить небольшой код VBA или стандартные средства Excel.

Создать именованный динамический диапазон. Если вы работаете с данными, объем которых изменяется с течением времени (увеличивается число строк и/или столбцов), то очень удобно присвоить диапазону имя, а размер диапазона определить с помощью функции СМЕЩ. Подробности здесь.

Суммируйте значения в ячейках определенного цвета. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, у Excel`я нет такой стандартной функции. Воспользуйтесь простеньким кодом VBA. Вы также можете подсчитать число ячеек, содержащих текст определенного цвета.

Установите фильтр сводной таблицы на основе данных в ячейке. Если вы используете сводные таблицы в Excel, в которых содержатся тысячи или десятки тысяч значений, то выбор одного из них фильтром отчета наверняка сводил вас с ума… Введите значение, по которому требуется установить фильтр в ячейку на листе и запустите макрос.

Применяйте анализ чувствительности при построении моделей. Например, при подготовке инвестиционного проекта результатом моделирования явилась внутренняя норма доходности – IRR. Как поведет она себя при том или ином изменении исходных посылок? Подробности здесь.

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

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

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

Комментарии: 110 комментариев

ДД! Про функцию ВПР примера нет. ссылка тольлко на word файл

Спасибо за комментарий. Поправил.

Вот бы еще надстроечку Excel для построения ящичной диаграммы))

Анна, надстроечку не нашел, а вот заметку по предложенной Вами теме написал: Excel. Биржевая диаграмма, она же блочная, она же ящичная

отличная статья. эксель 2007 как архив — это супер.спасибо.

Здравствуйте!
Условия игры: Банк намерен осуществить пять эмиссий денежных средств (по 10 млн. каждая) на общую сумму 50 млн. для выдачи кредитов заинтересованным предприятиям «А», «Б», и «X». Ключевая задача каждого предприятия — получить максимальную сумму кредита. Предприятия «А» и «Б» имеют статус привилегированных, предприятие «X» — простое, т.е. такого статуса не имеет. Привилегированное предприятие перед очередной эмиссией может предварительно сделать заказ на выделение ему той или иной суммы кредитных средств. Простое предприятие «X» такого права не имеет, но также участвует в распределении эмиссионных денег. Привилегированные предприятия («А», «Б») предварительно знакомятся с условиями распределения кредитов и получают информацию о фактическом их распределении банком после каждой очередной эмиссии денег в соответствии с установленными правилами (данные распределения фиксируются в таблице). Запросы на кредиты могут быть сделаны в пределах от 1 до 10 млн. и должны быть целочисленными (не дробными). Победителем считается предприятие, набравшее максимальную сумму кредитов по итогам пяти эмиссии. Правила распределения кредитов: Если суммарный заказ предприятий «А» и «Б» не превышает суммы эмиссии, то их требования удовлетворяются. Оставшаяся сумма (в случае ее наличия) переходит предприятию «X». Если суммарный заказ предприятий «А» и «Б» находится в пределах от 11 до 15 млн. р. (включая 15 млн. р.), то удовлетворяется заказ предприятия, запросившего сравнительно большую сумму кредита. Второе предприятие не получает кредит. Оставшаяся сумма переходит предприятию «X». В случае, если предприятия «А» и «Б» заказали одинаковые суммы, и при этом их суммарный заказ находится в пределах от 11 до 15 млн., их заказы не удовлетворяются и вся сумма эмиссии переходит к предприятию «X». Если суммарный заказ предприятий «А» и «Б» превышает 15 млн., оба привилегированных предприятия штрафуются на сумму своего заказа, а вся сумма эмиссии переходит к предприятию «X». Результаты свести в таблицу
Заранее признателен

Михаил, спасибо за задачку)) К сожалению, я очень давно не решал задачи теории игр, поэтому сходу затрудняюсь это сделать. Обязательно освежу в памяти и решу позже, а пока советую воспользоваться другими ресурсами…

Поставлена задача привязка внедренного объекта к ячейке листа с указанием ее адреса.
Решение найдено только по привязке, как узнать адрес: внедряю любой объект и привязываю его к ячейке листа с помощью формулы. Далее щелкнув два клика на объекте эта ячейка становится выделенной и для дальнейшей работы с ней нужен адрес ячейки. Для работы с ней использовал метод проверки данных, причем ячейка становится активной даже под защищенным объектом.
Хотелось бы работать с этой ячейкой только зная ее адрес, используя только формулы (без макросов).

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

Спасибо за ответ, пример отправлен

ЗАДАЧУ РЕШИЛ, БЛАГОДАРЕН ЗА ПОДСКАЗКУ О ПРОВЕРКЕ ДАННЫХ
ЧИСТО ТЕХНИЧЕСКИЙ ВОПРОС — ПОЧЕМУ ПРИ ОБРАЩЕНИИ К ФОРМУЛЕ «ГИПЕРССЫЛКА» НА ОДНОМ КОМПЬЮТЕРЕ ПРОСИТ ОБРАТИТЬСЯ К АДМИНИСТРАТОРУ , КАК ИСПРАВИТЬ.

Благодарю за вашу работу.
Вопрос. Есть ли инструмент поиска листа в книге по его названию. В работе книжечки по 100-150 листов.

Валерий, спасибо за вопрос)) У меня давно лежала заготовка заметки на эту тему. Появился отличный повод, чтобы ее дописать. См. Excel. Создание указателя листов в книге

Добрый день! Спасибо за статью) Очень полезно!
Вопрос: есть два листа с данными, на одном — таблица с суммами по месяцам горизонтально, а на другом таблица с суммами вертикально. На первом листе, в таблице есть строка в которой должны быть данные из столбца на второй странице. Как можно задать формулой, чтобы отдельно не транспонировать таблицу 2?

А все уже, спасибо. Священная ВПР 🙂

Отличная статья. Много полезного!!
Есть задача: ежемесячно делать выборки из таблицы (5000 строк) по критерию (месяц проведения испытания). Можно использовать фильтры, но все равно приходится частями копировать на новый лист, как это делать автоматически? Скажем выбрал месяц и автоматом из листа 1 сформировался перечень оборудования в листе 2.

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

Юлия, мне не известен способ, как это сделать без написания макроса. Если код VBA Вас не смущает, почитайте заметку Установка фильтра сводной таблицы в Excel на основе данных в ячейке. Может, пригодится…

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

Георгий, я не знаю, как это сделать без макроса. Если такой подход вас не смущает, воспользуйтесь рекомендациями в заметке Установка фильтра сводной таблицы в Excel на основе данных в ячейке.

Если это один источник данных, одно подключение у всех таблиц, то можно воспользоваться Срезами. О них есть статья.

Юлия, спасибо за подсказку)) Мой первый ответ Георгию относится к периоду, когда я еще не изучил срезы. Можно почитать заметку Excel 2013. Срезы сводных таблиц; создание временной шкалы

если в ячейке Е157 находится значении c цифрами но содержащими букву «С», то как присвоить F157 значение состоящее в Е157

Доброго дня! Перечитал ваши советы и попробовал использовать в своей задачи совет про Сводные таблицы, но несколько не получилось, может я не с той стороны начал? Задача в следующем: есть 3 листа с данными, на каждом листе 2 колонки с номером устройства и адресом, как выбрать на новом листе все адреса из трёх таблиц с одним номером устройства?

[…] Путь воина » Полезняшки Excel. На этой странице представлена коллекция простых и изящных инструментов работы в Excel. […]

Добрый день! Не могли бы Вы дать совет как сформировать график-радар на изменяющихся данных. При условии, если число расчитываемое меньше или равно нулю было красным цветом на графике, а выше нуля зеленым.
Сценарий 1 База
Дом 1 2 0
Дом 2 2 0
Дом 3 2 0
Дом 4 -1 0
Дом 5 1 0
Дом 6 1 0
Дом 7 0 0
Дом 8 1 0

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

Условия — имеем 2 сценария — для каждого свои значения. Дом 1 — Сценарий 1 = 2; База = 0
…………….
Дом 4 — Сценарий 1 = -1 База = 0

Спасибо

Lori, в вашей формулировке я не представляю, как решить задачу. Могу предложить несколько вариантов: 1) воспользуйтесь спарклайнами, типом Гистограмма, в них предусмотрена опция, позволяющая положительные и отрицательные значения показывать столбцами разного цвета (см. Спарклайны или микрографики в ячейках Excel); 2) постройте обычную диаграмму, тип гистограмма, выделите ряд, кликните правой кнопкой мыши и выберите Формат ряда данных, перейдите на вкладку Заливка и границы, и в области Заливка поставьте галочку Инверсия для чисел <0

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

Спасибо за ответ

Нашел на вашем сайте решение проблемы с #Н\Д при сложении. У меня есть массивы данных до 2000 строк (можно сократить, но не важно), мне надо посчитать через функцию ЛИНЕЙН коэффициенты а и в. Но если в строках попадаются пустые ячейки, #Н/Д, то вылезает ошибка #ЗНАЧ, не подскажите каким образом можно заставить excel брать только значения не равные 0 и не равные перечисленным ошибкам для расчета ЛИНЕЙН? уже две недели мучаюсь(((

Dart, попробуйте для начала избавиться от ошибки в ячейках, например, с помощью функции ЕСЛИОШИБКА:

Таким образом, в ячейках вместо ошибок отразятся пустые строки "". К сожалению, функция ЛИНЕЙН не сможет обработать такой диапазон. Но вместо неё можно использовать две другие функции: НАКЛОН и ОТРЕЗОК, которые не столь «привередливые».

Baguzin, но разница в значения может быть значительная???

И плохо видно картинку. Не прочитал доконца.

Чтобы увеличить изображение, кликните правой кнопкой на картинке, и выберите опцию Открыть картинку в новой вкладке…

Если имеете ввиду разницу между ЛИНЕЙН с одной стороны и НАКЛОН и ОТРЕЗОК, с другой, то разница не будет значительной. Обе функции используют метод наименьших квадратов. Различие, фактически, возникает из-за того, что ЛИНЕЙН считает ряд данных сплошным (поэтому-то и не работает на прерывистом ряду), а НАКЛОН и ОТРЕЗОК «знают», что ряд прерывистый.

Понял спасибо

Baguzin, а как заставить excel пропускать диапазоны, то есть надо из А21 вычесть А1, а затем следующая операция должна произойти только А31-А21, при этом надо чтобы это записывалось последовательно B1=A21-A1, B2=A31-A21 и так далее. Вас как специалиста спрашиваю. Решени с наклоном и отрезком избавило от многих головных болей, правда там формула адская получилась.

Попробуйте применить функции ДВССЫЛ и СТРОКА. См. Excel. Примеры использования функции ДВССЫЛ (INDIRECT), Excel. Использование ДВССЫЛ для транспонирования строк в столбцы с сохранением формул, Excel. Использование функции СТРОКА для создания заполнителя

P.S. Можно обращаться ко мне по имени (или имени, отчеству). Данные есть на странице «Об авторе».

Попробую

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

Добрый день!
Помогите, пожалуйста, с вопросом: есть Excel файл с большим объемом данных, я хочу дать к нему доступ нескольким сотрудникам, но так, чтобы они могли видеть только их касающиеся данные. В идеале, чтобы каждому из них отправить опеределенный пароль, при вводе которого они видели только свои данные. Как это сделать? Заранее большое спасибо!

Yana, может быть, вам поможет заметка Советы по работе со сводными таблицами. См. совет 14. Использование сводной таблицы для распределения набора данных по листам книги и совет 15. Использование сводной таблицы для распределения набора данных по отдельным книгам

Здравствуйте.
Excel2007: имеются ячейки с гиперссылками на интернет-страницы. Как извлечь гиперссылку в отдельную ячейку? Зараннее благодарю.

Павел, см. Как получить адрес гиперссылки из ячейки. Если вы никогда не создавали пользовательские функции, начните с Написание пользовательской функции в Excel

Спасибо. Обошел этот момент: импортировал сразу в 1С.

Здравствуйте, Сергей Викторович! Хотел бы попросить совета/помощи, в решении одной задачи Excel. Мне несколько раз попадались книги Excel(скаченные с интернета) в которых значение переменных изменялось при помощи по горизонтальной шкалы с прокруткой влево вправо для получения нужного значения переменной(на подобии полосы прокрутки в интерфейсе любой программы), как это можно сделать при помощи Excel? Это было бы очень наглядно при проведении анализа чувствительности или при задании данных в финансовую модель. То есть я хочу получить ссылку на определенную ячейку, но чтобы в этой ячейке был диапазон и менялся он нажатием кнопочки влево вправо. Возможно у Вас на эту тему уже написана статья, но к сожалению я не нашел.

Павел, посмотрите п. 5 заметки Диаграммы в Excel. Отображение части данных с использованием элементов управления. Там описано, как создать полосу прокрутки, и привязать к ней значение в конкретной ячейке. Возможно, для вашего случая также подойдет срез, см. Excel 2013. Срезы сводных таблиц; создание временной шкалы.

здравствуйте, подскажите пожалуйста, как мне сделать следующее: в столбце «А» есть любая информация (буквы, символы, числа), необходимо отразить в столбце «В» только числа столбца «А». Т.е установить как бы фильтр на числа.

Денис, воспользуйтесь свойством Excel возвращать ошибку при любом арифметическом действии с нечислами. Например, умножьте значение в столбце А на 1, и отразите в столбце В только числа с помощью формулы: =ЕСЛИОШИБКА(A1*1;"")

К сожалению, формула не справилась с пробелом. Либо «терпите», либо усложните формулу, чтобы пустые ячейки в столбце А также не отражались в столбце В.

Это понятно. но я имел ввиду отделить число 56 из текста 56п в нужную ячейку. котлеты (букву П) убрать, а мухи (число 56) оставить.

Денис, если у вас текст в ячейках структурирован, то подойдет формула с функциями: ДЛСТР, ЛЕВСИМВ, ПРАВСИМВ; см., например, здесь. Если у вас произвольная структура текстовой строки, попробуйте формулу массива; например здесь или здесь.

Спасибо! Помогли! Кланяюсь!

Добрый день, сижу бьюсь над простой задачей, да ни как не могу победить
есть число, скажем 100, и есть набор значений.. 100, 101, 95, 103 …
и вот как найти максимальное отклонение от заданного числа, ума не приложу

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

Владимир, рекомендую почитать заметку Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне. На ее основе для вашего случая я немного модифицировал формулу; см. в Excel-файле

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

Очень поможет Ваш ответ, заранее спасибо!

Надежда, мне не известен такой способ.

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

Формула получилась другого вида и при попытки вставлять ссылку вместо значения пишет ошибку "Ссылка".
Формула вот такая: =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Сумма по столбцу Бух суммаД]";’осв черн’!$A$3;"[Дата].[го_месяц_с]";"[Дата].[го_месяц_с].&[201301]";"[счета].[счет]";"[счета].[счет].&[51]")

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

И второй вопрос. Модели данных подлежат каким-либо преобразованиям или могут использоваться только сырыми как есть?

Добрый день ,Сергей
Как сделать чтобы отрицательные значения ( показания счетчика топлива) равнялись -0- .. а то это получается неверная сумма..

Александр, если я правильно понял ваш вопрос, воспользуйтесь формулой =ЕСЛИ(A1<0;0;A1)

См. также Excel-файл

Добрый День , Сергей
Спасибо вам за ваше время и ответ..
Написал вам на почту.. ( прикрепил файл) с дополнительными вопросами

Заранее спасибо
Александр

Александр, правильный адрес электронной почты: s_bag@mail.ru

Добрый День, Сергей..
Переслал на указанный адрес

спасибо

Уважаемый Сергей.
Добрый День

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

всего вам самого наилучшего

Александр

Добрый день!

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

Здравствуйте! Подскажите, пожалуйста, а можно сделать график с картой Европы/Азии/России или пока только штатовские карты доступны?

Может есть тоже какой-то макрос/утилита для этого?

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

Сергей, добрый день!
Не могу никак решить следующую проблему:

В одной таблице имеются данные о периоде принятия работ/услуг (Период 1) и период оплаты (Период 2) записываются построчно.
Период 1 Период 2 Сумма
1. P03 FY15 P04 FY15 100
2. P05 FY15 P08 FY15 140
3. P04 FY15 P06 FY15 50

В другую таблицу требуется в каждом периоде проставить итоговую сумму кредиторской задолженности исходя из данных первой таблицы
P01 FY15 P02 FY15 P03 FY15 P04 FY15
xx xx xx xx

То есть в первом случае сумма должна отобразиться
только в P03
во втором в Р05, Р06 и Р07
в третьем в Р04 и Р05

Не знаете есть ли в экселе удобный механизм для моего случая? Желательно без создания множества дополнительных таблиц…

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

Максим, пришлите Excel-файл: s_bag@mail.ru

Максим, с моей точки зрения, оптимальное решение — построение сводной таблицы на основе исходных данных. По этой теме рекомендую Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013.

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

Спасибо огромное за ответ!

Анна, Excel помимо равномерной позволяет использовать логарифмическую шкалу:

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

Добрый день!

Скажите, можно ли сделать такое через Excel:

Дана табл (имена учеников повторяются) :
Ученик Успеваемость (от 1 до 4)
Игорь 1
Игорь 1
Игорь 4
Максим 4
Максим 3
Саша 2
Саша 2
Саша 3
Саша 1

Необходимо группировать на уровне ученика , при этом напротив каждого должно стоять макс значение успеваемости. Т.е.:
Игорь 4
Максим 4
Саша 3
Как выбирать макс значение для каждого из учеников? Спасибо!!

Мария, на мой взгляд, наиболее простой метод — использовать сводную таблицу

См. также Excel-файл
Если вы не знакомы со сводными таблицами, рекомендую Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Если со сводными таблицами знакомы, но затрудняетесь с конкретной настройкой для вашего случая, см. Вычисления в сводной таблице (в области значений) в Excel 2013

Добрый день Сергей Викторович!

У нас есть зависимость двух показателей (в моем случае длина проезда пассажирами по маршруту и пассажиры отправленные). Необходимо вывести уравнение описывающее данную зависимость, предположительно это квадратный трехчлен. Подскажите, как с помощью Excel решить проблему подбора параметров.

Павел, попробуйте построить график на основе ваших данных. Я бы рекомендовал тип Точечная. Кликните правой кнопкой мыши на кривой и выберите опцию Добавить линию тренда. В окне Формат линии тренда поставьте галочки напротив Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2). «Поиграйтесь» с видом зависимости. Если, как вы предполагаете, это квадратичная зависимость, то выберите опцию Полиномиальная, Степень 2. Учтите, что чем ближе значение R^2 к единице, тем лучше выбрана линия тренда. Коэффициенты к уравнению, описывающему линию тренда появятся на графике.

Чтобы увеличить картинку, кликните на ней правой кнопкой мыши и выберите Открыть картинку в новой вкладке.

Задача: поле 10*10 ячеек. В этом поле две ячейки имеют зеленый цвет. Нужно заставить двигаться зеленые ячейки хаотично до тех пор, пока они не встретятся в одну. В это время должна появляться третья зеленая ячейка. И дальше все снова продолжают движение до выполнения такого же условия. Максимальное количество шагов каждой зеленой ячейки 80, после этого ячейка удаляется на совсем. Появление третьей ячейки может происходить при попадании двух ячеек в одну, только при условии что обе ячейки совершили минимум 20 шагов и максимум 60.

Ну вот, как то так)
Возможно ли осуществить такое в exel?

Подскажите мне, пожалуйста, возможно ли сделать в Excel 2010 так, чтобы, допустим, помечаю флажком нужные показатели, а их интерпретация собирается в список на другом листе. например: есть перечень признаков 1.2.3.4.5 и их значение. мне нужно выбрать только 1 и 5, я их отмечаю, а на другом листе, или в отдельном месте появляются по порядку не сами признаки, а их значение (словесное причем, а не цифровое)

Нина, можно, например, создать сводную таблицу, вставить к ней срез, отформатировать таблицу, убрав ненужные элементы. Выбирая фильтры на срезе, в таблице будут отражаться только требуемые описания. См. Excel-файл. Подробнее см. Срезы сводных таблиц.

Спасибо большое за сайт, все понятно и доступно для мозга, смогла решить множество задач по упрощению жизни!!!

Добрый день!

Подскажите пожалуйста, что можно сделать со следующей проблемой:

У нас работал сотрудник, который вёл таблицу данных в эксель. В этом же файле находится и сводная таблица. После того как сотрудник уволился этот файл перекинули с компьютера на компьютер через ctrl+c/ctrl+v а сам файл успешно стёрли при форматировании.

И после этого столкнулись с проблемой, файл открывается на любом другом компьютере, однако при попытке обновить сводную таблицу выводит сообщение что сводная таблица не может получить данные по адресу (адресом стоит пред идущее место пребывания файла) просто вернуть туда файл — не получается.

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

Спасибо.

p.s.

Надеюсь я понятно описал проблему.

Станислав, (1) можете прислать файл мне s_bag@mail.ru, я поправлю и верну; (2) кликните на любой ячейке сводной таблицы, перейдите на вкладку Анализ, кликните кнопку Источник данных, и в появившемся окне выберите новый диапазон исходных данных, на который должна ссылаться сводная.

Файл большой, под сто мегабайт. ))
Заново задали диапазон данных для сводной таблицы, сразу помогло, большое спасибо!

Не сочтите за рекламу. Очень понравился этот ресурс, потому что оказался полезен в том что мы делаем. Мы разрабатываем набор математически-экономических «Полезняшек для Excel», где расчеты выполняются «по одной кнопке». Пока это регрессионный анализ, анализ чувствительности, сценарный подход, метод Монте-Карло и подбор распределения случайных величин.
На ресурсе http://www.EVArisks.com есть описание и демо. Будем рады любым замечаниям и пожеланиям. Продолжаем изучать ваш ресурс. Спасибо

Сергей, спасибо за Ваш сайт просто клад для разных сторон жизни!
С удовольствием читаю и применяю!
У меня возник такой вопрос: есть ли какой то быстрый способ отображения скрытых строк в большом массиве информации отчета?Я делаю отчет и правой кнопкой мыши скрываю 90% строк, но периодически мне нужно их раскрывать. Дело в том, что выделить область, нажав правой кнопкой мыши функцию отобразить результата нет-строки не появляются, для их появления нужно вытягивать каждую строку вручную, что очень трудоемко. XL программа настолько продуманная и многогранная, что порой кажется удивительным возможность её создания, наверняка есть еще какая то функция для отображения строк, если функция отобразить не работает.

Светлана, не очень понял вашу проблему. Может быть, вам воспользоваться автофильтром для выбора тех или иных строк? Также в последних версиях Excel есть инструмент Таблица (Alt+T английское). Попробуйте. Не получится, пришлите файл в лику.

Добрый день. Вопрос может и глупый. Но не могу найти его решение. Есть простая таблица Excel с формулой СУММы в конце строки, каждый день мы удаляем значение в ячейках (соответственно обнуляется и итоговое значение) и заполняем новые. Теперь необходимо заполнять таблицу с нарастающим, т.е. удалять вчерашние значения, вводить свои, но итоговые результаты должны суммироваться с ранее введенными значениями, которые, как я ранее сказала, удалили. Как можно сохранить итоговое значение при удалении цифр в ячейках. Спасибо.

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

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

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

Добрый день! столкнулась с проблемой: необходимо перевести время из формата 00:16:03 просто в секунды. пробовала формулу преобразовать, все по нулям…. работаю в 2007 офисе. Спасибо!

Наталья, используйте формат [сс]

Спасибо! а я писала только одну с и ничего не получалось!!!

Добрый день. Подскажите, пожалуйста, как в Exel 2003 сделать следующее:
Есть ряд погрешностей (4;6;-7;3;-8). Как задать формулу, что бы с ряда этих чисел выбрало наибольшую погрешность?

Василий, используйте функцию =МАКС().

При использоании функции МАКС() exel определяет что максимальное отклонение (6), но правильный ответ (-8).
Вот как это задать я не знаю.

Тогда используйте что-то типа: =МАКС(ABS(4);ABS(6);ABS(-7);ABS(3);ABS(-8))

Все, я сделал, колега помог. Вот как вышло:
=ЕСЛИ(МАКС(А1:А5)>ABS(МИН(А1:А5)); МАКС((А1:А5);МИН(А1:А5))

Хорошее решение. Еще можно так:
=МАКС(МАКС(A1:A5);ABS(МИН(A1:A5)))

День добрый! Подскажите, каким образом можно реализовать задачу по пересчету долей множества значений при изменении руками доли одного из значений? Например, А — 30%, В — 20%, С — 50%, необходимо руками изменить долю С на 30%, а доли оставшихся без изменений показателей А и В должны автоматически пересчитаться у учетом первоначальных долей. Как-то так )))

Кирилл, если изменяться может только С, и соотношение между А и В постоянное, то:
А + В = 1 — С
А / В = 1,5
А = 1,5*(1 — С)/2,5
В = (1 — С)/2,5
Вставьте эти формулы, и будет вам счастье))

День добрый, Сергей Викторович! Если бы все было так просто, был бы рай ))) Измениться может любой показатель, а все остальные должны пересчитаться с учетом первоначальных долей, т.е. дельта, на которую изменяется один из показателей должна распределиться на остальные, пропорционально первоначальной доле.

Кирилл, не очень понятно. Допустим даже, что удалось написать формулу, по которой А, В и С взаимосвязаны (возможно, это получится сделать с помощью циклической ссылки и включения итерационных вычислений). Но в этом случае конструкция «живет» до первого занесения в А, В или С константы. Для последующий работы, вместо константы следует опять вернуть формулу. Не проще ли писать формулу каждый раз, когда возникает потребность пересчета?

Здравствуйте Сергей! Я использую формулу =РАЗНДАТ(F3;F7;"D")&" дней ". Как сделать чтобы "дней" склонялось — "день", "дня", "дней". Без использования макросов? Заранее благодарю!

Анатолий, склонение довольно сложная процедура. Могу предложить формулу на основе функции ВПР. Правда, потребуется выделить область листа для размещения таблицы подстановки:
=РАЗНДАТ($A$1;A2;"D")&" "& ВПР(ОСТАТ(РАЗНДАТ($A$1;A2;"D");100); $D$1:$E$100;2;ЛОЖЬ)

См. также Excel-файл

Здравствуйте Сергей! Включил в свою таблицу формулу на основе функции ВПР, предложенную Вами. Всё заработало отлично, «дней» склоняется правильно! Спасибо Вам огромное, что находите время для ответов, порой, на «бестолковые» вопросы. Ещё раз спасибо! С большим к ВАМ уважением!

Здравствуйте Сергей!
Подскажите пожалуйста, как сделать, чтобы в функции сумма не попадали ячейки со значением #value!
И еще, можно сделать так, чтобы они вообще скрывались на листе, пока в них не появится корректное значение?

Для суммирования диапазона ячеек, часть которых содержит ошибки, используйте функцию АГРЕГАТ (подробнее см., например, вторую часть заметки Суммирование по диапазону, содержащему ошибку). Чтобы скрыть значения ошибки в ячейке, используйте в ней функцию ЕСЛИОШИБКА.

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


Прокомментировать