Ни для кого не секрет, что электронные таблицы MS Excell – мощнейший инструмент обработки и структурирования данных. Часто бывает, что надо что-то сделать, например, таблицу стран мира с информацией о правилах ввоза и эксплуатации БПЛА, а как именно – непонятно. Можно, конечно, зарыться в Help, но там очень много информации, и найти нужную, да еще быстро – не всегда удается. Здесь собрана информация по наиболее интересным и наименее известным приемам и функциям MS Excell.
Содержание
Объединение текста из нескольких столбцов
Microsoft Excel позволяет объединять текст из нескольких столбцов, используя оператор & или функцию СЦЕПИТЬ. Например, введите в ячейки A1:C2 указанные ниже данные.
| A1: Имя | B1: Отчество | C1: Фамилия |
| A2: Иван | B2: Иванович | C2: Иванов |
Чтобы поместить в ячейку D2 фамилию, имя и отчество, введите одну из следующих формул:
Примечание. Символ пробела (« ») между ячейками используется, чтобы разделить фамилию, имя и отчество.
Установка области печати
Начиная с версии Microsoft Excel 97 для Windows, в меню Файл была добавлена кнопка панели инструментов Задать. Нажав кнопку Задать, можно установить область печати для выбранных элементов. После добавления кнопки Задать на существующую панель инструментов можно устанавливать область печати для выбранных элементов, нажимая данную кнопку.
Чтобы добавить кнопку Задать на панель инструментов Microsoft Excel, выполните следующие действия.
- В меню Вид последовательно выберите команды Панели инструментов и Настройка.
- Откройте вкладку Команды.
- В списке Категории щелкните строку Файл и найдите в списке «Команды» кнопку Задать.
- Щелкните строку Задать и перетащите данную команду на существующую панель инструментов.
Исключение повторяющихся элементов из списка
Чтобы на основе списка, содержащего повторяющиеся элементы, сформировать список, из которого повторяющиеся элементы будут исключены, воспользуйтесь командой Microsoft Excel Расширенный фильтр.
Для этого выполните следующие действия.
- Откройте новую книгу Microsoft Excel и введите в ячейки A1:A10 следующие значения.
A1: Фрукты A2: Яблоко A3: Вишня A4: Груша A5: Вишня A6: Слива A7: Яблоко A8: Яблоко A9: Груша A10: Яблоко - В меню Данные выберите последовательно команды Фильтр и Расширенный фильтр.
- В группе Обработка выберите параметр скопировать результат в другое место.
- В поле Исходный диапазон введите значение $A$1:$A$10.
- Установите флажок Только уникальные записи, в поле Поместить результат в диапазон введите $B$1 и нажмите кнопку ОК.
В столбце B появится приведенный ниже список, не содержащий совпадающих элементов.
B1: Фрукты B2: Яблоко B3: Вишня B4: Груша B5: Слива
Данный метод работает также при выборе нескольких колонок. Используя команду Расширенный фильтр, можно скрыть часть строк.
Перевод текстовых значений в числовые путем умножения на 1
В некоторых случаях при импорте файлов из посторонних источников числовые значения могут отображаться как числа, но обрабатываться как текстовые значения. Чтобы устранить данную проблему, преобразуйте эти значения в числа. Одним из способов преобразования является умножение данных текстовых значений на 1.
Для этого выполните следующие действия.
- Щелкните пустую ячейку на листе, убедитесь, что формат этой ячейки не является текстовым, и введите в данную ячейку значение 1.
- Выделите ячейку, выбранную на предыдущем шаге, и выберите в меню Правка команду Копировать.
- Выделите диапазон ячеек, содержащий значения, которые необходимо преобразовать в числовой формат.
- В меню Правка выберите команду Специальная вставка.
- В группе Операция выберите параметр умножить и нажмите кнопку ОК.
В результате выбранные текстовые значения будут преобразованы в числовые. Чтобы убедиться, что текстовые значения преобразованы в числовые, определите тип выравнивания ячеек с требуемыми значениями. Если используется формат ячеек «Общий», то числовые значения выравниваются по правому краю, а текстовые — по левому.
Перевод текстовых значений в числовые с помощью мастера импорта текста
Для перевода текстовых значений в числовые с помощью мастера импорта текста выполните следующие действия.
- Выделите диапазон ячеек, содержащий значения, которые необходимо преобразовать в числовой формат.
- В меню Данные выберите команду Текст по столбцам.
- Дважды нажмите кнопку Далее, чтобы перейти к шагу 3 мастера.
- В группе Формат данных столбца выберите параметр общий и нажмите кнопку Готово.
В результате выбранные текстовые значения будут преобразованы в числовые. Чтобы убедиться, что текстовые значения преобразованы в числовые, определите тип выравнивания ячеек с требуемыми значениями. Если используется формат ячеек «Общий», то числовые значения выравниваются по правому краю, а текстовые — по левому.
Сортировка десятичных чисел в структурах
Предположим, что в ячейках A1:A6 содержатся следующие структуры чисел.
| A1: 1.1.0 |
| A2: 1.10.0 |
| A3: 1.2.0 |
| A4: 1.20.0 |
| A5: 1.21.1 |
| A6: 1.3.0 |
После сортировки этих структур их порядок не изменится. Чтобы выполнить сортировку, учитывая числа, разделенные символом точки, воспользуйтесь мастером импорта текста. Для этого выполните следующие действия.
- Выделите ячейки A1:A6.
- В меню Данные выберите команду Текст по столбцам.
- На шаге 1 работы мастера импорта текста выберите параметр с разделителями и нажмите клавишу Далее.
- В группе Символом-разделителем является снимите все флажки, за исключением флажка другой. В поле флажка другой введите точку и нажмите кнопку Далее.
- На шаге 2 введите в поле Поместить в значение $B$1, чтобы не удалить исходную структуру, и нажмите кнопку Готово.
Соответствующие числа появятся в столбцах B, C и D.
- Выделите ячейки A1:D6.
- В меню Данные выберите команду Сортировка.
- В списке Сортировать по выберите значение Столбец B.
- В списке Затем по выберите значение Столбец С.
- В списке В последнюю очередь по выберите значение Столбец D и нажмите кнопку OК.
В столбце A появится отсортированный список.
Добавление записей к списку с помощью форм данных
Чтобы добавить записи к списку, воспользуйтесь готовыми формами данных. Чтобы открыть окно формы данных, выберите в меню Данные команду Форма.
Ввод текущей даты или времени
Чтобы быстро поместить в ячейку значение текущей даты, нажмите клавиши CTRL+;, а затем нажмите клавишу ВВОД. Чтобы быстро поместить в ячейку значение текущего времени, нажмите клавиши CTRL+:, а затем нажмите клавишу ВВОД.
Отображение аргументов формулы
Чтобы отобразить аргументы формулы, содержащейся в текущей ячейке, нажмите клавиши CTRL+SHIFT+A. Если ввести =НОРМА и нажать клавиши CTRL+SHIFT+A, то отобразятся аргументы, используемые этой формулой (например =НОРМА((кпер,выплата,нз,бс,тип,предположение))). Для получения дополнительных сведений введите =НОРМА и нажмите клавиши CTRL+A, чтобы запустить мастер функций.
Помещение текста или формулы в несколько ячеек
Чтобы поместить в несколько ячеек один и тот же текст или формулу, выполните следующие действия.
- Выделите диапазон ячеек, в которые следует ввести текст или формулу.
- Введите требуемый текст или формулу, но вместо клавиши ВВОД нажмите клавиши CTRL+ВВОД.
Введенный текст или формула появятся во всех выделенных ячейках.
Привязка надписи к данным в ячейке
Чтобы привязать надпись к данным в ячейке, выполните следующие действия.
- На панели инструментов Рисование нажмите кнопку Надпись. Щелкните лист и протащите указатель мыши, чтобы создать поле для надписи.
- Чтобы ввести изменения в строке формул, щелкните строку формул или нажмите клавишу F2.
- Введите требуемую формулу. Например, введите =A1 и нажмите клавишу ВВОД.
Текст, содержащийся в связанной ячейке, появится в окне надписи (например A1). Данную надпись можно переместить на любой лист книги.
Привязка изображения к диапазону ячеек
Microsoft Excel позволяет скопировать диапазон ячеек и вставить на лист итоговое изображение. Это дает возможность видеть содержимое требуемых ячеек, находясь в любой точке листа. Данный метод позволяет отобразить содержимое несмежных ячеек, находящихся на одной странице. Итоговое изображение обновляется как при изменении соответствующего содержимого, так и при изменении формата. Чтобы создать связанное изображение, выполните следующие действия.
- Выделите требуемый диапазон ячеек.
- В меню Правка выберите команду Копировать.
- Выделите ячейку, в которой должно размещаться итоговое изображение.
- Нажмите клавишу SHIFT и, удерживая ее нажатой, выберите в меню Правка команду Вставить связь с рисунком.
При этом будет сформировано изображение, которое будет обновляться по мере изменения содержимого исходных ячеек или их форматирования.
Устранение неполадок при использовании длинных формул
Если длинная формула возвращает ненадлежащий результат, протащите указатель мыши по строке формул, чтобы выделить какую-либо часть этой формулы, и нажмите клавишу F9. При этом выделенная часть формулы будет вычислена.
Внимание! Если после выполнения вышеописанных действий нажать клавишу ВВОД, выделенная часть формулы будет утеряна. Поэтому необходимо нажать клавишу ESC. Если по ошибке была нажата клавиша ВВОД, нажмите клавиши CTRL+Z, чтобы отменить изменения.
Выделение определенных имен
Примечание. Данный раздел относится только к Microsoft Excel 97 для Windows.
Если указать в поле Масштаб значение 39 процентов или меньше, то состоящие из двух или более смежных ячеек диапазоны, которым сопоставлены определенные имена, будут при отображении заключены в прямоугольные рамки. Если на панели инструментов Стандартная указать в поле Масштаб значение 40 процентов и более, прямоугольники, ограничивающие именованные диапазоны, автоматически исчезнут. Данная возможность недоступна в более ранних версиях Microsoft Excel.
Заполнение пустых ячеек в столбце содержимым предыдущей ячейки
Предположим, что в столбце A введены следующие имена:
Чтобы правильно отсортировать эти имена, необходимо ввести имена в пустые ячейки. Для этого выполните следующие действия.
- Выделите ячейки A1:A10.
- В меню Правка выберите команду Перейти.
- Нажмите кнопку Выделить, выберите параметр пустые ячейки и нажмите кнопку ОК.
- Введите =a1 и нажмите клавиши CTRL+ВВОД.
При этом в выделенные пустые ячейки будут введены имена.
- Выделите ячейки A1:A10.
- В меню Правка выберите команду Копировать.
- В меню Правка выберите команду Специальная вставка.
- В группе Вставить выберите параметр Значения и нажмите кнопку ОК.
Теперь ячейки заполнены именами.
Переключение между режимами отображения абсолютных и относительных ссылок
Чтобы переключиться между режимами отображения абсолютных и относительных ссылок в формуле, нажмите клавишу F4. При вводе формулы в строке формул указывайте адреса ячеек в относительном формате (например A1). Указав ссылку, нажмите клавишу F4, и адрес ячейки автоматически преобразуется в абсолютный формат (например $A$1). Повторно нажимая клавишу F4, можно отобразить ссылки в смешанном (абсолютном и относительном) формате.
Чтобы получить дополнительные сведения о ссылках на ячейки, откройте справку по Microsoft Excel, перейдите на вкладку Мастер ответов, введите абсолютное и относительное и дважды щелкните название раздела Различие между относительными и абсолютными адресами.
Изменение данных во вставляемых ячейках с помощью функции СМЕЩ
Предположим, что в ячейках A1:A7 хранятся приведенные ниже данные и что содержимое последней строки данного диапазона необходимо вычесть из содержимого первой строки диапазона.
| A1: 1 |
| A2: 2 |
| A3: 3 |
| A4: 4 |
| A5: 5 |
| A6: |
| A7: =A5-A1 |
Предположим, что для вычисления требуемого значения следует использовать формулу, которая должна находиться на 2 строки ниже последней ячейки, содержащей данные. При этом между последней ячейкой с данными и ячейкой с формулой должна находиться пустая ячейка. Предположим, что после вставки новой строки перед пустой строкой (строка 6 в следующем примере), необходимо, чтобы данные, хранящиеся в ячейке A1, вычитались из данных, хранящихся в ячейке A6, а не A5.
В этом случае после ввода данных в ячейку A6 формула =A5-A1 не будет учитывать введенные данные.
Чтобы учитывать эти данные, используйте функцию СМЕЩ. Функция СМЕЩ возвращает ссылку на диапазон, смещенный на заданное количество строк и столбцов относительно ячейки или диапазона ячеек. В данном примере необходимо использовать следующую формулу:
Формула, использующая функцию СМЕЩ, не привязана к строке, предшествующей ячейке A6, и ее результат изменяется по мере вставки новых строк.
Использование команды «Расширенный фильтр»
Команда Расширенный фильтр позволяет выбрать из списка данных определенные элементы и скопировать их на другой лист. Чтобы использовать данную команду, выберите в меню Данные пункт Фильтр, затем — команду Расширенный фильтр и следуйте инструкциям на экране. Сведения о параметрах, запрашиваемых Microsoft Excel при выполнении данной команды, см. в справке по Microsoft Excel.
Суммирование данных с помощью условного суммирования
Предположим, что необходимо найти сумму всех чисел, хранящихся в ячейках A1:A10, значения которых больше 50 и меньше 200. Для этого воспользуйтесь следующей формулой массива:
Примечание. Убедитесь, что данная формула введена как формула массива — путем нажатия клавиш CTRL+SHIFT+ВВОД. После этого формула заключается в фигурные скобки («{}»). Не вводите фигурные скобки вручную.
В данной формуле с помощью вложенных функций ЕСЛИ анализируется содержимое каждой ячейки заданного диапазона. Если содержимое ячейки удовлетворяет обоим условиям, оно добавляется к общей сумме.
Подсчет числа элементов с помощью условного суммирования
Предположим, что для диапазона A1:A10 необходимо определить число ячеек, значение которых больше 50 и меньше 200. Для этого воспользуйтесь следующей формулой массива:
Примечание. Убедитесь, что данная формула введена как формула массива — путем нажатия клавиш CTRL+SHIFT+ВВОД. После этого формула заключается в фигурные скобки («{}»). Не вводите фигурные скобки вручную.
В данной формуле с помощью вложенных функций ЕСЛИ анализируется содержимое каждой ячейки заданного диапазона. Если содержимое ячейки удовлетворяет обоим условиям, результат увеличивается на единицу.
Поиск данных с помощью функций ИНДЕКС и ПОИСКПОЗ
Предположим, что в ячейках A1:C5 содержатся приведенные ниже данные и что в ячейках C1:C5 указан возраст соответствующего лица:
Чтобы определить возраст какого-либо человека, указанного в данной таблице, по его имени, можно применить формулу, использующую функции ИНДЕКС и ПОИСКПОЗ, как показано в следующем примере:
В данной формуле ячейки A1:C5 рассматриваются как таблица, и в третьей колонке данной таблицы ищется возраст человека по имени Mary. В данном примере формула возвращает значение 22.
Создание последовательности значений путем перетаскивания маркера заполнения
Перетаскивая маркер заполнения ячейки, можно копировать содержимое выбранной ячейки в другие ячейки текущей строки или столбца. Если в исходной ячейке содержится число, дата или значение периода времени, Microsoft Excel формирует возрастающую последовательность и заносит в новые ячейки данные из этой последовательности. Например, если в исходной ячейке содержится значение «январь», то в результате перетаскивания маркера заполнения в ячейки текущей строки или столбца будут занесены значения «февраль», «март» и т. д. Кроме того, Microsoft Excel позволяет пользователям формировать собственные последовательности заполнения на основе текстовых значений (например названий областей).
Автоматическое заполнение данных
Если дважды щелкнуть маркер заполнения какой-либо ячейки, то ячейки, находящиеся ниже в данном столбце, будут заполнены автоматически. При этом данные для заполнения формируются на основе значения исходной ячейки, а число заполняемых ячеек определяется числом заполненных ячеек в смежном столбце. Например, если ввести какие-либо данные в ячейки A1:A20, ввести текст или формулу в ячейку B1, нажать клавишу ВВОД, а затем дважды щелкнуть маркер заполнения ячейки B1, Microsoft Excel автоматически заполнит ячейки B2:B20.
Использование функции ВПР с несортированными данными
В Microsoft Excel 97 для Windows и более поздних версиях Microsoft Excel функция ВПР работает с несортированными данными. Для этого необходимо указать в формуле дополнительный аргумент — диапазон_просмотра. Если данный аргумент не указан, его значение принимается равным ИСТИНА. Аргумент диапазон_просмотра является четвертым аргументом данной функции, что обеспечивает совместимость этой функции с более ранними версиями Microsoft Excel.
Чтобы функция ВПР правильно работала с несортированными данными, укажите в качестве аргумента диапазон_просмотра значение «ЛОЖЬ». Ниже приведен пример формулы, которая анализирует содержимое таблицы, рассматриваемой в разделе «Поиск данных с помощью функций ИНДЕКС и ПОИСКПОЗ», и определяет возраст человека по имени Stan:
Выбор каждого третьего значения
Предположим, что в ячейках A1:A12 содержатся приведенные ниже данные, и что каждое третье значение из этого столбца необходимо поместить в смежный столбец:
Для этого необходимо использовать функции СТРОКА и СМЕЩ. Например, можно воспользоваться следующей формулой:
Значение, возвращаемое данной формулой, зависит от номера строки ячейки, в которой введена эта формула. В данной формуле функция СТРОКА возвращает номер строки ячейки, в которой введена эта формула. Затем этот номер умножается на 3 и передается функции СМЕЩ, которая возвращает содержимое ячейки, расположенной ниже ячейки A1 на заданное число строк. В результате формируется список, содержащий каждое третье значение из исходного столбца.
Округление до ближайшего числа копеек
Предположим, что в ячейках A1:A3 содержатся следующие формулы.
| A1: =1,23/2 |
| A2: =1,21/2 |
| A3: =СУММ(A1:A2) |
Предположим, что в этих формулах рассматриваются денежные суммы и что используется формат «Финансовый». В этом случае возвращаемые значения будут выглядеть следующим образом.
| A1: 0,62р. |
| A2: 0,61р. |
| A3: 1,22р. |
В результате в ячейке A3 отображается ошибочное значение суммы. Данная проблема возникает из-за того, что при отображении используются округленные значения, однако фактические значения не округляются до ближайшего числа копеек. Чтобы устранить эту проблему, используйте функцию ОКРУГЛ. Например, измените формулы следующим образом.
| A1: =ОКРУГЛ(1,23/2,2) |
| A2: =ОКРУГЛ(1,21/2,2) |
| A3: =ОКРУГЛ(СУММ(A1:A2),2) |
Второй аргумент функции ОКРУГЛ задает количество десятичных разрядов для округления. В данном примере число 2 предписывает выполнять округление до ближайшего числа сотых.
Установка и использование справки Microsoft Excel
Справка Microsoft Excel позволяет найти темы, посвященные особенностям использования Microsoft Excel, просмотреть список найденных тем, а также выполнить поиск по отдельным словам и фразам. Кроме того, для просмотра сведений, относящихся к выполняемой задаче, можно нажать клавишу F1 и воспользоваться контекстной справкой.
Чтобы получить доступ к справке, необходимо установить файлы справки. Если файлы справки не установлены, запустите программу установки и нажмите кнопку Добавить или удалить, чтобы установить эти файлы.
Загрузка и сохранение файлов, находящихся на дискете
При работе с книгой Microsoft Excel создает временные файлы в папке, в которой сохраняется данный файл, и в папке, из которой данная книга была загружена. При закрытии книги эти временные файлы удаляются. Кроме того, при сохранении файла Microsoft Excel создает на указанном диске копию данного файла. Это может вызывать проблемы, если книга была открыта с дискеты или если на дискете недостаточно свободного места для сохранения данного файла.
Поэтому рекомендуется перед открытием файла скопировать данный файл на жесткий диск, а затем открывать его с жесткого диска. После внесения изменений сохраните файл на жестком диске, а затем скопируйте на дискету.
Сочетания клавиш для создания новой диаграммы или листа
Чтобы быстро создать диаграмму, выделите данные, необходимые для построения диаграммы, и нажмите клавишу F11. Чтобы создать новый лист, нажмите клавиши SHIFT+F11.
Выбор нескольких областей печати на одном листе
Microsoft Excel позволяет указать на одном листе несколько областей печати, не используя макросы. Для этого необходимо с помощью команд «Представления» и «Печать отчета» задать представления листа, а затем создать отчет, используя требуемые представления. Дополнительные сведения см. в справке Microsoft Excel.