Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как повторить числовую последовательность в 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 по умолчанию.

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

Читайте так же:
Как подсчитать игнорирование скрытых ячеек / строк / столбцов в 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:

Выполняем свёртку по столбцу Товар 1,2,3.

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

Готовый результат запроса

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

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

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

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

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

Читайте так же:
Как пакетно удалить гиперссылки сразу в Excel?

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

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

Читайте так же:
Как подсчитать конкретное значение на нескольких листах?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

procent-chisla-ot-chisla-v-excel-formula

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

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

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

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

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

procent-chisla-ot-chisla-v-excel-formula

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

Читайте так же:
Как предотвратить удаление определенного листа в Excel?

Общие советы

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

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

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

Заключение

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector