Ref-sgain.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выберите нужный тип из середины:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      15-е число всегда фиксировано, а последний день месяца всегда последний день месяца.

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

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

      7 формул для работы с текстами объявлений в Excel

      Татьяна Брызгалова

      Какие программы и сервисы вы используете при работе над текстовыми объявлениями контекстной рекламы? MS Excel — одна из первых программ, которую я бы рекомендовал, поскольку ее можно использовать для решения многих задач. Не зря Яндекс. Директ и Google AdWords позволяют загружать кампании с помощью xls- и csv-файлов. На этой неделе мы рассмотрим семь формул, полезных для работы с текстом и ключевыми фразами в Excel.

      1. Функция ДЛСТР — Подсчет символов в ячейке

      Эта формула может быть знакома многим, но есть еще один полезный совет. В строке ввода для соседней ячейки введите: = DLSTR(A1), где DLSTR — функция, (A1) — позиция ячейки в круглых скобках. Чтобы подсчитать количество символов в строке.

      formuly-excel-1

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

      1. Сначала выберите столбец с цифрами
      2. , затем в строке главного меню выберите Условное форматирование -> Правила выделения ячеек -> Еще,

      formuly-excel-2

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

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

      formuly-excel-3

      2. Функция СЖПРОБЕЛЫ— Удаление лишних пробелов

      Если вы копируете большой объем текста, в нем могут появиться лишние пробелы (до, после или между словами), что затруднит последующую работу с ним. Функция SPLINGES позволяет удалить все лишние пробелы, кроме одиночных, в указанных ячейках. Мы вводим формулу = SPLEDS(A1), где (A1) — ячейка, которую нужно изменить, и расширяем ее на необходимое количество ячеек.

      formuly-excel-4

      3. Формула СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))) — Преобразует первое слово ячейки с прописной буквы

      Чтобы преобразовать существующее ключевое слово в заголовок или текст объявления без использования стороннего сервиса, используйте следующую формулу: =CREATE(LEFTWORD(A1));RIGHTWORD(A1;(DLSTR(A1)-1)))), где A1 — это нужная ячейка.

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

      formuly-excel-5

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

      1. Выделите все измененные ячейки,
      2. Нажмите Ctrl+C;
      3. Затем нажмите Ctrl+V, не переходя к другим ячейкам;
      4. Выберите «Только значения» из выпадающего меню «Вставка».

      Теперь ячейки содержат только текстовые значения!

      4. Формула СЦЕПИТЬ(А1;“ ”;А2 ;“ ”;А3;…) — Объединяет 2 и более ячеек в одну с добавлением пробела между ними

      Давайте распишем формулу более подробно:

      =CREATE(A1;»;A2 ;»;A2 ;»;A3;…), где A1, A2, A3 — ячейки, которые необходимо объединить,

      » » — пробелы, которые должны быть размещены между содержимым ячеек.

      Если комбинация символов » » не применяется, ячейки вставляются без пробелов.

      formuly-excel-6

      5. Применение функций СЦЕПИТЬ, ЕСЛИ и ДЛСТР — Добавляет продающее окончание

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

      Во-первых, определитесь с концом. Для разного количества допустимых символов можно взять, например, «Купите со скидкой 30%! «, «Скидка 30%! «, «!». Не забудьте добавить точку после содержимого первой ячейки и пробел перед концом продажи. Другими словами, окончания продаж должны иметь следующий вид: «. Заказывайте со скидкой 30%! «, «. Скидка 30%!» или «! «.

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

      formuly-excel-7

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

      formuly-excel-8

      После этого вставляем формулу =If(DLSTR(A2)<7;CLICK(A2;". Покупайте со скидкой 25%!");IF(DLSTR(A2)<20;CLICK(A2;". Скидки 25%");CLICK(A2;"!")) в ячейку, где получаем результат: две связанные ячейки в зависимости от заданных условий.

      formuly-excel-9

      6. Функция СИМВОЛ — Приводит ключевое слово к фразовому соответствию

      При необходимости привести ключевые слова в широком соответствии к фразовому соответствию можно использовать формулу =СИМВОЛ(34)&СЖПРОБЕЛЫ(A1)&СИМВОЛ(34), где (А1) – ячейка к изменению, СИМВОЛ(34) — знак кавычек.

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

      formuly-excel-10

      7. Функция ПОДСТАВИТЬ — Приводит ключевую фразу к модифицированному широкому соответствию Google AdWords

      Если вам нужно перевести фразы широкого соответствия в модифицированное широкое соответствие Google AdWords, используйте формулу =»+»&SEND(SPACE(A1);» «; «+»), где (A1) — это ячейка, которую нужно модифицировать.

      formuly-excel-11

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

      Условное форматирование в Excel

      Использование условного форматирования в Excel — тема этого урока.

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

      Основы условного форматирования в Excel

      Условное форматирование позволяет нам:

      • Значения цвета
      • Изменить шрифт
      • Установить формат границы

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

      Где находится условное форматирование в Эксель?

      Кнопка «Условное форматирование» находится на панели инструментов, на вкладке «Главная»:

      Где находится пункт условное форматирование в Excek

      Как сделать условное форматирование в Excel?

      Когда используется условное форматирование, система должна выполнить две настройки:

      • Какие ячейки вы хотите отформатировать;
      • Каким условиям вы хотите назначить формат.

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

      • В таблице с данными выберите диапазон, к которому мы хотим применить цветовое выделение:

      Условное форматирование в Excel - выделение диапазона данных

      • Перейдем на вкладку «Главная» на панели инструментов и кликнем на пункт «Условное форматирование». В выпадающем списке вы увидите несколько типов формата на выбор:
        • Правила выбора первого и последнего значения
        • Гистограммы
        • Шкалы цветов
        • Наборы значков

        Условное форматирование - правило меньше

        Также возможны следующие условия:

        1. Значения больше или равны определенному значению;
        2. Выделить текст, содержащий определенные буквы или слова;
        3. Выделить цветом дубликаты;
        4. Выделить конкретные даты.
        • Во всплывающем окне в поле «Форматировать ячейки которые МЕНЬШЕ» укажем значение «0», так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

        Форматирование ячеек в Excel меньше чем

        • Для присвоения формата можно использовать предопределенные цветовые палитры или создать свою собственную. Для этого нажмите на элемент :

        Пользовательский формат условного форматирования в Эксель

        • Во всплывающем окне формата укажите:
          • Цвет заливки
          • Шрифта
          • Границ ячеек

          Пользовательский формат ячеек

          • По завершении настроек нажмите кнопку «ОК».

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

          условное форматирование отрицательных значений

          Как создать правило

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

          • Выделим диапазон данных. Кликнем на пункт «Условное форматирование» в панели инструментов. В выпадающем списке выберем пункт «Новое правило»:

          Создание собственного правила условного форматирования в Excel

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

          условное форматирование на основе значений между числами

          • Кликнем на кнопку «Формат» и зададим формат, как мы это делали в примере выше. Нажмите кнопку «ОК»:

          условное форматирование по собственному правилу

          Условное форматирование по значению другой ячейки

          В приведенных выше примерах мы назначили формат ячейкам на основе их значений. Формат ячейки можно задать на основе значений в другой ячейке Excel. Если у нас есть данные о курсе доллара в таблице, мы можем выделить ячейки цветом в соответствии с правилом. Когда курс доллара ниже, чем в предыдущем месяце, значение курса в текущем месяце будет выделено цветом.

          Чтобы создать условие на значение другой ячейки, выполните следующие действия:

          • Выделим первую ячейку для назначения правила. Кликнем на пункт «Условное форматирование» на панели инструментов. Выберем условие «Меньше».
          • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку «ОК».

          условное форматирование в Excel по значению другой ячейки

          • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт «Условное форматирование». Выберем в выпадающем меню «Управление правилами» => кликнем на кнопку «Изменить правило»:

          Изменение правила ячейки по значению другой ячейки

          • В поле слева всплывающего окна «очистим» ссылку от знака «$». Нажимаем кнопку «ОК», а затем кнопку «Применить».

          Условное форматирование в Excel по значению другой ячейки 2

          • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на «валик» и присвоим формат остальным ячейкам:

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

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

          Применение условного форматирования по значению другой ячейки в Эксель

          Как применить несколько правил условного форматирования к одной ячейке

          Можно применять несколько правил к одной ячейке.

          Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов — зеленым цветом, если выше 20 градусов — желтый, если выше 30 градусов — красным.

          Чтобы применить несколько условий к одной ячейке, сделайте следующее

          • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту «Условное форматирование» на панели инструментов => выберем условие выделения «Больше…» и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

          условное форматирование в Excel по нескольким условиям

          • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту «Условное форматирование» и перейдем в раздел «Управление правилами». Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку «Остановить, если истина». Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку «Применить» и «ОК»:

          Условное форматирование в Эксель с несколькими условиями

          Применив их, наша таблица с данными температуры «подсвечена» корректными цветами, в соответствии с нашими условиями.

          Таблица с примененным условным форматированием по нескольким условиям

          Как редактировать правило условного форматирования

          Для редактирования назначенного правила выполните следующие действия

          • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
          • Перейдите в пункт меню панели инструментов «Условное форматирование». Затем, в пункт «Управление правилами». Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку «Изменить правило»:

          Изменение правила ячейки по значению другой ячейки

          • После внесения изменений нажмите кнопку «ОК».

          Как копировать правило условного форматирования

          Чтобы скопировать формат в другие ячейки, выполните следующие действия

          • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов «Формат по образцу».
          • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

          Как удалить условное форматирование

          Ниже приведены шаги по удалению формата:

          • Выделите ячейки;
          • Нажмите на пункт меню «Условное форматирование» на панели инструментов. Кликните по пункту «Удалить правила». В раскрывающемся меню выберите метод удаления:

          удаление правил условного форматирования в Эксель

          Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

          Спасибо, очень полезный сайт!
          Вопрос:
          Есть таблица с остатками на складе. Последний столбец «остаток» — это формула «приход» минус «выдали».
          Пытаюсь по вашей статье создать правило автоматической окраски строки со значением «0» в ячейке «остаток». Но при создании условного форматирования выдаёт ошибку — ячейка уже содержит формулу, а не просто число. К тому же окрашиваются только ячейки с нужным значением, а не вся строка. Есть способ решить такую проблему?

          голоса
          Рейтинг статьи
          Читайте так же:
          Как создать диаграмму на нескольких листах в Excel?
Ссылка на основную публикацию
Adblock
detector