Возможность проверки в Excel - это замечательная вещь, но она не умеет выполнять одно важное действие (без следующего трюка): автоматически добавлять новую запись в список, который используется в качестве источника для списка проверки.
Если вы уже работали с проверкой, то знаете, что это весьма искусная возможность. Вероятно, наиболее впечатляющим является ее умение добавлять в любую ячейку список, в котором пользователь может выбирать значения. Было бы здорово, если бы при вводе нового имени в проверенную ячейку Excel автоматически добавлял ее в список. Это возможно благодаря следующему трюку.
Пусть есть список имен в диапазоне А1:А10 (рис. 2.24).
Эти имена представляют сотрудников компании. Не так уже редко в подобный список добавляются имена новых сотрудников, но сейчас единственный способ сделать это - добавить новые имена в конце списка, а затем выбирать их в списке в проверенной ячейке. Чтобы обойти это ограничение, сделайте следующее. В ячейке А11 введите следующую формулу и скопируйте ее вниз до строки 20 (рис. 2.25) - обратите внимание на относительную ссылку на ячейку А10: =IF(OR($D$1="";COUNTIF($A$1:A10;$D$l));"x";$D$l) , в русской версии Excel: =ЕСЛИ(ИЛИ($D$1="";СЧЁТЕСЛИ($А$1:А10;$D$1));"х";$D$1) .
Теперь выберите команду Формулы > Присвоить Имя (Formulas > Define Name) и в поле Имя (Names in workbook) введите MyNames. В поле Формула (Refers to) введите следующую формулу (рис. 2.26), щелкните кнопку Добавить (Add), затем щелкните кнопку ОК: =OFFSET(Sheet1!$А$1;0;0;COUNTIF(Sheet1!$А:$А;"х");1) , в русской версии Excel: =CMEЩ(Sheet1!$А$1;0;0;СЧЕТЕСЛИ(Sheet1!$А:$А;"х");1) .
Выделите ячейку D1 и выберите команду Данные > Проверка (Data > Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyNames; удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell). Перейдите на вкладку Сообщение об ошибке (Error Alert) и сбросьте флажок Выводить сообщение об ошибке (Show error alert after invalid data is entered). Щелкните кнопку ОК. Вы увидите результат, как на рис. 2.27.
Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code). Введите код, приведенный в листинге 2.5.
| 1 2 3 4 5 6 7 8 | // Листинг 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application. EnableEvents = False Range ("MyNames" ) = Range ("MyNames" ) . Value Application. EnableEvents = True On Error GoTo 0 End Sub |
// Листинг 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False Range("MyNames") = Range("MyNames").Value Application.EnableEvents = True On Error GoTo 0 End Sub
Чтобы вернуться обратно в Excel и сохранить рабочую книгу, закройте окно. Теперь выделите ячейку D1, введите любое имя, не входящее в список, и нажмите клавишу Enter. Снова выделите ячейку D1 и взгляните на список. Теперь в списке должно присутствовать новое имя (рис. 2.28).
Если вы хотите добавить больше 10 новых имен в список, скопируйте формулу ниже строки 20.
Наверное, многие неопытные пользователи пытались скопировать в Экселе какие-нибудь данные, но в результате действий у них на выходе получалось или совсем другое значение, или ошибка. Это связано с тем, что в первичном диапазоне копирования находилась формула, и именно она была вставлена, а не значение. Подобных проблем удалось бы избежать, если бы эти пользователи были знакомы с таким понятием, как «Специальная вставка» . С её помощью можно выполнять также много других задач, в том числе арифметических. Давайте разберемся, что собой представляет данный инструмент и как с ним работать.
Специальная вставка, прежде всего, предназначена для того, чтобы вставить определенное выражение на лист Excel в том виде, в каком это нужно пользователю. С помощью этого инструмента можно вставить в ячейку не все скопированные данные, а только отдельные свойства (значения, формулы, формат и т.д.). Кроме того, используя инструменты, можно производить арифметические действия (сложение, умножение, вычитание и деление), а также транспонировать таблицу, то есть, менять в ней местами строки и столбцы.
Для того, чтобы перейти в специальную вставку, прежде всего, нужно выполнить действие по копированию.
Способ 1: работа со значениями
Если вам нужно перенести значения ячеек, результат в которых выводится с помощью вычислительных формул, то специальная вставка как раз предназначена для такого случая. Если вы примените обычное копирование, то скопируется формула, а значение, выводящиеся в ней, может оказаться совсем не тем, которое вам нужно.
Способ 2: копирование формул
Но существует и обратная ситуация, когда нужно скопировать именно формулы.
Но существует возможность произвести перенос формул с сохранением формата чисел или даже с полным сохранением исходного форматирования.
Способ 3: перенос форматирования
Если пользователю не нужно переносить данные, а он только хочет скопировать таблицу, чтобы заполнить её совершенно другой информацией, то в этом случае можно воспользоваться определенным пунктом специальной вставки.
Способ 4: копирование таблицы с сохранением размера столбцов
Не секрет, что если мы выполним простое копирование таблицы, то не факт, что все ячейки новой таблицы смогут вместить в себя всю информацию исходника. Исправить данную ситуацию при копировании можно также с помощью специальной вставки.
Способ 5: вставка рисунка
Благодаря возможностям специальной вставки можно произвести копирование любых данных, отображаемых на листе, в том числе и таблицы, как рисунка.
В окне специальной вставки подобную операцию выполнить нельзя.
Способ 6: копирование примечаний
Посредством специальной вставки можно осуществлять быстрое копирование примечаний.
Способ 7: транспонирование таблицы
С помощью специальной вставки можно производить операцию транспонирования таблиц, матриц и других объектов, в которых нужно поменять местами столбцы и строки.
Способ 8: использование арифметических действий
Посредством описываемого нами инструмента в Excel также можно выполнять распространенные арифметические действия:
- Сложение;
- Умножение;
- Вычитание;
- Деление.
Посмотрим, как применяется данный инструмент на примере умножения.
По такому же принципу можно выполнять деление, сложение и вычитание. Только для этого в окне нужно будет переставить переключатель соответственно в позицию «Разделить» , «Сложить» или «Вычесть» . В остальном все действия аналогичны вышеописанным манипуляциям.
Как видим, специальная вставка является очень полезным инструментом для пользователя. С его помощью можно производить копирование не только всего блока данных в ячейке или в диапазоне, а разделив их на различные слои (значения, формулы, форматирование и т.д.). При этом существует возможность комбинирования этих слоев друг с другом. Кроме того, посредством этого же инструмента можно выполнять арифметические действия. Безусловно, приобретение навыков работы с данной технологией значительно поможет пользователям на пути освоения программы Excel в целом.
Вставка или Ctrl+V, пожалуй, самый эффективный инструмент доступный нам. Но как хорошо вы владеете им? Знаете ли вы, что есть как минимум 14 различных способов вставки данных в листах Ecxel? Удивлены? Тогда читаем этот пост, чтобы стать пэйст-мастером.
Данный пост состоит из 2 частей:
— Основные приемы вставки
— Вставка с помощью обработки данных
1. Вставить значения
Если вы хотите просто вставить значения с ячеек, последовательно нажимайте клавиши Я, М и З, удерживая при этом клавишу Alt, и в конце нажмите клавишу ввода. Это бывает необходимо, когда вам нужно избавиться от форматирования и работать только с данными.
Начиная с Excel 2010, функция вставки значений отображается во всплывающем меню при нажатии правой клавишей мыши
2. Вставить форматы
14. Какой ваш любимый способ вставки?
Есть еще много других скрытых способов вставки, таких как вставка XML-данных, изображений, объектов, файлов и т.д. Но мне интересно, какими интересными приемами вставки пользуетесь вы. Напишите, какой ваш любимый способ вставки?
Функция «Если» в Excel является одной из самых популярных среди всех существующих в этом редакторе. Благодаря ей вы сможете выполнить различные сравнения при помощи логических выражений. При этом можно указать, что делать в зависимости от результата выполнения условия.
Принцип работы очень простой: если выражение истинное, то сделать это, иначе – сделать что-то другое.
То есть, в результате проверки условия может произойти два события:
- первое – при истине;
- второе – если возвращается ложь.
Рассмотрим основной синтаксис. При вызове окна «Вставка функции», ниже указывается описание каждого предложенного варианта.
Справка по функции «ЕСЛИ» выглядит следующим образом.
Для этого нужно выполнить следующие действия:
- Кликните на иконку вставки формул (Fx).
- В появившемся меню выберите пункт «ЕСЛИ».
- В результате этого вы увидите окно, в котором нужно указать аргументы функции:
- логическое выражение;
- значение, если истина;
- значение, если ложь.
- После ввода значений в эти поля, правее их будет отображаться предварительный результат.
- Для вставки нажмите на кнопку «OK».
Примеры
Рассмотрим несколько различных логических выражений, чтобы вы поняли, как пользоваться этой функцией.
- Сделайте активной ячейку «А1».
- Повторите описанные выше действия по вызову окна вставки «Аргументов».
- Введите в поле условие следующий код.
Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится.
- В поле для истины вводим следующее.
Именно этот текст будет выводиться в случае выполнения этого условия.
- В поле «если ложь» вводим следующее.
Данный текст мы увидим в случае ложного выполнения условия.
- Для вставки нашей формулы нажимаем на кнопку «OK».
- В результате этого мы увидим следующее (поскольку ячейка пустая, то и сообщение соответствующее).
- Введите любой текст в ячейку C.
- После нажатия на кнопку Enter вы увидите следующий результат.
Как видите, редактор Эксель выдал сообщение, которое мы указывали в случае ложного результата условия.
Операторы сравнения
Ниже вы видите список операторов сравнения:
Арифметические операторы
А это таблица арифметических операторов:
Более подробно об арифметических операторах и их приоритете в Экселе можно почитать в онлайн справке сайта Microsoft.
Несколько условий
Если хотите, то можете использовать большое количество логических выражений. Всё работает точно так же. Рассмотрим пример более сложного условия.
Составлять формулу будем через панель инструментов. Это намного удобнее, чем всё писать в одну строку, поскольку очень легко запутаться. Особенно в самом начале, когда вы только учитесь.
Для этого необходимо выполнить следующие действия.
- Перейдите на вкладку «Формулы». Кликните на кнопку «Вставить функцию».
- В результате этого всё содержимое ячейки автоматически подставится в «конструктор».
- Удалите всё, что находится в поле «Значение_если_ложь».
- Теперь в этой строке будет написано новое логическое выражение. То есть у нас будет новое вложенное условие. Для начала введите условие «Если значение ячейки С1 больше 1000». Для этого вводим следующий код. Обязательно в конце поставьте символ точки с запятой.
Очень внимательно следите за текущей раскладкой клавиатуры. Многие ошибаются и вводят русскую букву С вместо английской C. Визуально вы разницу не увидите, но для редактора это очень важно. В таком случае ничего работать не будет.
- Теперь добавим сообщение, которое будет выводиться, если число в ячейке C1 больше 1000.
Все текстовые значения обязательно вводим в кавычках.
- Теперь точно таким же образом вводим значение при невыполнении данного условия.
- В конце нажмите на кнопку «OK».
- В результате этого, мы видим сообщение о том, что введенное число больше 1000.
- Удалите содержимое ячейки C. Результатом станет следующее.
Мы проверили все три возможных результата. Всё прекрасно работает.
Копирование функции в таблицах
Иногда бывает так, что введенное логическое выражение необходимо продублировать на несколько строк. В некоторых случаях дублировать приходится очень много. Такая автоматизация намного удобнее, чем ручная проверка.
Рассмотрим пример копирования на таблице премий для сотрудников на праздники. Для этого нужно сделать следующие шаги.
- Создайте соответствующую таблицу.
- Кликните на пустую ячейку в первой строке и выберите «Fx» в поле ввода.
- В появившемся окне выберите функцию «ЕСЛИ» и нажмите на кнопку «OK».
- В первом поле введите следующее условие.
Таким образом мы проверяем, является ли данный сотрудник мужчиной.
- В случае истины вводим какое-нибудь число. Например, 3000. В случае ложного выполнения условия вводим 0. Это значит, что девушкам премию давать не нужно. Для вставки нажмите на кнопку «OK».
- Далее наведите курсор на правый нижний угол ячейки. После того как курсор изменит свой внешний вид на «черный плюс», не отпуская пальца, потяните его до самой последней строчки.
- Результатом будет следующее.
- Теперь полностью скопируйте всю таблицу (при помощи сочетания клавиш Ctrl +C и Ctrl +V ). Измените заголовок на 8 марта.
- Перейдите на первую ячейку. Кликните на поле ввода формулы.
- Измените букву «М» на «Ж».
- Теперь так же продублируйте функцию до самого низа.
- Результатом будет следующее.
Здесь мы видим, что получилась полная противоположность. Это означает, что всё работает правильно.
Использование дополнительных операторов
Кроме арифметических выражений и сравнений, также можно использовать операторы «И» и «ИЛИ». Рассмотрим их более внимательно, поскольку благодаря им возможности функции «ЕСЛИ» значительно расширяются.
Первым делом создайте таблицу, в которой будет несколько полей, по которым можно будет сравнивать строки. В нашем случае при помощи поля «Статус сотрудника» мы будем проверять, кому нужно выплатить деньги, а кому – нет.
Оператор И
Будем использовать старую функцию, в которой мы проверяли, является ли сотрудник мужчиной. Теперь нужно сделать следующие изменения.
- Добавьте в условие скобку и рядом букву «И».
- Старое условие будет первым, а второе – после точки с запятой.
- Продублируйте эту формулу до самого низа.
- В результате этого мы видим, что те сотрудники, которые не относятся к основному штату, будут лишены премии на праздники. Даже несмотря на то, что они мужчины.
Оператор Или
Того же самого эффекта можно добиться и при помощи оператора «ИЛИ».
Для этого в формулу внесите следующие изменения:
- Поменяйте букву «Ж» на «М».
- Статус сотрудника измените на «Совместителя».
- В поле истины поставьте 0, а для ложного события – 3000. Мы меняем их местами.
- Продублируем формулу до самой последней строки.
- Результат оказался точно таким же. Дело в том, что операторы «И» или «ИЛИ» являются полной противоположностью друг друга. Поэтому очень важно правильно указывать значения в поля для истины и лжи. Не ошибитесь.
- Для проверки можете изменить статус одного сотрудника на «Основной штат».
- Сразу после этого вы увидите, что напротив его фамилии появится число 3000.
Функция СУММЕСЛИ
Наверняка многие из вас делали расчеты в редакторе Excel. Но бывают случаи, когда в таблице нужно посчитать не все строки, а только лишь некоторые, соответствующие определенному условию.
Например, возьмем таблицу, в которой указаны различные товары, их стоимость, количество и статус. Посчитать общую сумму несложно. Но как узнать количество проданных продуктов?
Для этого используется функция «СУММЕСЛИ».
- Первым делом перейдите в ту ячейку, в которой нужно вывести результат.
- Затем кликните на поле ввода формулы.
- Начните вводить название функции. Справка появится автоматически.
- После этого при помощи мышки выделите последний столбец. Его значения автоматически подставятся в формулу.
- Затем поставьте точку с запятой.
- После этого в кавычках допишите слово «Продано» и снова ставим символ «;».
- Теперь указываем поле, которое будем считать – также вручную.
- Добавляем закрывающую скобку.
- Нажимаем на кнопку Enter . В результате этого вы увидите количество проданных товаров.
Функция СЧЁТЕСЛИ
Данная функция считает количество строк, которые соответствуют условию.
=СЧЁТЕСЛИ(E2:E7;»Продано»)Считается не значение ячеек, а их количество.
Результат будет следующим.
Возможные проблемы
Что делать, если возникает ошибка? Как правило, они отображаются следующим образом.
Для того чтобы попытаться исправить её, нужно сделать следующие действия.
- Кликните на поле для ввода формулы.
- Посмотрите внимательно – возможно, где-то не хватает скобки, кавычки или точки с запятой.
- В нашем случае буква Ж (текстовый символ) указан не в кавычках.
- После того как вы всё исправите, ошибка сразу исчезнет.
Кроме этого, помощь в поисках может оказать и сам редактор. Для этого достаточно кликнуть на предупредительный знак возле ячейки. Благодаря этому вы увидите подсказку и ссылку на онлайн справку по данной проблеме.
Заключение
В данной статье были рассмотрены различные способы применения функции «ЕСЛИ»:
- самый простой случай;
- с несколькими условиями;
- с применением дополнительных операторов;
- с использованием арифметических символов и прочие варианты.
Если вдруг у вас что-то не получилось, возможно, вы допускаете где-нибудь опечатку. Так же распространенной ошибкой большинства является неправильная логика в условиях.
Видеоинструкция
Если у вас остались какие-нибудь вопросы, то вы можете посмотреть видеоролик, приложенный ниже. В нем вы сможете найти дополнительные комментарии к описанной инструкции.
Всем известна возможность копирования и вставки данных через Буфер обмена операционной системы.
Комбинации горячих клавиш:
Ctrl+C - скопировать
Ctrl+X - вырезать
Ctrl+V - вставить
Команда Специальная вставка - универсальный вариант команды Вставить.
Специальная вставка позволяет осуществить раздельную вставку атрибутов скопированных диапазонов. В частности, можно вставить в новое место рабочего листа только комментарии, только форматы или только формулы из скопированного диапазона.
Чтобы эта команда стала доступной, необходимо:
В результате на экране появится диалоговое окно Специальная вставка, которое будет различным в зависимости от источника скопированных данных.
1. Вставка информации из программы Excel
Если копирование диапазона ячеек было проведено в том же приложении, то окно Специальная вставка будет выглядеть следующим образом:
Группа переключателей Вставить:
Все - выбор этой опции эквивалентен использованию команды Вставить. При этом копируется содержимое ячейки и формат.
Формулы - выбор этой опции позволяет вставить только формулы в том виде, в котором они вводились в строку формул.
Значения - выбор данной опции позволяет скопировать результаты расчетов по формулам.
Форматы - при использовании данной опции, в ячейку или диапазон будет вставлен только формат скопированной ячейки.
Примечания - если нужно скопировать только примечания к ячейке или диапазону, то можно воспользоваться данной опцией. Эта опция не копирует содержимое ячейки или атрибуты ее форматирования.
Условия на значения - если для конкретной ячейки был создан критерий допустимости данных (с помощью команды Данные | Проверка), то этот критерий можно скопировать в другую ячейку или диапазон, воспользовавшись данной опцией.
Без рамки - часто возникает необходимость скопировать ячейку без рамки. Например, если у Вас есть таблица с рамкой, то при копировании граничной ячейки будет скопирована также и рамка. Чтобы избежать копирования рамки можно выбрать эту опцию.
Ширины столбцов - можно скопировать информацию о ширине столбца из одного столбца в другой.
Совет! Эту функцию удобно использовать при копировании готовой таблицы с одного листа на другой.
Часто после вставки скопированной таблицы на новый лист приходится корректировать ее размеры.
Лист 1 Исходная таблица
Лист 2 Вставка
Чтобы этого избежать, воспользуйтесь вставкой Ширины столбцов. Для этого:
- Скопируйте исходную таблицу.
- Перейдите на новый лист и выделите ячейку для вставки.
- Откройте диалоговое окно Специальная вставка, отметьте опцию Ширины столбцов и нажмите ОК.
В результате Вы получите точную копию исходной таблицы на новом листе.
Опция пропускать пустые ячейки не позволяет программе стирать содержимое ячеек в области вставки, что может произойти, если в копируемом диапазоне есть пустые ячейки.
Опция транспонировать меняет ориентацию копируемого диапазона. Строки становятся столбцами, а столбцы — строками. Подробнее об этой опции можно прочитать в Фишке Excel «Транспонирование».
Переключатели из группы Операция позволяют выполнять арифметические операции.
Разберем примеры выполнения математических операций с помощью диалогового окна Специальная вставка.
Задача 1. Прибавить 5 к каждому значению в ячейках А3:А12
- Вносим значение «5» в любую ячейку(в данном примере - С1).
- Копируем значение ячейки С1.
- Выделяем диапазон А3:А12 и открываем диалоговое окно Специальная вставка
4. Выбираем операцию Сложить и нажимаем ОК.
В результате все значения в выделенном диапазоне будут увеличены на 5.
Задача 2 . Уменьшить на 10% цены на товары, находящиеся в диапазоне Е4:Е10 (не пользуясь формулами).
- С учетом скидки новая цена будет составлять 90% прежней, следовательно, вносим значение 90% в любую из ячеек (в данном примере - F2).
- Скопируем содержимое ячейки F2 в буфер обмена.
- Выделим диапазон Е4:Е10 и откроем диалоговое окно Специальная вставка
