Как работать в Excel (программе): советы начинающим. Создание формул в Excel Скачивание книги "Учебник по формулам"

Большинство пользователей компьютерных систем на основе Windows с установленным пакетом Microsoft Office непременно сталкивались с приложением MS Excel. У начинающих юзеров программа вызывает некоторые сложности в освоении, тем не менее работа в «Экселе» с формулами и таблицами не так уж и сложна, как может показаться на первый взгляд, если знать основные принципы, заложенные в приложении.

Что такое Excel?

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

Работа с таблицами «Эксель» подразумевает использование более широких возможностей с объединением и вычислений, и обычного текста, и мультимедиа. Но в своем изначальном виде программа создавалась именно как мощнейший математический редактор. Некоторые, правда, поначалу принимают приложение за какой-то калькулятор с расширенными возможностями. Глубочайшее заблуждение!

Работа в «Экселе» с таблицами для начинающих: первое знакомство с интерфейсом

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

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

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

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

Основные типа ввода данных и простейшие операции

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

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

Но ввод несколько отличается от того, который производится в текстовых редакторах. Дело в том, что программа изначально настроена на автоматическое распознавание того, что пользователь прописывает в активной ячейке. Например, если ввести строку 1/2/2016, данные будут распознаны как дата, и в ячейке вместо введенных цифр появится дата в упрощенном виде - 01.02.2016. Изменить формат отображения можно достаточно просто (на этом остановимся чуть позже).

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

Но по окончании ввода данных многие начинающие пользователи пытаются перейти на следующую ячейку, используя для этого клавиатурные стрелки (по типу того, как это можно сделать в таблицах Word). И это не срабатывает. Почему? Да только потому, что работа с таблицами «Эксель» отличается от текстового редактора Word достаточно ощутимо. Переход может осуществлен нажатием клавиши Enter или установкой активного прямоугольника на другую ячейку при помощи левого клика мыши. Если после написания чего-то в активной ячейке нажать клавишу Esc, ввод будет отменен.

Действия с листами

Работа с листами на первых порах трудностей вызывать не должна. На панели снизу имеется специальная кнопка добавления листов, после нажатия на которую появится новая таблица с автоматическим переходом на нее и заданием имени («Лист 1», «Лист 2» и т. д.).

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

Форматы ячеек

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

В окне слева представлены все доступные форматы, а справа показаны варианты отображения данных. Если посмотреть на пример с датой, показанный выше, в качестве формата выбирается «Дата», а справа устанавливается желаемый вид (например, 1 февраля 2016 г.).

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

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

Работа в «Эксель» с формулами (таблицами): пример

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

Но можно ввести формулу и вручную (работа с таблицами «Эксель» предполагает и такую возможность, когда автоматическое действие не предусмотрено). Для той же суммы следует в строке формул поставить знак равенства и прописать операцию в виде A1+A2 или СУММ(A1;A2), а если требуется указать диапазон ячеек, используется такой вид после знака равенства: (A1:A20), после чего будет посчитана сумма всех чисел, находящихся в ячейках от первой до двадцатой включительно.

Построение графиков и диаграмм

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

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

Перекрестные связи, импорт и экспорт данных

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

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

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

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

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

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

1. Каждая начинается со знака «=».
2. Участвовать в вычислениях могут значения из ячеек и функции.
3. В качестве привычных нам математических знаков операций используются операторы.
4. При вставке записи в ячейке по умолчанию отражается результат вычислений.
5. Посмотреть конструкцию можно в строке над таблицей.

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

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

Обозначение Значение

Сложение
- Вычитание
/ Деление
* Умножение

Если вам необходимо указать число, а не адрес ячейки – вводите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

Как вводить и скопировать формулы в Excel

Ввод их всегда осуществляется после нажатия на «=». Но что делать, если однотипных расчетов много? В таком случае можно указать одну, а затем ее просто скопировать. Для этого следует ввести формулу, а затем «растянуть» ее в нужном направлении, чтобы размножить.
Установите указатель на копируемую ячейку и наведите указатель мыши на правый нижний угол (на квадратик). Он должен принять вид простого крестика с равными сторонами.


Нажмите левую кнопку и тяните.


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


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


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


Цена с НДС высчитывается как цена*(1+НДС). Введем последовательность в первую ячейку.


Попробуем скопировать запись.


Результат получился странный.


Проверим содержимое во второй ячейке.


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


Нажмите F4. Адрес будет разбавлен знаком «$». Это и есть признак абсолютно ячейки.


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

Использование функций для вычислений

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


Расскажем о некоторых функциях.

Как задать формулы «Если» в Excel

Эта функция позволяет задавать условие и проводить расчет в зависимости от его истинности или ложности. Например, если количество проданного товара больше 4 пачек, следует закупить еще.
Чтобы вставить результат в зависимости от условия, добавим еще один столбец в таблицу.


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


Как и при вставке любой функции, откроется окно для заполнения аргументов.


Укажем условие. Для этого необходимо щелкнуть в первую строку и выбрать первую ячейку «Продано». Далее поставим знак «>» и укажем число 4.


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


Нажмите ОК и скопируйте запись для всего столбца.


Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.


Затем ОК и снова скопируйте.


Теперь мы видим, какой товар следует закупить.

Формула текст в Excel

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


В первую ячейку введем функцию (кнопка «Текстовые» в разделе «Формулы»).


В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».


Нажмем ОК и скопируем.


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

«ЗНАЧ» обозначает, что вычисления не могут быть произведены.
Примеры форматов вы можете видеть на скриншоте.

Формула даты в Excel

Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.

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

Расставьте адреса ячеек соответствующим образом и нажмите ОК.


Скопируйте запись.

Автосумма в Excel

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


Перейдите на панель «Формулы» и нажмите «Автосумма».


Excel автоматически выделит ближайший числовой диапазон.


Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.


При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Работа в Excel с формулами: пример

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


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

Чтобы переименовать лист, два раза на нем щелкните и введите имя.

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

Отличного Вам дня!

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

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

Функция «ВПР»

Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)» . Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.

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

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

Сводные таблицы

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

Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица» .

Создание диаграмм

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

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

Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами» .

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

Формулы в Excel

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

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

Функция «ЕСЛИ»

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

Операторами «И» , «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.

Макросы

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

Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.

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

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

Форматирование будет выполнено.

«Умная» таблица

Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная» , кликаем по кнопке «Форматировать как таблицу» . Появится список с различными вариантами стилей оформления, где укажите подходящий.

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

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

Подбор параметра

С помощью функции подбора параметров можно подобрать исходные данные, руководствуясь желаемым для вас результатом. Перейдите на вкладку «Данные» и нажмите кнопку «Анализ «что если»» , расположенную в блоке инструментов «Работа с данными» . В появившемся списке укажите пункт «Подбор параметра…» .

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

Функция «ИНДЕКС»

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

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

Группировка данных

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

Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам (см. рис. 2).

Рисунок 2 – Выбор типа группировки

В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.

Рисунок 3 – Группировка строк «вниз»

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

Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .

Рисунок 5 – Окно настроек структуры

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

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

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

Рисунок 6 – Многоуровневая структура каталога в Excel

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

Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

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

Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.

Рисунок 8 – Закрепляем строку или столбец

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

А вот закрепление нескольких строк или области из строк и столбцов осуществляется не так прозрачно. Вы выделяете три строки, щёлкаете по пункту Закрепить области , и… Excel «замораживает» только две. Почему так? Возможен ещё более плохой вариант, когда области закрепляются непредсказуемым образом (например, вы выделяете две строки, а программа ставит границы после пятнадцатой). Но не будем списывать это на недосмотр разработчиков, потому что единственный правильный вариант использования данной функции выглядит по-другому.

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

Рисунок 9 – Закрепляем область из строк и столбцов

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

Поворот листа (замена строк на столбцы и наоборот)

Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

1. Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
2. Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
3. Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
4. Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

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

Перенос строк в ячейках

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

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

В зависимости от своих предпочтений вы можете пойти двумя путями:

1. Сначала создать надпись, а потом её повернуть.
2. Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

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

Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.

Рисунок 25 – Выбираем заготовку условного форматирования

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

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

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

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

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек (см. рис. 28).

Рисунок 28 – Удаляем правила условного форматирования

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

Использование фильтров

Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.

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

После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).

Рисунок 29 – Создаём фильтры

Теперь ячейки преобразятся в раскрывающиеся списки, где вы можете задать параметры выборки. Например, мы ищем все упоминания про Intel в столбце Наименование товара . Для этого выбираем текстовый фильтр Содержит (см. рис. 30).

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

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

Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).

Рисунок 32 – Числовые фильтры

Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).

Отображение информационных кривых

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

Рисунок 33 – Инфокривая Excel 2010

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

Рисунок 34 – Вставка инфокривой

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

Заключение

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

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

Для создания простой формулы достаточно следовать следующей инструкции:

1. Сделайте активной любую клетку. Кликните на строку ввода формул. Поставьте знак равенства.

1. Введите любое выражение. Использовать можно как цифры,

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

Из чего состоит формула

В качестве примера приведём следующее выражение.

Оно состоит из:

• символ «=» – с него начинается любая формула;
• функция «СУММ»;
• аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
• оператора «+» (сложение);
• ссылки на ячейку «C1»;
• оператора «^» (возведение в степень);
• константы «2».

Использование операторов

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

• скобки;
• экспоненты;
• умножение и деление (в зависимости от последовательности);
• сложение и вычитание (также в зависимости от последовательности).

Арифметические

К ним относятся:

• сложение – «+» (плюс);

• отрицание или вычитание – «-» (минус);

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

• умножение – «*»;

• деление «/»;

• процент «%»;

• возведение в степень – «^».

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

• знак «равенства» – «=»;

• знак «больше» – «>»;

• знак «меньше» – «<»;