Excel выбор ячеек с шагом
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Как протянуть формулу в ячейках разного размера?
В аттаче прикрепленный файл. Необходимо протянуть формулы по столбцам G, H и I. Но этого сделать.
Как протянуть формулу в Excel, пропуская одну строку?
Добрый день! Подскажите, нигде не могу найти. Необходимо протащить формулу в одном столбце.
Как в Excel протянуть формулу по горизонтали, чтобы она работала
Здравствуйте! Файл во вложении. Копированием или протягиванием не получается.Приходится каждую.
Как протянуть формулу, ориентируясь на количество строк левого столбца
Такая проблема, есть столбец А, в него начиная с А2 поступают данные, в В2 вписана формула, которую.
Решение
spbdab, по сути это задача для базы данных, а не для экселя.Расположите карточки объектов друг над другом в столбик и воспользуйтесь прямо на листе "Факт_недельный" функцией СМЕЩ (можно оставить компоновку как есть, но тогда придется забивать не одно, а два смещения, что усложняет задачу).
Формула в таблице на листе "Факт недельный" будет вида (ячейка С4):
Если компоновку менять не будете, то нужно будет вместо нуля учесть смещение по столбцам:
=СМЕЩ('24'!$P$5;17*([@№]-1); 0 )
Как вариант, ваши карточки можно также оформить как списки (умные таблицы), и обращаться напрямую независимо от местоположения. Спасибо вам огромное, вы очень меня выручили! )))) Карточки объектов поменял))
в G9 по аналогии.
П.С. в эксель объединение - ЗЛО.
Выражаю просто мега огромную благодарность. Так оперативно ответили, выручили очень сильно. Добрый день! Нужна помощь от опытных пользователей excel по составлению формулы для протягивания ячеек. Дано: на листе "Сводка" некоторые данные берутся с листа "Промо" (почти все данные из столбцов с A по L). При выделении диапазона A7:G9 на листе "Сводка" и протягивании вниз excel вставляет данные с шагом в 2 ячейки вниз, а нужно последовательно (как пример для одной ячейки: в "Сводке" ячейка A7 данные берет с листа "Промо" ячейки А3, а при протягивании в "Сводке" А10 данные берет с А6 "Промо", но должны быть с А4). а при протягивании в "Сводке" А10 данные берет с А6 "Промо", но должны быть с А4). В А10 вбейте =ИНДЕКС(Промо!$A$3:$A$8;(СТРОКА()-СТРОКА($A$7))/3+1)и протягивайте дальше выделение из трех строк.
Остальные ячейки по аналогии. А лучше справа на листе Сводка сделайте еще один столбец и туда пропишите номер строки из промо, из которого нужно забрать данные, и используйте тот же индекс().
Как EDIT TEXT изменять значения с определенным шагом?
Добрый день. Прошу Вашей помощи. можно щелчком мыши по EDIT1 изменять значение, ну например с шагом.
Как вывести значения функции в разных точках с определенным шагом?
Нужно протабулировать функцию в промежутке от 1 до 1000 с шагом 0.1 (то есть нужно вывести значение.
Как подставить в формулу числа в диапазоне от 0 до 0,1 с шагом 0,0001?
Здравствуйте. Подскажите как написать простую задачу. Имеется формула double x=0; double .
Суммирование ячеек с определенным шагом
Доброе утро! Подскажите пожалуйста на моем примере. Из листа1 суммирую столбцы, например.
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :
Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :
Способ 4. Суммирование каждой 2-й, 3-й. N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
Предположим, вы инвестируете в акции и вам нужно каждые 20 минут регистрировать изменение цен определенной акции. Что бы вы сделали, чтобы быстро создать временной список с шагом в 20 минут? В этой статье показано несколько методов ее решения.
Добавьте время с шагом в часах / минутах / секундах с помощью формулы
Вы можете применить следующие формулы, чтобы добавить время с шагом часы / минуты / секунды в Excel.
1. Введите время начала в пустую ячейку (говорит A1), затем выберите следующие пустые ячейки, которые необходимо заполнить с шагом приращения. Здесь я выбираю A2: A15.
Для добавления времени с шагом 20 минут:
Введите формулу = A1 + ВРЕМЯ (0,20,0) в панель формул, а затем нажмите Ctrl + Enter ключ одновременно. Вы можете видеть, что время каждой ячейки добавляется с шагом 20 минут и сразу же отображается в выбранном диапазоне.
Для добавления времени с шагом 20 секунд:
Измените формулу выше на = A1 + ВРЕМЯ (0,0,20) , а затем нажмите Ctrl + Enter ключи одновременно. Смотрите скриншот:
Для добавления времени с шагом 1 час:
Пожалуйста, измените формулу на = A1 + ВРЕМЯ (1,0,0) , а затем нажмите Ctrl + Enter ключи одновременно. Смотрите скриншот:
Внимание: В формуле = A1 + ВРЕМЯ (0,20,0) , первое число в скобках означает час, второе число - минуты, а последнее - секунды. Вы можете изменить их в зависимости от вашего приращения времени.
Добавьте время с шагом часов / минут / секунд с помощью ручки заполнения
Ручка заполнения также может помочь вам добавить время с шагом часов / минут / секунд в Excel. Пожалуйста, сделайте следующее.
1. Введите время начала и время первого приращения в первых двух ячейках, говорит A1 и A2. Смотрите скриншот:
2. Выберите ячейки A1 и A2, затем перетащите маркер заполнения вниз, пока не заполнятся необходимые значения времени приращения. См. Снимок экрана:
Добавьте время с шагом часов / минут / секунд с помощью Kutools for Excel
Kutools for Excel собирает множество часто используемых формул. А его формулы добавления часов / минут / секунд к дате помогают быстро добавлять время с шагом в часы / минуты / секунды в Excel без запоминания формул.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку (A2) для вывода результата, затем щелкните Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:
2. в Помощник по формулам диалоговое окно, настройте следующим образом.
- В Выберите формулу выберите Добавить часы к дате.
Советы: Вы можете проверить Фильтр поле введите ключевое слово, чтобы отфильтровать формулу сертификата по мере необходимости. - Укажите ячейку времени, в которой нужно добавить час Дата Время коробка,
- Введите разницу во времени между этими двумя часовыми поясами в поле Число коробка,
- Нажмите OK кнопку.
Затем вы можете увидеть новое время с шагом в 1 час на основе вычисленного значения ячейки A1.
3. Продолжайте выбирать ячейку результата (A2), перетащите маркер заполнения вниз, чтобы получить другое новое время приращения.
Вы можете применить формулу Добавить минуты к дате или Добавить секунды к дате, чтобы создать список времени с приращениями минут или секунд в Excel. Смотрите скриншот:
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: