Содержание

Ввод данных в Excel

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

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

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

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

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

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

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

При вводе дробных значений некоторые записи Эксель может интерпретировать как дату и производить автозамену. Чтобы этого не происходило, необходимо перед дробным значением ввести ноль или же сразу выбрать необходимый формат ячейки перед внесением дробного значения. Так при вводе «1/10» будет произведена автоматическая замена на «01.окт», если формат ячейки был общим.

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

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

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

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

Если необходимо ввести текст в ячейке в несколько строк, то можно в конце каждой строки устанавливать разрыв строки комбинацией клавиш «Alt + Enter».

[expert_bq id=»1570″]Например, формула СУММЕСЛИ B2 B5; Иван ; C2 C5 суммирует только те значения из диапазона C2 C5, для которых соответствующие значения из диапазона B2 B5 равны Иван. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Многие пользователи Excel достаточно часто сталкиваются с необходимостью извлечь из строки отдельные символы. Например, из списка служащих организации (имена и фамилии) требуется извлечь только фамилии всех сотрудников, чтобы в дальнейшем использовать часть текстовых данных каждой ячейки. Excel предоставляет несколько превосходных функций, позволяющих решить эту задачу.

Excel 2. Нумерация ячеек – Эффективная работа в MS Office

Формула вернет символ А. Этот пример лишь иллюстрирует действие функций, вряд ли он будет полезен на практике. Сначала введенный символ преобразуется в соответствующее значение кода (192), после чего функция СИМВОЛ возвращает символ А, который соответствует данному значению.

Excel если ячейка содержит значение то

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

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

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

Функция ЕТЕКСТ принимает один аргумент и возвращает значение ИСТИНА, если ячейка содержит текст, или значение ЛОЖЬ в противном случае. К примеру, следующая формула вернет значение ИСТИНА, если ячейка А1 содержит текст:

Функция ТИП также имеет один аргумент и возвращает значение, которое указывает тип данных, содержащихся в ячейке. Например, если ячейка А1 содержит текстовую информацию, формула, приведенная ниже, вернет значение 2 (кодовый номер текстового формата):

Функция ЕТЕКСТ считает текстом также числовое значение, перед которым расположен апостроф. Однако она не считает текстом число, отформатированное как текст (если текстовый формат применен после ввода числа в ячейку).

Каждому символу, который виден на экране компьютера, соответствует определенное кодовое число. Для работы в системе Windows приложение Excel использует стандартный набор символов ANSI, который содержит 255 символов, пронумерованных числами в диапазоне от 1 до 255.

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

Для работы с кодами символов Excel предоставляет две специальные функции: КОДСИМВ и СИМВОЛ. Несмотря на то, что эти функции не столь популярны, как остальные, они пригодятся при совместном использовании с другими функциями.

Функции КОДСИМВ и СИМВОЛ работают только со строками в кодировке ANSI. Эти функции не будут выполняться для строк в двухбайтовой кодировке Unicode.

Функция КОДСИМВ, которая используется в приложении Excel, возвращает код символа, введенного в качестве аргумента функции. Например, формула, приведенная ниже, возвращает значение 192 – код русского символа А, введенного в верхнем регистре.

В том случае, если аргумент функции КОДСИМВ содержит несколько символов, функция использует только первый символ. Например, следующая формула возвращает значение 196 – код символа Д:

По своей сути функция СИМВОЛ полностью противоположна функции КОДСИМВ. Ее аргументом является числовое значение в интервале от 1 до 255, а сама функция возвращает символ, соответствующий этому значению. Например, приведенная ниже формула возвращает русский символ А:

Чтобы продемонстрировать разницу между функциями КОДСИМВ и СИМВОЛ, введите в ячейку следующую формулу:

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

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

Чаще всего не стоит беспокоиться по поводу регистра символов текста. Если же необходимо при сравнении учитывать регистр символов, можно использовать функцию СОВПАД. Приведенная ниже формула возвращает значение ИСТИНА только в том случае, если ячейки А1 и А2 содержат абсолютно идентичные записи.

Следующая формула возвращает значение ЛОЖЬ, поскольку первая строка содержит в конце пробел:

Excel использует знак & как оператор конкатенации (объединения строк). Конкатенация – это просто модный термин, указывающий, что результат объединяет содержимое нескольких ячеек. Например, если ячейка А1 содержит текст Tucson, а ячейка А2 – California, приведенная ниже формула возвратит текст TucsonCalifornia:

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

Другой способ, который, может быть, даже лучше предыдущего, – использовать запятую и пробел, чтобы получить текст Tucson, California.

Еще один способ объединения строк состоит в применении функций СИМВОЛ с соответствующими аргументами. Обратите внимание, что в приведенном ниже примере использования функции СИМВОЛ в объединяемый текст вставляются запятая (44) и пробел (32).

Ниже приведен еще один пример использования функции СИМВОЛ. Следующая формула возвращает строку Stop, объединяя четыре символа, полученные с помощью функции СИМВОЛ:

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

Имейте в виду, что в Excel есть также функция СЦЕПИТЬ, которая поддерживает до 255 аргументов. Эта функция объединяет свои аргументы в единую строку. Многие пользователи предпочитают применять именно ее, однако использование оператора конкатенации (&) значительно проще.

По существу, эта формула объединяет текстовую строку с содержимым ячейки ВЗ и отображает полученный результат. Обратите внимание, что в А5 к содержимому ВЗ не был применен ни один специальный формат. Тем не менее при желании можно установить для нее денежный формат с использованием пробелов и символа валюты.

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

Применить формат к содержимому ячейки ВЗ в ячейке А5 можно с помощью функции ТЕКСТ. Для этого нужно ввести в А5 такую формулу:

Эта формула будет отображать и текст, и само отформатированное числовое значение следующим образом:

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

В предыдущем примере мы использовали простую ссылку на ячейку ВЗ. Но это не единственная возможность. Вместо ссылки на ячейку можно использовать любое выражение. Ниже приведен пример, в котором текст объединяется с числом, полученным путем вызова функции СРЗНАЧ.

Теперь мы рассмотрим другой пример, в котором используется функция СЕГОДНЯ, возвращающая текущую дату и время. Использование этой функции совместно с функцией ТЕКСТ позволяет отобразить на экране текущую дату и время, представленные в удобном для восприятия формате.

Отображение денежных значений, отформатированных как текст

В отдельных случаях функция РУБЛЬ может использоваться вместо функции ТЕКСТ. Однако намного эффективнее применение функции ТЕКСТ, которая является более гибкой, поскольку не ограничивает вас определенным числовым форматом.

Приведенная ниже формула возвращает такой текст: Итого: 1 287,37р. Второй аргумент функции РУБЛЬ определяет количество десятичных знаков после запятой.

Удаление пробелов и непечатных символов

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

• Функция СЖПРОБЕЛЫ удаляет из строки ведущие и замыкающие пробелы. Кроме того, внутренние последовательности пробелов она заменяет одним пробелом. • Функция ПЕЧСИМВ удаляет из строки все непечатаемые символы, которые в импортированном формате были служебными символами.

Рассмотрим пример использования функции СЖПРОБЕЛЫ. Приведенная ниже формула возвращает строку “Чистый доход за квартал” без лишних пробелов.

Функция ДЛСТР принимает только один аргумент и возвращает количество символов, содержащихся в ячейке. Возьмем, например, ячейку А1, содержащую строку Продажи в сентябре. Формула, приведенная ниже, вернет значение 18.

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

Следующая формула укорачивает текст, который оказался слишком длинным. Если в ячейке А1 содержится более десяти символов, формула возвращает первые 9 символов, за которыми следует символ троеточия (в кодовой таблице ANSI этот символ имеет код 133). Если символов десять или меньше, возвращается вся строка.

Функция ПОВТОР предназначена для того, чтобы повторить любую строку текста или символ (первый аргумент) заданное количество раз (второй аргумент). Например, следующая формула возвращает текст НаНаНа:

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

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

Добавление к числу заданных символов

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

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

Представленная далее формула отображает звездочки, дополняющие число сразу с двух сторон. Она возвращает 24 символа в том случае, если число в ячейке А1 содержит четное количество символов, и 23 символа, если число содержит нечетное количество символов.

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

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

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

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

1. Функция ПРОПИСН преобразует все символы текста в верхний регистр. 2. Функция СТРОЧН преобразует весь текст в нижний регистр. 3. ПРОПНАЧ преобразует первый символ каждого слова в верхний регистр, а все остальные символы – в нижний.

Эти функции действуют очень просто. Например, следующая формула преобразует все символы текста в ячейке А1 в верхний регистр. Если ячейка А1 содержит текст MR. JOHN Q. PUBLIC, она вернет строку Mr. John Q. Public.

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

Функция ПРОПНАЧ не всегда приводит к нужному результату, поскольку она может неправильно трактовать некоторые слова. К примеру, фамилию McCartney она изменит на Mccartney.

Преобразование данных с помощью формул

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

1. Создайте формулы, преобразующие исходные данные. 2. Выделите ячейку с формулой. 3. Выберите команду Главная→Буфер обмена→Копировать. 4. Выделите исходную ячейку с формулой. 5. Выберите команду Главная→Буфер обмена→Вставить→Вставить значения.

После выполнения приведенных выше действий формула удаляется, а на ее место вставляются преобразованные данные.

Извлечение заданных символов из строки

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

• Функция ЛЕВСИМВ возвращает заданное количество символов с начала строки. • Функция ПРАВСИМВ возвращает заданное количество символов с конца строки. • Функция ПСТР возвращает заданное количество символов, начиная с любой позиции в пределах строки.

Формула, приведенная ниже, возвращает последние 10 символов содержимого ячейки А1. Если ячейка А1 содержит менее десяти символов, формула вернет текст ячейки в полном объеме.

В следующей формуле используется функция ПСТР. Она возвращает из ячейки А1 пять символов, начиная со второго. Другими словами, формула возвращает символы со второго по шестой.

Таким образом, если бы ячейка А1 содержала текст ПЯТЫЙ КВАРТАЛ, данная формула вернула бы текст Пятый квартал.

Ниже приведена формула, в которой используется функция ПОДСТАВИТЬ для замены значения года 2001 на 2002 в строке Бюджет 2001. Эта формула возвращает значение Бюджет 2002.

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

Приведенная далее формула использует функцию ЗАМЕНИТЬ для замены всего одного символа, расположенного в пятой позиции, ничего не вставляя вместо него. Иными словами, она просто удаляет шестой символ (дефис) и возвращает текст Часть544.

Например, если ячейка А1 содержит строку Part-2A — Z(4М1)_А*, данная формула вернет Part2AZ4M1A.

Найти местонахождение определенного текста или символа в пределах одной строки в приложении Excel можно с помощью функций НАЙТИ и ПОИСК.

В формуле, приведенной ниже, применена функция НАЙТИ, которая возвращает значение 7, т.е. номер позиции первого символа m, встречающегося в строке. Следует отметить, что эта формула учитывает регистр символов:

Следующая формула использует функцию ПОИСК и возвращает значение 5 – номер позиции первого встреченного символа m. В этом случае регистр не учитывается.

В первом аргументе функции ПОИСК можно использовать следующие символы макроподстановки:

• вопросительный знак (?) соответствует любому одиночному символу; • звездочка (*) соответствует любой последовательности символов.

Чтобы найти символы вопросительного знака или звездочки, введите в формуле перед этими символами тильду (~).

Следующая формула исследует текст в ячейке А1 и возвращает позицию первой последовательности из трех символов, содержащей в середине дефис. Иначе говоря, формула ищет любой символ, за которым следует знак дефиса и любой другой символ. Таким образом, если ячейка А1 содержит текст Part-А90, формула возвращает значение 4.

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

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

Следующая формула использует функцию ПОДСТАВИТЬ для достижения того же эффекта, но более удобным способом:

[expert_bq id=»1570″]В результате появится окошко настроек, где необходимо выбрать тип прогрессии, шаг, максимальное значение и направление заполнения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если необходимо скопировать одно и то же значение в большое количество ячеек, можно скопировать это значение в буфер обмена, выделить диапазон заполняемых ячеек или отдельные ячейки с зажатой клавишей «CTRL», а затем вставить значение из буфера обмена.