Evolcom.ru

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

Как подсчитать, начинаются ли ячейки или заканчиваются определенным текстом в Excel?

Как правильно искать в таблицах Excel

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

Выполните Пуск ► Правка ► Найти и выделить ► Найти (или нажмите Ctrl+F), чтобы открыть диалоговое окно Найти и заменить. Если вы хотите заменить данные, выберите Главная ► Правка ► Найти и выделить ► Заменить (или нажмите Ctrl+H). Какая команда будет выполнена, зависит от того, какая из двух вкладок открыта в диалоговом окне.

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

Рис. 21.1. Вкладка Найти диалогового окна Найти и заменить

Рисунок 21.1. Вкладка «Найти» диалогового окна «Найти и заменить

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

Y V*smir* следует ввести в поле Найти, а затем нажать кнопку Найти все. Подстановочные знаки можно использовать не только для сокращения количества вводимых слов, но и для того, чтобы гарантировать, что вы найдете данные клиента, если они существуют на рабочем листе. Вы можете увидеть результаты, которые не соответствуют критериям поиска, но, по крайней мере, вы что-то получите.

Диалоговое окно «Найти и заменить» позволяет выполнять поиск с использованием двух подстановочных знаков:

  • ? — соответствует любому символу;
  • * соответствует любому количеству символов.

Кроме того, эти подстановочные знаки можно использовать для поиска числовых значений. В данном примере, если в строке поиска ввести 3*, вы получите список всех ячеек, содержащих значение, начинающееся с 3. Аналогично, если ввести 1?9, вы получите список записей, начинающихся с 1 и заканчивающихся 9.

Читайте так же:
Как посчитать медиану в видимых ячейках только в Excel?

Используйте символ тильды () для поиска вопросительного знака или звездочки

).
Например, следующая строка поиска находит текст *NONE*: -*N0NE

*
Чтобы найти символ тильды, поставьте в строке поиска две тильды.

Если поиск работает неправильно, проверьте эти три настройки (которые иногда меняются сами по себе).

  • Флажок Uppercase Matching — установите этот флажок, чтобы сопоставить регистр искомого текста с регистром указанного текста. Например, если вы ищете слово Иван и установите этот флажок, слово Иван не будет появляться в результатах поиска.
  • Флажок Полная ячейка: установите этот флажок, чтобы найти ячейку, которая содержит именно тот текст, который указан в строке поиска. Например, если вы введете слово Excel в поле поиска и установите флажок, вы не найдете ячейку, содержащую слово Microsoft Excel.
  • Выпадающий список окна поиска: список содержит три элемента: значения, формулы и примечания. Например, если вы зададите в поле поиска число 900 и выберете в раскрывающемся списке «Значения» поле поиска, вы не увидите ячейку со значением 900 в результатах поиска, если оно получено с помощью формулы.

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

Также обратите внимание, что вы не можете использовать окно «Найти и заменить» для поиска форматированных числовых значений. Например, если вы введете в поле поиска $5*, значение с денежным форматированием, подобное $54.00, не будет найдено.

В Excel существует множество форматов для дат, поэтому работа с ними может быть непростой. При поиске даты, к которой применено форматирование по умолчанию, Excel находит ее, даже если она отформатирована по-другому. Если в вашей системе используется формат m/d/y, то поисковая строка 10/*/2010 найдет все даты октября 2010 года, независимо от их формата.

Используйте пустое поле Заменить на, чтобы быстро удалить какую-нибудь информацию на рабочем листе. Например, введите — * в поле Найти и оставьте поле Заменить на пустым. Затем нажмите кнопку Заменить все, чтобы Excel нашел и убрал все звездочки на листе.

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

Автозаполнение ячеек в Excel

Автозаполнение ячеек Excel – это автоматический ввод серии данных в некоторый диапазон. Введем в ячейку «Понедельник», затем удерживая левой кнопкой мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (или в другую сторону). Результатом будет список из дней недели. Можно использовать краткую форму типа Пн, Вт, Ср и т.д. Эксель поймет. Аналогичным образом создается список из названий месяцев.

Автозаполнение дней недели в Excel

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

Автозаполнение последовательности чисел в Excel

Excel также может распознавать числа в тексте. Поэтому легко создать список кварталов. Введите «1 квартал» в ячейку и перетащите вниз.

Автозаполнение кварталов в Excel

Средний пользователь Excel ничего не знает об автозаполнении. Однако это не единственные приемы, и мы обсудим и другие эффективные приемы.

Автозаполнение в Excel из списка данных

Помимо дней недели и месяцев, вам, вероятно, понадобятся и другие списки. Например, часто бывает необходимо ввести названия городов, в которых расположены сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Первый шаг, который вам необходимо сделать, — это создать и сохранить (в правильном порядке) полный список имен. Перейдите в меню Файл — Параметры — Дополнительно — Общие — Изменить списки.

Изменить списки для автозаполнения в Excel

В следующем открывшемся окне вы увидите те списки, которые существуют по умолчанию.

Диалоговое окно для изменения списков в Excel

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

Добавление нового списка

Нажмите OK. Список создан и может быть использован для автозаполнения.

Помимо создания текстовых списков, вам часто приходится создавать последовательности чисел и дат. В начале статьи был рассмотрен примитивный вариант. Можно сделать и более интересные вещи. Сначала нужно выбрать одно или несколько первых значений ряда, а также диапазон (вправо или вниз), в котором будет расширяться последовательность значений. Затем вызываем диалоговое окно прогрессии: Главная — Заливка — Прогрессия.

Читайте так же:
Как преобразовать дату в день недели, месяц, название года или число в Excel?

Команда Прогрессия в Excel

Настройки диалогового окна Прогрессия

В левой части окна с помощью переключателя установите направление последовательности: вниз (по строкам) или направо (по столбцам).

Посередине вы выбираете желаемый тип:

  • Арифметическая прогрессия — каждое последующее значение умножается на число, указанное в поле Шаг
  • Геометрическая прогрессия — каждое последующее значение умножается на число, указанное в поле Шаг
  • Даты — создает последовательность дат. Выбор этого типа активирует переключатели справа, где можно выбрать тип устройства. Существует 4 варианта:
      • День — список календарных дат (в следующих шагах)
      • Рабочий день — последовательность рабочих дней (выходные пропускаются)
      • Месяц — изменяются только месяцы (число устанавливается как в первой ячейке)
      • Год — изменяются только годы.
      • Автозаполнение — эта команда аналогична перетаскиванию левой кнопкой мыши. Это означает, что Excel сам решает, следует ли продолжить числовой ряд или развернуть список. Если вы предварительно заполните две ячейки значениями 2 и 4, в других выделенных ячейках появятся значения 6, 8 и т.д. Если вы предварительно заполните больше ячеек, Excel рассчитает аппроксимацию линейной регрессии, т.е. предсказание прямолинейного тренда (интересная особенность — подробнее ниже).

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

      Предельное значение в прогрессии

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

      Автозаполнение чисел с помощью мыши

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

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

      Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
      Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.

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

      Если отпустить правую кнопку мыши при перетаскивании, сразу же откроется контекстное меню.

      Автозаполнение с помощью правой кнопки мыши

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

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

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

      Данные для с равномерным ростом

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

      Прогноз с помощью линейного тренда на диаграмме

      Для получения численного прогноза необходимо произвести расчеты на основе полученного уравнения регрессии (или непосредственно с помощью формул Excel). Это приводит к длинному ряду шагов, требующих хорошего понимания.

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

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

      Прогноз по методу экспоненциального приближения

      Наверное, нет более быстрого способа предсказания, чем это.

      Автозаполнение дат с помощью мыши

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

      Автозаполнение дат в Excel с помощью мыши

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

      Автозаполнение по месяцам

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

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

      Это практически все. В этом учебнике по Excel вы узнаете, как автоматизировать заполнение ячеек.

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