Перейти к содержимому

Excel

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

В Excel существует дополнительное (альтернативное) контекстное меню перетаскивания с полезными опциями (Alternate Drag and Drop menu). Чтобы получить к нему доступ выделите ячейку или диапазон ячеек. Перейдите к правому краю данных. Щелкните правой кнопкой мыши и перетащите границу вправо в новую область. Отпустите кнопку мыши.

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

Если нужно найти сумму цифр в строке текста, можно воспользоваться алгоритмом: 1 * количество единиц в строке + 2 * количество двоек в строке + … + 9 * количество девяток в строке = результат

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

Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

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

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

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

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k).

Несмотря на то, что привычный тип ссылок А1 является доминирующим, существуют ситуации, когда стиль R1C1 существенно эффективнее. Такой стиль удобнее при записи макросов, программировании VBA, использовании функции ДВССЫЛ и условном форматировании.

Функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Если вы хотите суммировать только видимые строки, используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Эта функция чаще используется в таблицах, но ничего не мешает использовать ее, как обычную функцию листа.

Функция ДВССЫЛ используется, когда нужно сослаться на ячейку, чей адрес будет определен на основе вычислений. ДВССЫЛ также справится, когда предварительно вычисляется лист, на который нужно сослаться. Билл Джелен описывает трюк, который справится, даже если имя листа представляет собой дату. Более того ДВССЫЛ позволяет ссылаться на диапазон ячеек, который затем используется внутри функций ВПР или СУММЕСЛИ.

Если у вас есть множество листов с идентичной структурой, например, 12 листов с коммерческими результатами за каждый месяц, вы можете найти сумму в одной и той же ячейке на каждом листе с помощью 3D-формулы. Если листы имеют имена Янв, Фев и т.д., а найти нужно сумму в ячейке В4 за все месяцы, используйте формулу =СУММ(Янв:Дек!B4).

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

До введения VBA, макросы писали на языке xlm (Excel Macro). Язык использовал макрофункции, т.е., функции листа макросов Excel 4.0. Этот язык до сих пор поддерживается Microsoft для совместимости с предыдущими версиями Excel. Язык xlm среди прочих содержит функцию Получить.Ячейку (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция ЯЧЕЙКА(). На самом деле, Получить.Ячейку может рассказать о 66 различных атрибутах ячейки, в то время, как функция ЯЧЕЙКА возвращает лишь 12 параметров.

Начиная с версии 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-дневка или сменный). Подробности здесь.

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

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

138 комментариев для “Excel”

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

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

  3. Анастасия

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

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

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

  6. Виталий

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

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

  8. Виталий

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

  9. Валерий

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

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

  11. Ярослав

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

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

  13. Георгий

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

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

  15. Евгений

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

  16. Уведомление: Excel | Pearltrees

  17. Добрый день! Не могли бы Вы дать совет как сформировать график-радар на изменяющихся данных. При условии, если число расчитываемое меньше или равно нулю было красным цветом на графике, а выше нуля зеленым.
    Сценарий 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

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

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

    Спасибо

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  40. Владимир

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

  41. Владимир

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

  42. Надежда

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

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

  43. Виктория

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

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

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

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

  44. Александр

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

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

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

  46. Александр

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

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

  47. Александр

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

    спасибо

  48. Александр

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

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

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

    Александр

  49. Маргарита

    Добрый день!

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

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

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

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

    В одной таблице имеются данные о периоде принятия работ/услуг (Период 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

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

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

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

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

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

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

  54. Добрый день!

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

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

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

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

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

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

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

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

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

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

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

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

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

  61. Нина Экономист

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

  62. Станислав

    Добрый день!

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

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

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

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

    Спасибо.

    p.s.

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

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

  64. Станислав

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

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

  66. Светлана

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

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

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

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

  70. Наталья

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

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

  72. Наталья

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

  73. Наталья

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

  74. Василий

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

  75. Василий

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

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

  77. Василий

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

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

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

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

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

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

  83. Анатолий

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

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

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

  85. Анатолий

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

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

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

  88. Александр

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

  89. Александр

    Добрый день.
    Подскажите формулу подсчета суммы значений в диапазоне ячеек при условии, если значение в ячейке >8, то считать как 8, а если <=8, считать как значение данной ячейки

  90. Александр, могу предложить такую формулу: =СУММЕСЛИ(A1:A27;"<8«)+ СЧЁТЕСЛИ(A1:A27;">=8")*8
    Здесь предполагается, что диапазон суммирования А1:А27. Первая функция суммирует все ячейки для которых значение меньше 8, вторая — находит число ячеек, для которых значение больше или равно 8, и умножает это число на 8.

  91. Здравствуйте! Большое спасибо за статью, очень много интересных возможностей.
    Мне достался «по наследству» от предшественников файл в котором 8500 строк и в одной из колонок в финансовом формате данные в разных валютах. Есть ли в Екселе функция, позволяющая разделить значения по типу валют? Например в разные столбцы Файл большой и через фильтрацию сложно и долго.
    Спасибо!

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

  93. Игорь, добавьте проверку условия, что ячейка непустая (строка 9):

  94. Здравствуйте. Подскажите пожалуйста, как сделать такую вещь: сумма двух ячеек сохраняется во второй ячейке, перезаписывая предыдущее своё значение, и при следующем вводе в первую ячейку, вторая суммирует своё текущее значение с нововведённым в первую, и опять сохраняет полученную сумму в себе, до следующего ввода в первую ячейку? т.е., например, в а1 0 , и в б1 0, вводим в а1 2, в б1 появляется 2, вводим в а1 3, в б1 появляется 5

  95. Сергей, наберите в ячейке В1 формулу =А1+В1. Пройдите по меню: Файл->Параметры. В открывшемся окне перейдите на вкладку Формулы. Поставьте галочку Включить итеративные вычисления. Установите Предельное число итераций = 1.
    Использование циклической ссылки для вычислений
    Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

  96. всплыл небольшой косячёк: когда я закрываю таблицу с сохранением изменений, то к B1 ещё раз прибавляется последнее введённое в А1 значение. Т.е. я , например, ввёл 400, и потом 100 — в B1 стало 500 , я закрыл с сохранением, потом открыл, а там 600 вместо 500. Может можно как-то сделать, чтоб всё осталось так же, только А1 ещё-бы и обнулялась/очищалась после каждого ввода?

  97. макрос на открытие книги, например:
    Sub Workbook_Open()

    Range("G2") = ""

    End Sub

  98. Виталий

    ЗДРАВСТВУЙТЕ!
    График с программы сохраняется как .csv
    Затем при открытии файла в экселе получаю только 1 колонку со всеми данными в ней через запятую.
    Как можно сделать просмотр/ преобразование на несколько колонок этой информации?
    СПАСИБО!!!!

  99. Могу предложить такой вариант. Откройте файл в Word, сохраните с расширением txt. Откройте его в Excel. В мастере импорта укажите «с разделителями». В качестве разделителя выберите запятую. Текст распределится по колонкам. Проблема возникнет, если запятая используется не только в качестве разделителя, но и в десятичных числах.

  100. Приветствую. Интуитивно понимаю что сделать можно… но не понимаю как… Неделю уже штудирую материалы по power query и power pivot, такое ощущение что хожу вокруг да около… подскажите пожалуйста с чего начать, какие инструменты мне помогут реализовать поставленную задачу.

    задача такая:Есть два файла, основной и подгружаемый. Связаны они идентификатором столбцом «код». Как сделать так, чтобы при присоединении подгружаемого файла к основному происходило следующее. 1)При совпадении по столбцу «код» в обоих файлах, в основном файле подменялись значения из подгружаемого в строке совпадения по столбцам «остаток» и «норм» и желательно выделение этих строк отдельным фильтром. 2)Если в файле остались строки не нашедшие совпадения по столбцу «код» с подгружаемым файлом, то заменить в этих строках в столбце «остаток» на 0 и желательно выделение этих строк отдельным фильтром. 3)Если в подгружаемом файле появились новые строки не нашедшие совпадения по столбцу «код» с основным файлом, то добавить эти строки в в основной файл со всеми данными по столбцам в этих строках и желательно выделение этих строк отдельным фильтром.
    Буду благодарен за любую помощь.

  101. Снежана

    Обратите внимание, у вас попозло форматирование (где-то в html что-то не закрыли)
    метода Монте-Карло Excel

  102. Снежана, вы о заметке Использование метода Монте-Карло для расчета риска? Какой браузер вы используете? У меня в Chrome всё Ok.

  103. Снежана, еще раз спасибо. Поправил и проверил всю страницу))

  104. Здравствуйте Сергей Викторович!

    Я к сожалению в экселе далеко не гуру.
    И хоть понимаю все сложность задачи, может все же подскажите, что-то не очень уж сложное для склонения ФИО. Мне бы было достаточно и родительного падежа, но с макросами и тому подобными «фишками» я к сожалению на «вы».

    Сердечно благодарю

  105. Анастасия

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

  106. Сергей Багузин

    Анастасия, пришлите файл s_bag@mail.ru с подробным описанием, что нужно получить.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *