Формула замены в excel


ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)

В этой статье описаны синтаксис формулы и использование ЗАМЕНИТЬ и ЗАМЕНИТЬБ  в Microsoft Excel.

Описание

Функция ЗАМЕНИТЬ заменяет указанное число символов текстовой строки другой текстовой строкой.

Функция ЗАМЕНИТЬ заменяет часть текстовой строки, соответствующую заданному числу байтов, другой текстовой строкой.

Важно: 

  • Эти функции могут быть доступны не на всех языках.

  • Функция ЗАМЕНИТЬ предназначена для языков с однобайтовой кодировкой, а ЗАМЕНИТЬБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

  • Функция ЗАМЕНИТЬ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

  • Функция ЗАМЕНИТЬБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ЗАМЕНИТЬБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)

ЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст)

Аргументы функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ описаны ниже.

  • Стар_текст    Обязательный. Текст, в котором требуется заменить некоторые символы.

  • Начальная_позиция    Обязательный. Позиция символа в старом тексте, начиная с которого требуется выполнить замену новым текстом.

  • Число_знаков    Обязательный. Число символов в старом тексте, которые требуется ЗАМЕНИТЬ новым текстом.

  • Число_байтов    Обязательный. Число байтов старого текста, который требуется ЗАМЕНИТЬБ новым текстом.

  • Нов_текст    Обязательный. Текст, который заменит символы в старом тексте.

Пример

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

Данные

абвгдеёжзий

2009

123456

Формула

Описание (результат)

Результат

=ЗАМЕНИТЬ(A2;6;5;"*")

Заменяет пять знаков последовательности абвгдеёжзий одним знаком *, начиная с шестого знака (е).

абвгд*й

=ЗАМЕНИТЬ(A3;3;2;"10")

Заменяет последние два знака (09) числа 2009 на 10.

2010

=ЗАМЕНИТЬ(A4;1;3;,"@")

Заменяет первые три знака последовательности 123456 одним знаком @.

@456

Поиск или замена текста и чисел на листе

Поиск

Чтобы найти что-то, нажмите CTRL+F или перейдите на главная > редактирования> Найти & Выберите >Найти.

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

  1. В поле Найти что: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Найти : и выберите последний элемент поиска из списка.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  2. Нажмите кнопку Найти все или Найти далее, чтобы выполнить поиск.

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

  3. Нажмите кнопку>> , чтобы при необходимости определить поиск.

    • В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

    • Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения, заметки или примечания.

      Примечание:   Формулы, значения, заметки и примечания доступны только на вкладке Найти. На вкладке "Заменить" доступны только формулы.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение всего содержимого ячеек . Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти : .

  4. Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формат и сделайте выбор в диалоговом окне Найти формат.

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

Заменить

Чтобы заменить текст или числа, нажмите CTRL+H или перейдите на главная > редактирования > Найти & Выберите > Заменить.

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

  1. В поле Найти что: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Найти : и выберите последний элемент поиска из списка.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  2. В поле Заменить на: введите текст или числа, которые вы хотите использовать для замены текста поиска.

  3. Нажмите Заменить все или Заменить.

    Совет: При нажатии кнопки Заменить все вхождения ищемого условия заменяются, а при этом заменяются по одному вхождению за раз.

  4. Нажмите кнопку>> , чтобы при необходимости определить поиск.

    • В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

    • Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения, заметки или примечания.

      Примечание:   Формулы, значения, заметки и примечания доступны только на вкладке Найти. На вкладке "Заменить" доступны только формулы.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение всего содержимого ячеек . Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти : .

  5. Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формат и сделайте выбор в диалоговом окне Найти формат.

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

Существует два разных способа поиска или замены текста или чисел на компьютере Mac. Первый из них — использование диалогового & поиска . Второй вариант — использование панели поиска на ленте.

Диалоговое & "Заменить"

Поиск и параметры

Поиск текста или чисел с помощью диалогового & поиска

Чтобы найти что-то, нажмите CTRL+F или перейдите на главная > Найти & Выберите >Найти.

  1. В поле Найти: введите текст или числа, которые нужно найти.

  2. Нажмите кнопку Найти далее , чтобы выполнить поиск.

  3. При необходимости вы можете определить поиск.

    • В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

    • Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения, заметки или примечания.

      Примечание:   Формулы, значения, заметки и примечания доступны только на вкладке Найти. На вкладке "Заменить" доступны только формулы.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение всего содержимого ячеек . Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти : .

Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

  • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

  • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

  • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

Замена текста или чисел с помощью диалогового & поиска

Чтобы заменить текст или числа, нажмите CTRL+H или перейдите на главная > Найти & Выберите >Заменить.

  1. В поле Найти введите текст или числа, которые нужно найти.

  2. В поле Заменить на введите текст или числа, которые нужно использовать для замены поискового текста.

  3. Нажмите кнопку Заменить или Заменить все.

    Совет: При нажатии кнопки Заменить все вхождения ищемого условия заменяются, а при этом заменяются по одному вхождению за раз.

  4. При необходимости вы можете определить поиск.

    • В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или По столбцам.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение всего содержимого ячеек . Проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти : .

Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

  • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

  • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

  • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

Поиск текста или чисел на панели поиска

  1. Щелкните любую ячейку, чтобы найти весь лист, или выберите определенный диапазон ячеек для поиска.

  2. Нажмите +F или щелкните значок, чтобы развернуть область поиска, а затем в поле поиска введите текст или число, которое вы хотите найти.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  3. Нажмите клавишу RETURN.

  4. Чтобы найти следующий экземпляр ищите элемент, щелкните поле поиска и нажмите кнопку Return или в диалоговом окне Найти нажмите кнопку Найти далее.

    Совет: Вы можете отменить поиск, нажав клавишу ESC .

  5. Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите поиск на листе или Поиск в книге. Вы также можете выбрать параметр "Дополнительные параметры", чтобы запустить диалоговое окно Найти.

Замена текста или чисел из области поиска

  1. Щелкните любую ячейку, чтобы найти весь лист, или выберите определенный диапазон ячеек для поиска.

  2. Нажмите +F, чтобы развернуть область поиска, а затем в поле поиска введите ищемый текст или число.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  3. В поле поиска щелкните значок лупы и выберите команду Заменить.

  4. В поле Заменить на введите замещающие символы.

    Чтобы заменить символы в поле Найти пустыми, оставьте поле Заменить на пустым.

  5. Нажмите кнопку Найти далее.

  6. Чтобы заменить выделенное вхождение, нажмите кнопку Заменить.

    Чтобы заменить все вхождения символов на листе без предварительного просмотра, нажмите кнопку Заменить все.

    Совет: Отмените поиск, просто нажав клавишу ESC.

  7. Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите поиск на листе или Поиск в книге. Вы также можете выбрать параметр "Дополнительные параметры", чтобы запустить диалоговое окно Заменить.

Поиск

Чтобы найти что-то, нажмите CTRL+F или перейдите на главная > редактирования> Найти & Выберите >Найти.

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

  1. В поле Найти: введите текст или числа, которые нужно найти.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  2. Нажмите кнопку Найти далееили Найти все, чтобы выполнить поиск.

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

  3. Нажмите > параметры поиска , чтобы при необходимости определить поиск.

    • В: Чтобы найти данные в пределах определенного выделения, выберите выделение. Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение со всем содержимым ячеек: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти.

Заменить

Чтобы заменить текст или числа, нажмите CTRL+H или перейдите на главная > редактирования > Найти & Выберите > Заменить.

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

  1. В поле Найти: введите текст или числа, которые нужно найти.

    Советы: В условиях поиска можно использовать подделавные знаки: вопросии (?), звездочки (*), тильды (~).

    • Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти "м?т", то будут "ед" и "множество".

    • Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки "г*д" и "начало", они будут отозныны.

    • Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит "фг91?".

  2. В поле Заменить на: введите текст или числа, которые вы хотите использовать для замены текста поиска.

  3. Нажмите кнопку Заменить или Заменить все.

    Совет: При нажатии кнопки Заменить все вхождения ищемого условия заменяются, а при этом заменяются по одному вхождению за раз.

  4. Нажмите > параметры поиска , чтобы при необходимости определить поиск.

    • В: Чтобы найти данные в пределах определенного выделения, выберите выделение. Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

    • Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

    • Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

    • Совпадение со всем содержимым ячеек: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы ввести в поле Найти.

Функция ПОДСТАВИТЬ

В этой статье описаны синтаксис формулы и использование ПОДСТАВИТЬ в Microsoft Excel.

Описание

Подставляет значение аргумента "нов_текст" вместо значения аргумента "стар_текст" в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст начиная с определенной позиции.

Синтаксис

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])

Аргументы функции ПОДСТАВИТЬ описаны ниже.

  • Текст    Обязательный. Текст или ссылка на ячейку, содержащую текст, в котором подставляются знаки.

  • Стар_текст    Обязательный. Заменяемый текст.

  • Нов_текст    Обязательный. Текст, на который заменяется "стар_текст".

  • Номер_вхождения    Необязательный. Определяет, какое вхождение фрагмента "стар_текст" нужно заменить фрагментом "нов_текст". Если этот аргумент определен, то заменяется только заданное вхождение фрагмента "стар_текст". В противном случае все вхождения фрагмента "стар_текст" в тексте заменяются фрагментом "нов_текст".

Пример

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

Данные

Сведения о продажах

<

Квартал 1, 2008 г.

<

Квартал 1, 2011 г.

<

Формула

Описание (результат)

Результат

=ПОДСТАВИТЬ(A2; "продажах"; "ценах")

Замена "ценах" на "продажах" ("Сведения о ценах")

Сведения о ценах

=ПОДСТАВИТЬ(A3; "1"; "2"; 1)

Замена первого экземпляра "1" на "2" ("Квартал 2, 2008 г.")

Квартал 2, 2008 г.

=ПОДСТАВИТЬ(A4; "1"; "2"; 3)

Замена третьего экземпляра "1" на "2" ("Квартал 1, 2012 г.")

Квартал 1, 2012 г.

См. также

Функция ЗАМЕНИТЬ, ЗАМЕНИТЬБ

Функция СЖПРОБЕЛЫ

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)

где

  • Ячейка - ячейка с текстом, где производится замена
  • Старый_текст - текст, который надо найти и заменить
  • Новый_текст - текст, на который заменяем
  • Номер_вхождения - необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

  • Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 - обе "Маши" заменены на "Олю").
  • Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая "Маша" заменена на "Олю").
  • Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. "маша" написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом - спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами - заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM). Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:


Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:


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

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:


У нее простая логика:

  1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
  2. ищем позицию символа # функцией НАЙТИ (FIND)
  3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Ссылки по теме

 

Найти и заменить в Excel

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

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

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

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

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

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

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

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

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.


А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

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

Теперь вы знаете, как в эксель сделать поиск по столбцу, строке, любому диапазону, листу или даже книге.

Поделиться в социальных сетях:

ЗАМЕНИТЬ Формулу в Excel | Как использовать REPLACE Formula в Excel?

ЗАМЕНА Формула в Excel (Содержание)

  • Введение в замену формулы в Excel
  • Как использовать REPLACE Formula в Excel?

Введение в замену формулы в Excel

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

Например: везде, где есть Ян, нужно заменить на февраль.

Определение

Функция, которая помогает нам заменить старый текст новым текстом без стирания существующего текста, называется функцией ЗАМЕНА. Текст может быть одним символом из набора символов. В этой статье мы обсудим замену формулы в Excel.

Заменить можно двумя способами:

1. С помощью Find and Replace.

2. С использованием функции замены.

Если мы нажмем Ctrl + F, появится всплывающее окно ниже.

В «Найти что» нам нужно дать существующий текст и «Заменить на» нам нужно дать новый текст. Мы можем заменить одну ячейку или все выделенные ячейки одним кликом.

Замена с использованием функции REPLACE

Другой способ замены - использование функции «Заменить». Replace - это функция рабочего листа, которая также может использоваться в VBA.

Синтаксис

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

Аргумент в замене формулы

Old_text: этот аргумент представляет существующий старый текст.

Start_num: этот аргумент представляет номер начального символа, с которого мы хотим заменить.

Num_chars: этот аргумент представляет количество символов, которые мы хотим заменить.

New_text: Этот аргумент представляет новый текст, который мы хотим заменить вместо старого существующего текста.

Как использовать Excel REPLACE Formula в Excel?

Excel REPLACE Formula очень прост и удобен. Давайте разберемся, как использовать формулу Excel REPLACE с несколькими примерами.

Вы можете скачать этот шаблон ЗАМЕНЫ формулы Excel здесь - ЗАМЕНИТЬ шаблон формулы Excel

Пример № 1 - заменить текст или строку

Рассмотрим текстовую строку, как показано ниже.

Теперь нам нужно заменить июнь на май с помощью функции REPLACE. Выполните следующие шаги, чтобы заменить.

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

Нам нужно дать стартовый номер, с которого символ мы хотим заменить. Здесь мы хотим заменить на E, который находится в позиции № 9. Итак, поставьте запятую после A2 и введите 9.

Сколько символов мы хотим заменить. Здесь «июнь» занимает 4 символа, которые мы хотим заменить новым текстом. Введите 4 в позиции num_chars.

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

Нажмите клавишу ввода.

Пример № 2 - Заменить дату с помощью формулы ЗАМЕНА

Теперь мы применим функцию Replace, чтобы заменить месяц в формате даты. Рассмотрим дату в следующем формате.

Теперь мы хотим заменить 2019 на 2020, используя REPLACE. Примените формулу, как мы уже применяли ранее.

Но мы получили некоторые странные результаты здесь. Почему? Поскольку дата будет сохранена в числовом формате, следовательно, это добавило 2020 к этому номеру. Каково решение тогда?

Во-первых, нам нужно преобразовать дату в текстовый формат, используя функцию TEXT, формат «дд-ммм-гггг», затем мы заменим 2019 на 2020.

В «Старый текст» используйте функцию ТЕКСТ, чтобы преобразовать дату в требуемый формат даты, как показано ниже.

На этом шаге мы преобразовали дату в ячейке G11 в текстовый формат «dd-mmm-yyy», и теперь мы можем ввести остальные аргументы как обычно.

Мы хотим заменить 2019 на 2020, следовательно, введите 8 в «start_numb», так как 2 находится в 8- й позиции строки.

Введите количество символов 4, потому что «2019» занимает 4 символа.

Наконец, введите новый текст «2020» в двойных кавычках.

Нажмите клавишу ввода.

Пример № 3 - Вставка символов и NESTED REPLACE

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

Рассмотрим строку «зять» без символа «-», как показано ниже.

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

Выберите адрес ячейки.

В начальный номер введите 4, потому что нам нужен дефис в четвертой позиции.

Введите ноль вместо количества символов, так как мы не хотим заменять здесь какие-либо символы.

Введите символ дефиса (-) вместо нового текста с двойными кавычками.

Благодаря этому мы можем ввести первый символ дефиса между «son» и «in», и результат этой функции замены будет введен для внешней функции замены.

Снова введите начальный номер как 7, количество символов как 0 и новый текст как дефис, как показано ниже.

Результат будет как ниже.

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

Что нужно помнить о замене формулы в Excel

  • Он используется для замены старого текста новым текстом, когда мы знаем положение текста.
  • Если вы не знаете положение старого текста, то лучше избегать его использования или сочетания с функцией НАЙТИ. В позиции start_num используйте функцию FIND.
  • Если вы не хотите удалять какие-либо символы, введите ноль в количестве символов.
  • При работе с датами убедитесь, что вы позже конвертируете в текст и заменяете его на нужный Если результат требует в формате даты, а не в текстовом формате, то введите функцию DATEVALUE в результат функции REPLACE.

Рекомендуемые статьи

Это было руководство по замене формулы в Excel. Здесь мы обсудили, как использовать REPLACE Formula в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. КОЛОННЫ Формула в Excel
  2. Выходные Формула в Excel
  3. Группировка столбцов в Excel
  4. Как использовать формулу Excel IRR?

Как быстро изменить формулы в скопированном листе программы Excel

 

Рассмотрим ситуацию копирования листа с формулами в другую книгу (Другой документ) .

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

 

Копирование листа в другой документ.

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

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

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

 


При копировании листа с расчетами в другую книгу (другой документ), формулы ссылаются на старый документ.

Как быстро избавится от ссылок на старый документ в формулах.

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

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

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

Вызвать поиск можно нажав сочетание клавиш «ctrl+F»  (в русской раскладке «ctrl+А» ), либо воспользоваться пиктограммой «Найти и выделить…» на главной вкладке.


В появившейся форме для поиска нужно выбрать вкладку «Заменить». На вкладке «Заменить» есть 2 поля. Первое поле  — то значение, которое  ищем, второе поле – то, на что меняем.

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

 

Функция ПОИСКПОЗ Excel - Avendi

MS Office

Функция ПОИСКПОЗ находит первое вхождение значения, которое соответствует заданному (искомому) тексту или числу в заданном массиве.

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

Функция не различает прописные и строчные буквы.

Если искомое значение не найдено, функция возвращает ошибку #Н/Д!

В английской версии Excel функция называется ПОИСКПОЗ.

Аргументы функции:

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

Вкладка_поиска — Непрерывный диапазон ячеек, в которых необходимо найти значение, указанное в параметре искомое_значение

тип_сравнения — Это необязательный аргумент.Если его опустить, то значением по умолчанию будет 1. Этот аргумент может принимать значения -1, 0, 1. Они означают соответственно:

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

    Пример 1: искомое значение равно 3. Искомый массив с данными: 1,2,3,4. Найдено значение 3. Результатом является значение 3. Это позиция, в которой находится искомая тройка. Пример 2: искомое значение равно 3. Искомый массив с данными: 1,2,4,5. В массиве нет значения, значит, будет найдено значение 2 (это наибольшее значение меньше искомого). Выход устанавливается на 2. Это позиция, в которой находятся найденные два.

  • 0 - функция найдет первое значение, точно равное искомому значению. Значения в искомом массиве не нужно сортировать.

    Если мы выбираем тип сравнения 0 и искомое значение является текстом, мы можем использовать звездочку (*) и вопросительный знак (?) Заполнители

  • -1 - функция находит наименьшее значение, большее или равное искомое значение.Значения в искомом массиве должны быть отсортированы по убыванию
  • Пример 1: искомое значение равно 3. Искомый массив с данными: 4,3,2,1. Найдено значение 3. Результатом является значение 2. Это позиция, в которой находится искомая тройка. Пример 2: искомое значение равно 3. Искомый массив с данными: 5,4,2,1. В массиве нет значения 3. Таким образом, будет найдено значение 4 (это наименьшее значение больше искомого).Результат устанавливается равным 2. Это позиция, в которой находится четыре.

Вы можете вложить ПОИСКПОЗ() в функцию ИНДЕКС(). Это позволяет:

  • получить значение также слева или выше диапазона поиска. Вы не можете сделать это с функциями ВПР() и ГПР(). Функция ВПР() позволяет извлекать значения только справа, а функция ГПР() только ниже искомого диапазона
  • одновременный горизонтальный и вертикальный поиск

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

Решаем задачу следующим образом:

  • используем функцию ПОИСКПОЗ() и находим позицию продукта. Для этого: в качестве значения выберите товар (значение из ячейки А245), искомый массив представляет собой диапазон ячеек, содержащих товары (от А239 до А242), в аргументе «тип_сравнения» введите значение 0.
  • .ITEM() и найти, где находится месяц. Для этого: в качестве искомого значения выберите месяц (значение из ячейки B245), искомый массив представляет собой диапазон ячеек, содержащих месяцы (от B238 до F238), в аргументе «тип_сравнения» введите значение 0.
  • . В этой функции в первом аргументе мы указываем массив для поиска. В качестве второго аргумента мы должны передать номер строки. Мы вычислили это число с помощью первой функции GIVE.ВЕЩЬ (). Поэтому мы вкладываем его в функцию ИНДЕКС(). В качестве третьего аргумента мы должны указать номер столбца. Это число мы нашли с помощью второй функции ПОИСКПОЗ(). Поэтому мы также вкладываем его в функцию ИНДЕКС().

Пример 2 В следующем примере мы хотим найти максимальную сумму продажи и день продажи.

В ячейке B276 мы используем функцию MAX(), чтобы найти максимальную стоимость продаж. Затем используйте функции ИНДЕКС () и ПОИСКПОЗ.ITEM() найдет подходящую дату. Приводим аргументы функции ИНДЕКС():

  • в первом аргументе отмечаем массив, который будет искаться. В примере диапазон ячеек составляет от A265 до A274.
  • во втором аргументе даем номер строки, содержащей искомую дату. Мы вычислили это число с помощью функции ПОИСКПОЗ(). Поэтому мы будем вкладывать эту функцию в функцию ИНДЕКС().
  • в третьем аргументе мы вводим число 1, потому что мы хотим получить данные из первого столбца

В этом примере мы не сможем использовать функцию ПРОСМОТР.VERTICAL(), потому что мы искали столбец B и значение было взято из столбца A (слева от искомого столбца). Функция ВПР() позволяет получить значение справа от искомого столбца. Для получения информации о ВПР () см. ВПР в Excel ()

Пример 3 В примере мы хотим найти стоимость продаж для данного региона и года.

Решаем задачу с помощью функции ИНДЕКС().Мы даем следующие аргументы:

  • в первом аргументе мы выбираем таблицу для поиска, т.е. диапазон ячеек от B289 до E293
  • во втором аргументе мы должны указать номер строки, из которой будут получены данные . Находим это число с помощью ПОИСКПОЗ(). Эта функция вернет значение 3. Это номер строки, в которой находится год 2016.
  • в третьем аргументе мы должны указать номер столбца, из которого будут извлекаться данные. Мы вычислим это число, используя функцию ДАТЬ.ВЕЩЬ (). Эта функция вернет значение 3. Это номер столбца северного региона.
.

Как скопировать значения в Excel [Не формула]

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

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

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

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

  1. Выберите ячейку со значением, которое вы хотите скопировать.
  2. Щелкните правой кнопкой мыши выбранную ячейку и выберите Копировать . (Вы также можете использовать Ctrl + C для этого шага.)

  3. Теперь выберите ячейку электронной таблицы, в которую вы хотите вставить значение.

  4. Перейти к Дом в ленте.

  5. Буфер обмена , Нажмите на Вставьте маленькую стрелку.

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

Примечание. С помощью этого метода можно выделить и скопировать несколько ячеек.

Есть и более простой способ:

  1. Выберите ячейки со значением, которое вы хотите скопировать.

  2. Щелкните правой кнопкой мыши выбранную ячейку или диапазон ячеек и выберите Копировать .

  3. Теперь щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить значения.

  4. Поместите курсор на маленькую стрелку рядом с Специальная вставка… .

  5. В расширенном меню в разделе Вставить значения выберите первый вариант в строке ( Значения ).

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

Как и в предыдущем примере, вам понадобится Специальная паста… .Чтобы скопировать и вставить ячейки с условным форматированием, выполните следующие действия:

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

  2. Щелкните правой кнопкой мыши выбранный диапазон еще раз и выберите Копировать (или используйте Ctrl + C для этого шага).

  3. Выберите диапазон ячеек, куда вы хотите вставить условное форматирование, а затем нажмите Специальная вставка… .

  4. Теперь под Вставьте на Вставьте специальную , отметьте Форматы .

  5. После выбора нажмите OK .

Другим методом копирования и вставки условного форматирования является использование Painter Format :

  1. Выберите диапазон ячеек с условным форматированием.

    90 150

  2. Если это еще не сделано, перейдите к Home на ленте.

  3. Теперь в буфере обмена щелкните Format Painter .

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

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

Кроме того, условное форматирование можно вставлять несколько раз.На шаге 3 дважды щелкните Format Painter . Когда вы закончите вставку условного форматирования, нажмите кнопку еще раз, чтобы отключить функцию вставки.

Дополнительные часто задаваемые вопросы

Как отображать формулы в Excel вместо значений?

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

1. Перейдите к Formulas на ленте.

2.В Проверка формул нажмите Показать формулы .

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

Создает ли Microsoft Excel точную копию формулы?

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

1. Дважды щелкните ячейку с формулой, которую нужно скопировать. Теперь ячейка находится в режиме редактирования.

2.В строке формул над электронной таблицей выберите формулу и нажмите Ctrl + C (Копировать).

90 220

3. Выберите ячейку, к которой вы хотите применить формулу, и нажмите Ctrl + V (вставить).

Примечание: Если дважды щелкнуть ячейку, а курсор не появляется в ячейке, необходимо войти в режим редактирования. Перейдите в Файл> Параметры> Дополнительно и в Параметры редактирования отметьте Разрешить редактирование непосредственно в ячейках .

90 240

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

1. Поместите курсор в правый нижний угол ячейки так, чтобы он выглядел как черный крест.

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

3. Отпустите курсор, когда ячейки будут выделены.

Теперь формула применяется к группе ячеек.

Как заменить значения в Excel?

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

1. Выберите диапазон ячеек, для которых вы хотите изменить значения.

2. Перейдите к Дом на ленте. в Редактирование , Нажмите Найти и выбрать .

4. Теперь нажмите Найти… в раскрывающемся меню, чтобы открыть новое всплывающее окно.

5. W Найти и заменить , выбрать Заменить .

6. Теперь введите значение, которое вы хотите, чтобы Excel нашел в Найдите, что , и в Замените на , введите замещающее значение.

Примечание: Вы можете заменить шаги 1-3 сочетанием клавиш Ctrl + H .

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

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

2. Щелкните Заменить , чтобы заменить значение этой ячейки новым значением.

Если вы хотите заменить все значения в выбранном диапазоне ячеек:

1. Нажмите Найти все . Это выберет все ячейки со значением, которое вы хотите заменить.

2.Нажмите Заменить все , чтобы заменить все старые значения новыми.

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

Почему Excel копирует значение, а не формулу?

Excel по какой-то причине настроен на ручной пересчет. Нужно поставить обратно в автоматический режим:

1. Перейдите к Formulas на ленте.

2. В Расчеты щелкните Параметры расчета .

3. Теперь нажмите Автоматически .

Как скопировать значение и формат в Excel?

Для этого можно использовать функцию Специальная вставка :

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

2. Нажмите Ctrl + C , а затем щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить значения и форматы.

3. Поместите курсор на маленькую стрелку рядом с Специальная вставка. В расширенном меню в разделе Вставить значения выберите третий вариант в строке ( Значения и исходное форматирование ).

Как показать значение в Excel?

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

1. Выберите ячейку со значением, которое вы хотите показать.

2.Перейдите к . Просмотр на ленте.

3. В Показать отметьте Панель формул .

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

1. Выберите необходимый диапазон ячеек.

2. Перейдите к Дом на ленте.

3. В Номер нажмите маленькую кнопку со стрелкой в ​​правом нижнем углу.

4. Выберите Custom в категории .

5. Прокрутите ползунок вниз, вы должны увидеть запись с одной или несколькими точками с запятой (; ). Выберите эту запись и нажмите Удалить .

Теперь должны отображаться любые скрытые значения в выбранном диапазоне ячеек.

Копирование значений в Excel без формулы

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

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

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

Были ли у вас проблемы с копированием значений в Excel? Если да, то как вы подошли к этой проблеме? Дайте нам знать в комментариях ниже.

.

выпускников на чемоданах | Найти и заменить содержимое ячейки

Excel предлагает мощные инструменты поиска листов и замены данных. Мы можем использовать два ключа для замены нежелательных данных.

I. На вкладке Главная в группе Редактировать нажмите Найти и выбрать .

Выполните следующие действия:

A. Для поиска текста или цифр

1. Нажмите Найдите

2.Откроется диалоговое окно. В поле Найти введите текст или цифры, которые вы хотите найти

3. Чтобы указать дополнительные, нажмите кнопку Options

— для поиска данных на листе или во всей книге в поле Внутри выберите Рабочий лист или Рабочая книга .

— для поиска данных в определенных строках или столбцах в поле Search щелкните Rows или Columns .

- Для поиска данных с конкретными деталями в поле Поиск нажмите Формулы , Значения или Комментарии .

— для поиска данных с учетом регистра установите флажок С учетом регистра .

— для поиска ячеек, содержащих только символы, введенные в Найти , установите флажок Сопоставить со всем содержимым ячейки .

4.Нажмите Найти все или Найти далее

- При выборе Найти все искомые данные будут отображаться с адресами

— при выборе Найти следующий будет выделена первая ячейка, содержащая текст или числа, и при повторном нажатии будет показана следующая ячейка, содержащая нужный текст

B. Для поиска и замены текста или цифр

1.Нажмите Заменить .

2. В диалоговом окне введите что и чем хотите заменить. Чтобы заменить текст или числа, введите подстановочные знаки в поле Заменить на (или оставьте его пустым, чтобы заменить символы ничем, т. е. удалить их)

3. Нажмите Найти следующий или Найти Все .

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

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

.

Разделить строку в Excel | Томбукту

Связанные страницы:

Объединение строк в Excel

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

Разделить строку в указанной позиции

есть три встроенные функции Excel, предназначенные для разделения строки в определенной позиции. Существуют левые, центральные и правые функции Excel. Эти функции описаны ниже:

Excel Левая, средняя и правая функции

Функция Excel Left Возвращает указанное количество символов слева (начало) предоставленной текстовой строки.

В следующем примере функция Left возвращает первые два символа «тестовой строки»:

= ВЛЕВО ("тестовая строка", 2) возвращает текстовую строку "te"

Функция Excel Mid Возвращает указанное количество символов из середины предоставленной текстовой строки, начиная с указанного символа.,

В приведенном ниже примере функция Mid возвращает 3 символа из середины «тестовой строки», начиная с символа 6:

= MID ("тестовая строка", 6, 3) возвращает текстовую строку "str".

Функция Excel Right Возвращает указанное количество символов справа (конец) предоставленной текстовой строки.,

В следующем примере функция Right возвращает два последних символа тестовой строки:

= ПРАВО ("Тестовая строка", 2) возвращает текстовую строку "ng"

, разделить текстовую строку на первое вхождение указанного символа

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

Другие функции Excel, которые могут быть полезны для разбиения строки на определенную позицию:

Найти - Возвращает позицию подстроки в предоставленной строке (с учетом регистра).
Поиск - Возвращает позицию подстроки в заданной строке (без учета регистра).
Лен - Возвращает длину предоставленной текстовой строки.,

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

разбить текстовую строку на указанный символ -

примеров

Пример 1. Возврат текста От начала текстовой строки до первого пробела

Если вы хотите использовать формулу для разделения текстовой строки по первому пробелу, а затем вернуть левую часть разделенной строки, вы можете сделать это, объединив функцию «Влево» с функцией «Найти»., Это показано в следующем примере:

А
1 тестовая строка = ЛЕВЫЙ (A1, НАЙТИ ("", A1) - 1) - Возвращает результат "теста"

В приведенной выше формуле find возвращает 5 в качестве позиции пробела в предоставленной «тестовой строке». Вычитание 1 из этого значения дает значение 4, которое затем передается левой функции,

.

Пример 2. Возвращает текст в конце текстовой строки

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

А
1 тестовая строка = ПРАВО (A1, ДЛИН (A1) - НАЙТИ ("", A1)) - Возвращает "строку"

В приведенной выше формуле функция len возвращает 11 в качестве длины тестовой строки, а функция поиска возвращает 5 в качестве пробела.

, поэтому выражение ДЛСТР (A1) - НАЙТИ ("", A1) оценивается как 6 (= 11 - 5), которое затем передается соответствующей функции.,

, поэтому функция Right возвращает последние 6 символов указанной строки.

, разделить строку на N 'с этим экземпляром указанного символа

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

Один из способов найти положение N-го вхождения символа — использовать функцию заполнителя Excel в сочетании с функцией поиска или поиска Excel.

Заполнитель заменяет N 'этого экземпляра указанной строки второй предоставленной строкой. Затем функцию Find можно использовать для возврата позиции замещающей строки, и эта позиция затем может быть доставлена ​​в левую, центральную или правую функцию.

Пример этого показан ниже.,

разделить строку W N 'с этим вхождением указанного символа - пример

в этом примере мы возвращаем левую часть исходной текстовой строки "sample text string" до третьего пробела., iv >

А
1 ОБРАЗЕЦ текстовой строки
2 = замена (A1, "", "|", 3) — возвращает «образец текста | строка»
3 = НАЙТИ ("|", A2) - Возвращает "16"
4 = ЛЕВЫЙ (A1, A3 - 1) — возвращает «образец текста» td

на первом шаге приведенной выше формулы мы заменили третий пробел на «|»., Причина выбора этого символа в том, что мы знаем, что он не появляется в исходном тексте.

Три шага, показанные в ячейках A2–A4 приведенной выше электронной таблицы, возвращают левую часть исходной текстовой строки до третьего пробела. Если вы уверены в формуле Excel, вы можете объединить эти три шага в одна формула, как показано на скриншоте ниже:

одиночная формула:

1 ОБРАЗЕЦ текстовой строки тд
2 = Левый (A1, найти ("/", подставить (A1, "", "|", 3)) - 1) - Возвращает "образец текста"
.

Пользовательское форматирование в Excel — часть первая

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

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

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

Создание пользовательских форматов чисел и валюты

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

Чтобы определить новый пользовательский числовой формат, откройте диалоговое окно Формат ячеек , используя сочетание клавиш Ctrl + 1 или нажав кнопку Числовой формат в группе Число на вкладке Главная .

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

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

Этот простой в построении код определяет, как будет отображаться содержимое ячейки — тысячи, отделенные от сотен (# ##), значение, округленное до двух знаков после запятой (0,00), и, наконец, символ валюты (PLN).

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

Код Описание
0 (ноль) Заполнитель цифры, указывающий количество символов, которые должны отображаться с каждой стороны десятичной точки (,).Например, если вы используете формат 0,000, значение 1,75 будет отображаться как 1,750. Формат 000.00 будет отображать 1.75 как 001.75. Если значение в ячейке содержит больше символов, определяющих десятичную часть, чем количество нулей, размещенных справа от десятичной точки в формате, то отображаемое значение округляется. Например, если вы используете формат 0,00, значение 1,567 будет отображаться как 1,57, а в формате 0,0 значение будет равно 1,6.
? Заполнитель цифры работает так же, как 0 (ноль), за исключением того, что заполнитель (?) добавляет пробел для нулей, которые не имеют значения по обе стороны от десятичной точки (,).Например, при использовании формата 0.0? значения 1,56 и 1,5 будут отображаться одинаково, выравнивая их по десятичной запятой.
# Заполнитель для цифры, которая работает как 0 (ноль). Не отображает дополнительные нули в случае, когда введенное число содержит меньше цифр по обе стороны от десятичного разделителя (,), чем символов в формате. Этот код сообщает Excel, где отображать символ десятичной точки (,) или другие разделители.Например, при использовании формата #, ### значение 15,67 будет отображаться как 15,67, а значение 39,625 — как 39,625.
, Десятичный разделитель, указывающий, сколько цифровых символов (0 или #) появляется справа или слева от него. Если числовой формат содержит только символ (#) слева от десятичной точки, то все значения ниже 1 будут отображаться с разделителем, но без ведущего нуля, например, значение 0,75, отформатированное с кодом #, ## будет представлен как 75.Чтобы избежать этого отображения, используйте ноль (0) в качестве первого символа слева от десятичной точки. Затем в ячейке появится значение 0,75.
% Символ процента, который умножает значение на 100 и вводит знак %.
/ Символ дробного формата отображает десятичную часть как дробную часть числа. Количество заполнителей цифр, присутствующих в формате, определяет точность отображаемого значения. Например, значение 0,169, отформатированное кодом #?/? Excel будет отображаться как 1/6 при использовании кода # ??/?? представит как 12/71.
Е-, Е+, е-, е + Символ, указывающий научный формат, отображающий количество знаков после запятой (,) справа от показателя степени E или e. Количество нулей (0) или цифр (#) заполнителей справа от E или e указывает минимальное количество символов в показателе степени. Если вы используете формат E + или e +, знак минус (-) появится с отрицательными показателями степени, а знак плюс (+) появится с положительными показателями. Используя формат E или e, вы указываете Excel вставлять отрицательный знак (-) в отрицательные степени.
$-+/() пробел Стандартные символы форматирования, использование которых добавит указанный символ к созданному формату.
\ Символ обратной косой черты предшествует каждому символу, который вы хотите отобразить в ячейке (кроме символов $ - + / () и пробела).
_ Символ подчеркивания, который при использовании в коде вставляет пробел, равный ширине следующего за ним символа.Например, если формат содержит код _X, он вставит пробел, равный ширине заглавной буквы X. Используйте этот символ для выравнивания значений в ячейках.
"Текст" Строка, заключенная в двойные кавычки. В этом случае в ячейку можно ввести всю текстовую строку без повторения обратной косой черты после каждого символа.
* Символ, позволяющий повторять следующий за ним символ до тех пор, пока он не заполнит всю ширину ячейки.Обратите внимание, что в формате можно использовать только один символ повторения (*). Например, при использовании формата 0,00 * Excel будет отображать значение, округленное до двух знаков после запятой, за которым следует знак минус (-) до конца ширины ячейки.
@ Заполнитель, который вставляет текст в ячейке (если есть) в формат вместо символа (@). Например, если код формата выглядит как «Шаблон, созданный» @, в ячейке будет отображаться текст «Шаблон, созданный Excelforwarding.pl" в случае, когда в ячейку была помещена текстовая строка "Exceldlaksiegowych.pl".
Как вы заметили, таких читов несколько, и каждый из них выполняет определенную задачу. Я настоятельно рекомендую вам просмотреть этот список и записать собранную информацию, которая позволит вам понять, как создаются форматы и как они работают.

Структура числовых форматов

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

ПОЛОЖИТЕЛЬНЫЕ ЗНАЧЕНИЯ, ОТРИЦАТЕЛЬНЫЕ ЗНАЧЕНИЯ, НУЛЕВЫЕ ЗНАЧЕНИЯ, ТЕКСТ

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

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

Основные восемь цветов, которые мы используем при определении форматов:

  • [Черный]
  • [Зеленый]
  • [Белый]
  • [Синий]
  • [Пурпурный]
  • [Желтый]
  • [Голубой]
  • [Красный]

Хорошо.Вы уже знаете коды, знаете из каких разделов состоит формат, также можете добавлять цвета. Но знаете ли вы, что вы также можете применять условия в пользовательском числовом формате? Кстати, их также следует определять в квадратных скобках.

Настало время нам вместе шаг за шагом, раздел за разделом, проанализировать пример записи пользовательского формата:

[Зеленый] _- * # ## 0,00 зл. _-; [Красный] - * # ## 0,00 зл. _-; [Синий] _- * "-" ?? PLN _-;_- @ _-

Показанный формат включает все четыре раздела кода, разделенные точкой с запятой.

Код [Зеленый] _- * # ## $ 0,00_-
Определяет первый раздел формата для положительных значений. Он начинается с указания цвета шрифта (в данном случае зеленого), затем появляется символ подчеркивания, чтобы вставить пробел для ширины символа позади него, то есть знак минус (-). Затем за ним следует символ повторения (*) и пробел, который вставляется по всей ширине ячейки. Следующим шагом является определение формата чисел, отделяющих тысячи от сотен и определяющих два десятичных разряда.Затем пробел, указав валюту (PLN) и повторно вставив пробел для ширины знака минус (-).

Код [Красный] - * # ## $ 0.00_-
Определяет второй раздел формата для отображения отрицательных значений красным цветом. Значения будут начинаться со знака минус (-), за которым следует пробел (пробел) несколько раз по ширине ячейки. Числовые значения форматируются до двух знаков после запятой и отделяются от сотен до тысяч. За ними следует символ валюты (PLN), а в конце вставляется пробел шириной со знаком минус (-).

Код [Синий] _- * "-" ?? PLN_-
Определяет третий раздел формата нулевых значений, которым присвоен синий цвет. Опять же, сначала определяется ширина знака минус (-), затем по всей ширине вставляется пробел. Следующим элементом является знак минус (-), за которым следуют два заполнителя для незначащих нулей, за которыми следует пробел и символ валюты (PLN). Все это завершается вставкой пробела шириной знака минус (-).

Код _- @ _-
Задает четвертый и последний раздел формата, на этот раз для текста. Он начинается с пробела шириной со знак минус (-), за которым следует текст внутри ячейки, и заканчивается пробелом той же ширины.

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

Стоит помнить!
При изменении существующего (встроенного в Excel) кода формата старый остается нетронутым, а создается новый. Кроме того, Excel не позволяет удалить встроенные форматы. Вы можете удалять только созданные пользователем.

Ниже вы найдете краткий контрольный список команд, которые вы должны выполнить, если хотите создать или удалить пользовательские форматы.

Чтобы создать новый номер или формат учета:
ШАГ 1: Выберите ячейку, содержащую числовое значение.
ШАГ 2: На вкладке Главная в группе Номер щелкните Бухгалтерия , а затем используйте сочетание клавиш Ctrl + 1 .
ШАГ 3: В диалоговом окне Формат ячеек , на вкладке Числа выберите категорию Пользовательский .
ШАГ 4: В поле Введите , измените код нужным образом и нажмите OK .

Чтобы удалить нестандартный формат:
ШАГ 1: Используйте сочетание клавиш Ctrl + 1 .
ШАГ 2: В диалоговом окне Format Cells на вкладке Numbers выберите категорию Custom .
ШАГ 3: Проверьте нестандартный размер, который вы хотите удалить.
ШАГ 4: Нажмите Удалить под списком Тип, а затем нажмите OK .

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

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

Если вам понравилась эта статья и вы сочли ее полезной, поделитесь информацией с друзьями в Facebook, Twitter, Google+, LinkedIn или в комментариях ниже.
Заранее большое спасибо!

.

4 способа подсчета количества слов или текстов в Excel для начинающих

Вы когда-нибудь слышали о функции LEN? Что ж, функция ДЛСТР обычно используется для подсчета большого количества текстовых символов и количества цифровых символов.

Вопрос "Как подсчитать количество слов в ячейке или диапазоне Excel?" В Excel мы по-прежнему можем подсчитывать количество слов, хотя сам Excel не предоставляет специальных функций для подсчета слов ( количество слов ).

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

1. Как подсчитать общее количество слов в ячейке

Формула, используемая для подсчета количества слов в предложении в ячейке в Microsoft Excel, а именно:

  = LEN (Калимат) -LEN (ЗАМЕНА (Калимат, "", "")) + 1  

Шаги для применения приведенной выше формулы следующие:

1. Предположим, что данные находятся в ячейке B2: B5.

2. Щелкните ячейку C2 и введите формулу:

  = ДЛСТР (B2) -ДЛСТР (ЗАМЕНА (B2, "", "")) + 1  

Примеры применения следующей формулы:

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

Описание:

LEN (B2) используется для подсчета общего количества символов в A2. Этот LEN (SUBSTITUTE (B2; ""; "")) используется для подсчета символов в тексте без подсчета пробелов.

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

  = ДЛСТР (ОТДЕЛКА (A2)) - ДЛСТР (ЗАМЕНА (A2; ""; "")) + 1  

2. Как подсчитать общее количество слов в диапазоне

Как подсчитать общее количество слов в диапазоне?
Мы можем рассчитать общее количество слов в диапазоне с помощью таблицы формул.Формулы массива — это функции или формулы Excel, которые включают массивы в свои расчеты данных.

Где находится формула массива, а именно:

  = СУММ (ДЛСТР (ДиапазонКалимат) -ДЛСТР (ЗАМЕНА (ДиапазонКалимат, "", "")) + 1)  

Помимо использования формулы массива , мы также можем использовать формулу Excel, т.е. функцию Суммарное произведение . Формулы функции следующие:

  = СУММПРОИЗВ (ДЛСТР (RangeKalimat) -ДЛСТР (ЗАМЕНА (ДиапазонKalimat, "", "")) + 1)  

Шаги для применения двух приведенных выше формул следующие:

1.Предположим, данные находятся в ячейке B2: B5 .

2. Щелкните ячейку C9 и введите формулу:

  = СУММ (ДЛСТР (B2, B5) -ДЛСТР (ПОДСТАВИТЬ (B2: B5, "", "")) + 1)  

Затем нажмите CTRL + Shift + Enter , чтобы закончить.

3. Щелкните ячейку C10 и введите формулу:

  = СУММПРОИЗВ (ДЛСТР (B2: B5) -ДЛСТР (ЗАМЕНА (B2: B5, "", "")) + 1)  

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


Примечание:

Знак {...} не пишется вручную, а появляется автоматически как знак того, что формула Формула массива вводится нажатием CSE (Ctrl + Shift + Enter) одновременно

3. Как подсчитать количество определенных слов в ячейке

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

  = СУММ (ДЛСТР (АламатСел) -ДЛСТР (ЗАМЕНА (АламатСел, "ТексДихитунг", ""))) / ДЛСТР ("ТексДихитунг")  

Шаги для применения приведенной выше формулы следующие:

1.Предположим, данные находятся в ячейке A2 .

2. Щелкните ячейку B5 и введите формулу:

  = СУММ (ДЛСТР (A2) -ДЛСТР (ПОДСТАВИТЬ (A2, "excel", ""))) / ДЛСТР (A5)  

Примеры его использования, как показано ниже:

Примечание:

Заполнитель — , с учетом регистра. означает, что различаются прописные и строчные буквы, где "IT" и "To" считаются разными.

Таким образом, если вы хотите подсчитать количество слов без учета регистра, текст в подсчитываемой ячейке будет преобразован в верхний или нижний регистр с помощью функций ПРОПИСНАЯ или НИЖНЯЯ .Теперь, чтобы было понятнее, мы можем увидеть пример использования функций ВЕРХНИЙ и НИЖНИЙ ниже этого:

На приведенном выше фото использованы 2 формулы Excel, а именно:

  = СУММА (ДЛСТР (A2) -ДЛСТР (ЗАМЕНИТЬ (ПРОЧН. (A2), ПРОП. (A5), ""))) / ДЛСТР (A6)  

или

  = СУММА (ДЛСТР (A2) -ДЛСТР (ПОДСТАВИТЬ (НИЖНИЙ (A2), МНИЖНИЙ (A5), ""))) / ДЛСТР (A5)  

4. Как подсчитать количество конкретных слов в диапазоне

Формулы массива также можно применять к диапазону, который должен заканчиваться на Ctrl + Shift + Enter .

Шаги для применения этого случая:

1. Предположим, что данные находятся в ячейке B2: B5 .

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

  = СУММ (ДЛСТР (B2: B5) -ДЛСТР (ЗАМЕНА (B2: B5, "excel", "")))) / ДЛСТР ("excel")  

Или используйте функцию Суммарное произведение , например:

  = СУММПРОИЗВ (ДЛСТР (B2: B5) -ДЛСТР (ЗАМЕНА (B2: B5, "excel", ""))) / ДЛСТР ("excel")  

3.Результаты выполнения формулы будут выглядеть следующим образом:

Кроме того, если вы хотите игнорировать прописные и строчные буквы, добавьте функции НИЖНИЙ или ВЕРХНИЙ в две приведенные выше формулы. Посмотрите на картинку ниже:

На изображении выше видно, что красная стрелка указывает на использование формулы массива, а именно:

  {= СУММ (ДЛСТР (B2: B5) -ДЛСТР (ПОДСТАВИТЬ (МНИЖНИЙ (B2: B5), МНИЖНИЙ ("Excel"), "")))) / ДЛСТР ("Excel")}  

Синяя стрелка указывает на использование этой функции Суммарное произведение , то есть:

  = СУММПРОИЗВ (ДЛСТР (B2: B5) -ДЛСТР (ЗАМЕНА (МНИЖНИЙ (B2: B5), МНИЖНИЙ ("Excel"), ""))) / ДЛСТР ("Excel")  

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

Ресурс статьи

  • Подсчет символов в ячейках — Служба поддержки Office | https://bit.ly/2Y8QZ9Z
  • Формулы Excel подсчитывают слова или тексты - класс Excel | https://bit.ly/2Zc5UBK
.

VLOOKUP в Excel — статьи TechTV

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

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

Вы можете найти и импортировать в Интернете список, содержащий название города для каждого кода аэропорта.

Но как получить эту информацию о каждой записи в отчете?

  1. Используйте функцию ПОИСК.ПЕРПЕНДИКУЛЯРНО. ВПР расшифровывается как «вертикальный поиск». Вы можете использовать его в любое время, когда у вас есть список данных с ключевым полем в левом столбце.
  2. Начните вводить функцию = ВПР (. Нажмите Ctrl + A для справки по функции.
  3. ВПР требует четырех параметров. Первый — это код города в исходном отчете. В этом примере это будет ячейка D4
  4. .
  5. Следующий параметр - это диапазон со справочной таблицей. Выделите диапазон. Не забудьте использовать F4, чтобы сделать диапазон абсолютным.(Абсолютная ссылка имеет знак доллара перед номером столбца и номером строки. После копирования формулы ссылка по-прежнему будет указывать на I3: J351.
  6. Третий параметр сообщает Excel, в каком столбце находится название города. В диапазоне I3:J351 название города находится в столбце 2. Введите 2 для этого параметра.
  7. Четвертый параметр сообщает Excel, верно ли "близкое" совпадение. Это не так, поэтому введите False.
  8. Нажмите OK, чтобы завершить формулу.Перетащите маркер заполнения, чтобы скопировать формулу вниз.
  9. Поскольку вы тщательно ввели абсолютные формулы, вы можете скопировать столбец E в столбец D, чтобы получить город назначения. В этом случае все вылеты осуществляются из международного аэропорта Торонто Пирсон.

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

  1. Один список содержит тире, а другой нет. Используйте функцию = ПОДСТАВИТЬ() для удаления тире. При первом использовании функции ВПР вы получите сообщение об ошибке «Неприменимо».

    Чтобы удалить тире с помощью формулы, используйте формулу ОСНОВА. Используйте 3 аргумента. Первый аргумент — это ячейка, содержащая значение. Следующий аргумент — это текст, который вы хотите изменить. Последний аргумент — текст замены.В этом случае вы хотите заменить тире на ничто, поэтому формула = ПОДСТАВИТЬ (A4, "-", "") .

    Вы можете обернуть эту функцию в ВПР для описания.

  2. Это незаметно, но очень часто. В одном списке в конце записи есть пробел. Используйте = TRIM() для удаления лишних пробелов. После первоначального ввода формулы все ответы являются ошибками «Неприменимо». Вы точно знаете, что значения есть в списке и с формулой все выглядит нормально.

    Стандартная проверка — перейти к ячейке с искомым значением. Нажмите F2, чтобы перевести ячейку в режим редактирования. В режиме редактирования вы можете видеть, что курсор находится на расстоянии одного пробела от последней буквы. Это означает, что в конце записи есть пробел.

    Используйте функцию TRIM для решения проблемы. = TRIM (D4) удалит начальные и конечные пробелы и заменит все внутренние двойные пробелы одним пробелом. В этом случае TRIM отлично справляется с удалением завершающего пробела. = ВПР (ОТРЕЗАТЬ (D4), $ I $ 3: $ J $ 351,2, ЛОЖЬ) — это формула.

  3. Я упомянул в примечаниях к программе лишний совет: как заменить результат #Н/Д для пропущенных значений пробелом. Если значение поиска отсутствует в таблице поиска, функция ВПР возвращает ошибку Н/Д.

    В этой формуле используется функция = ISNA () , чтобы определить, является ли результат формулы ошибкой N / A. Если возникает ошибка, второй аргумент функции ЕСЛИ сообщит Excel о вставке любого текста.

    = ЕСЛИ (ИСНА (ВПР (D4, $I $3: $J$351,2, ЛОЖЬ)), "Неверный код", ВПР (D4, $I$3: $J$351,2, ЛОЖЬ))

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

.

Смотрите также

Только новые статьи

Введите свой e-mail

Видео-курс

Blender для новичков

Ваше имя:Ваш E-Mail: