Как повторить числовую последовательность в Excel?

Распределяем список по наборам

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

Мы покажем вам несколько вариантов, чтобы облегчить вам работу.

Способ 1. Вручную через сводную таблицу

Этот метод не обновляется автоматически, поэтому он подходит только в тех случаях, когда необходимо простое, быстрое и своевременное решение. Сделайте следующее:

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

2 . Поместите поле Корзина в область строк, поле Товар — в область столбцов, а область Значения — в панель полей сводки. На выходе должна получиться сводка с номером на пересечении товара и корзины, если товар находится в корзине:

3 . Если выбрать центральную область данных без включения итогов (диапазон E3:S8 в примере выше), ее можно скопировать и вставить в любое место рабочего листа.

4 . В скопированной области снова выделите только средний блок с числами без меток, а затем заставьте Excel выделить только ячейки с числами, нажав F5 — Перейти к Специальным — Константы:

5. Аккуратно, чтобы не нарушить получившееся выделение, вводим в первую выделенную ячейку (она будет белой) ссылку на соответствующий продукт (в нашем случае это будет ссылка на Брокколи), нажимаем F4, чтобы оставить знак доллара только перед числом (то есть зафиксировать строку) и комбинацию клавиш Ctrl + Enter (чтобы ввести одну и ту же формулу во все выделенные ячейки сразу):

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

7. Используя тот же трюк, который мы использовали в шаге 4 с клавишей F5 — Выделить, на этот раз мы выделим пустые ячейки и удалим их, сдвинув shift влево:

Способ 2. Обновляемый запрос через Power Query

Этот метод немного сложнее, но он позволяет нам не повторять весь процесс в будущем, если исходные данные изменятся — нам просто нужно будет обновить наш запрос. Для решения мы будем использовать надстройку Power Query, которую можно бесплатно загрузить с сайта Microsoft для Excel 2010-2013. Начиная с Excel 2016, Power Query теперь встроена в Microsoft Excel по умолчанию.

Для решения нашей проблемы, пожалуйста, сделайте следующее

1 . Он преобразует исходную таблицу в интеллектуальную поворотную таблицу, чтобы вам не пришлось думать об изменении ее размера. Можно использовать команду Главная — Форматировать как таблицу или комбинацию клавиш Ctrl + T.

2 . Загрузите интеллектуальную таблицу в Power Query с помощью команды Data — From Table/Range.

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

4 . Сгруппируйте таблицу по корзинам, выбрав Группировать по на вкладке Преобразование и установив операцию Все строки:

Наша исходная таблица будет «разбита» на корзины, содержимое которых будет находиться в подтаблицах в последней колонке:

5 . Выберите Add Column — Custom Column, чтобы добавить индексный столбец к каждой вложенной таблице с номером строки с помощью функции M Table.AddIndexColumn:

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

7 . Добавьте слово «Товары» к нумерации в последнем столбце с помощью команды Трансформация — Формат — Добавить префикс.

8 . В качестве последнего штриха повернитесь на последнем пронумерованном столбце с помощью команды Transform — Pivot Column:

Проблема решена! Осталось выгрузить результаты обратно в Excel через Home — Close & Load — Close & Load to. В данном случае закрыть и загрузить в Home (закрыть и загрузить в Home).

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

Способ 3. Функции динамических массивов

Этот метод работает только для тех, кто имеет последние обновления Office 365, в которых добавлена поддержка динамических таблиц и новые возможности функций Excel: SORT, UNIC, FILTER, LAST и SLOWMASSIVE. Если у вас есть Office 365, но у вас еще нет этих функций, это означает, что вы еще не получили соответствующие обновления (они рассылаются волнами и не всем пользователям одновременно), поэтому вам придется немного подождать.

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

1 . Он преобразует исходную таблицу в интеллектуальную поворотную таблицу, чтобы вам не пришлось думать об изменении ее размера. Можно использовать команду Главная — Форматировать как таблицу или комбинацию клавиш Ctrl + T.

2 . Наша интеллектуальная таблица Table4 дает отсортированный список уникальных названий корзин с помощью функций SORT и UNIQUE соответственно:

3. Вы можете отфильтровать все товары в корзинах с помощью функции FILTER:

По умолчанию функция FILTER отображает результаты в столбце. Чтобы расширить их по горизонтали — до линий — необходимо дополнительно использовать функцию транспонирования TRANSPOSE.

Поиск в таблице Excel — простые способы

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

Простой поиск

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

Когда откроется окно «Найти и заменить», введите искомое значение и нажмите «Найти все».

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

Текстовый поиск осуществляется аналогичным образом. В этом случае текст для поиска вводится в строку поиска.

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

Расширенный поиск

Предположим, вы хотите найти все значения между 3000 и 3999. В этом случае в поле поиска введите 3. Подстановочный знак «?» заменяет все остальные символы.

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

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

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

Это означает, что поиск производился в ячейках, где существует формула, а не в значении. Число 3 в них дало три ошибочных результата. Если в качестве области поиска выбрать «Значения», поиск будет осуществляться только в данных, а ошибочные результаты, связанные с ячейками формул, исчезнут.

Чтобы удалить единственный оставшийся неверный результат в первой строке, выберите «Вся ячейка» в окне расширенного поиска. Тогда результат поиска будет на 100% точным.

Этого результата можно было бы достичь, сразу выбрав «Вся ячейка» (даже оставив «Формулы» в поле «Поиск»).

Теперь нажмем на опцию «Поиск».

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

Следующий пункт в окне расширенного поиска — «Browse», который имеет два значения. По умолчанию установлено значение «по строкам», что означает, что ячейки сканируются в порядке возрастания строк. Выбор второго значения, «по столбцу», изменяет только направление поиска и порядок отображения результатов.

При поиске в документах Microsoft Excel можно также использовать другой подстановочный знак — «*». Если под символом «?» подразумевался любой символ, то «*» заменяет любое количество символов. Вот скриншот из поиска слов в Луизиане.

Иногда при поиске необходимо учитывать регистр символов. В случае, когда louisiana пишется с маленькой буквы, результаты поиска останутся неизменными. Однако выбор опции «Соответствие регистру» в окне расширенного поиска не сработает. Программа будет считать, что Louisiana и louisiana — это разные слова, и, естественно, не найдет первое.

Разновидности поиска

Поиск совпадений

Иногда необходимо найти повторяющиеся значения в таблице. Чтобы найти совпадение, необходимо сначала выбрать диапазон поиска. Затем на той же вкладке Главная в группе Стили откройте инструмент Условное форматирование. Затем последовательно выберите «Правила выбора ячеек» и «Повторяющиеся значения».

Результат вы можете увидеть на скриншоте ниже.

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

Фильтрация

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

1. Выделить первый столбец с заголовком.
2. На той же вкладке «Главная» в разделе «Редактирование» открыть инструмент «Сортировка и фильтр», и щёлкнуть пункт «Фильтр».
3. В верхней строчке столбца B появляется треугольник – условный знак списка. После его открытия в списке «Числовые фильтры» щёлкнуть пункт «между».
4. В окне «Пользовательский автофильтр» следует ввести начальное и конечное значение плюс OK.

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

Различные варианты поиска обсуждались на примере Excel 2010. Как искать в Excel в других версиях? Существует разница в изменении фильтрации в версии 2003. В меню Данные последовательно выберите Фильтр, Автофильтр, Условие и Пользовательский автофильтр.

Процент числа от числа в Excel (формула)

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

Формула для расчета

Каждый раз, когда вы хотите вычесть процент от числа из числа, вы должны ознакомиться с формулой, используемой для расчета: =»число»/»всего «*100.

Важно: Прежде чем начать использовать формулу в расчетах, найдите вкладку «Число» на главной панели инструментов, переключитесь на «Процент».

Практический пример

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

1. Выберите любую ячейку с помощью LKM.
2. Введите формулу. Убедитесь, что вы начинаете с «=».
3. Нажмите кнопку «Ввод».

Желаемый результат должен появиться в поле.

Пример вычисления процента от числа в свободной ячейке таблицы

Как вычесть проценты в заполненной таблице

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

1. Чтобы получить процентное соотношение числовых значений в ячейках столбца, щелкните левой кнопкой мыши свободную ячейку рядом с начальной ячейкой этого столбца.
2. Введите символ «=».
3. Щелкните ячейку рядом с введенным числовым значением.
4. Введите символ «-«.
5. Нажмите на ту же ячейку еще раз, чтобы повторно ввести ее координаты в ячейку.
6. Заполните формулу нужным количеством процентов, введите «%».

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

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

Результаты заданных расчетов должны появиться в соседнем столбце.

Автоматизирует процесс вычисления процентных соотношений для каждой ячейки в столбце

Как вычесть проценты в таблице с зафиксированным %

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

1. Щелкните левой кнопкой мыши на ячейке рядом с числовым значением, для которого вы хотите получить результат.
2. Введите знак «=», нажмите на ячейку с начальным значением.
3. После первых координат ячейки поставьте знак «-«, снова нажмите на ячейку с числовым значением, чтобы повторить координаты.
4. Введите знак «*», затем нажмите левую кнопку мыши в ячейке, где задается процент.
5. Последнее действие — нажать кнопку «Enter», чтобы получить желаемый результат. Затем вы можете автоматически рассчитать результат для каждой ячейки ниже.

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

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

Общие советы

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

1. В таблице можно настроить отображение отрицательных значений в процентах. Для этого необходимо использовать окно редактирования «Условное форматирование». Чтобы получить доступ к этому разделу, перейдите на вкладку «Главная», на главной панели инструментов перейдите в группу «Стили». В окне редактирования необходимо нажать на опцию «Форматировать только те ячейки, которые содержат». Затем вы увидите три поля, которые необходимо заполнить. В первом нужно выбрать «Значение ячейки», во втором — «меньше чем», в третьем написать «0».
2. Выделите отрицательные процентные значения (для удобства они могут быть выделены разными цветами). Для этого необходимо создать пользовательский числовой формат. Порядок действий следующий: перейдите на вкладку «Формат ячеек», выберите пункт «Число», перейдите в категорию «Все форматы». Затем в свободном поле следует указать пользовательский формат. Его главная особенность — добавление цвета выделения слова в квадратных скобках.

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

Ячейки, к которым применяется «общий» параметр, не связаны с определенным числовым параметром.

Заключение

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