Этот материал рассчитан на пользователей, имеющих минимальный опыт работы в MS Ofice Excel.
Предполагается, что пользователь уже умеет рисовать таблицы, раскрашивать шрифты, границы, фон ячеек и т.п.
В материале будет показано как облегчить свою повседневную работу настроив удобно интерфейс, изучите самые часто используемые функции, возможности и всё это на понятном и доступном языке.
Меня часто просили научить работать в Excel, что однажды пришлось сесть и написал небольшой самоучитель в стиле «просто и коротко о главном». Читайте, учитесь, а если вас просят «Научи» или «Подскажи», то можете смело дать ссылку на эту статью, тем самым сэкономив своё время. Хотите, чтобы ваши сотрудники стали эффективней, тогда тоже, порекомендуйте им эту статью. Приятного изучения!
Перемещение и копирование листов
Область ячеек, именованные области
Разделение рабочей области (окна)
Приложение MS Excel является реализацией электронной таблицы. Файл приложения на внутреннем языке программы (VBA) в переводе звучит как «Книга», т.к. состоит из одной или нескольких страниц (листов). В некоторых файлах MS Excel созданных в других приложениях ярлычки листов могут быть скрыты. В таких случаях надо изменить настройку отображения ярлычков из меню «Пуск – Параметры Excel – Дополнительно – Показывать ярлычки листов».
При нажатии правой кнопкой мыши по ярлычку всплывает контекстное меню ярлычка и листа.
Здесь можно:
· переименовать лист,
· задать цвет ярлычка,
· переместит/скопировать,
· скрыть или отобразить ранее скрытые листы
· и пр.
Каждая страница состоит из ячеек, которые в совокупности образуют строки и колонки. У каждой строки и колонки страницы имеется свое название. У строк названия описываются только числовыми символами, а столбцы могут описываться как латинскими буквами так и цифрами.
Настройка стиля отображения происходит из меню «Пуск – Параметры Excel – Формулы – Стиль ссылок R1C1». Row – строка, Column – колонка.
Перемещение и копирование листов
Для вызова окна перемещения/копирования листов необходимо вызвать соответствующее диалоговое окно через контекстное меню ярлычка (Рис. 2).
Листы можно переместить как внутри книги, так и между одновременно открытыми книгами или в отдельный новый файл. Для этого имеется список выбора «в книгу:» и «перед листом:».
Если необходимо создать копию листа, тогда необходимо установить соответствующий флаг «Создать копию».
Скрытие листов бывает удобным при хранении не меняющихся данных или при избытке листов в книге. Делается это просто, через контекстное меню ярлыка. Если потребуется отобразить скрытый лист, то в контекстном меню ярлыка нажмите «Отобразить…» и откроется список скрытых листов. Выберите нужный лист и нажмите «ОК».
Каждая ячейка располагается на пересечении определенной строки и колонки. Таким образом у каждой ячейки есть адрес расположения состоящий из номера строки и названия колонки.
Обратите внимании, что в зависимости от выбранного стиля ссылок, у каждой ячейки будет по-разному отображаться её адрес: B5 или R5C2 соответственно.
Таким образом, существует два стиля отображения ссылок: А1 и R1C1.
При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+~) в двух вариантах адресации:
Найти ошибку в режиме R1C1 намного проще, правда?
Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
Другие случаи, когда удобней использовать стиль ссылок R1C1 касаются области программирования.
Кроме того, ссылки бывают абсолютными и относительными.
Для начала, необходимо создать простую таблицу, ввести формулу, как на рисунке и нажать ENTER.
Затем выделить эту ячейку и потянуть вниз за правый нижний угол, обратите внимание на вид курсора.
В результате ячейки строк столбца «Е»заполнятся формулами, где обращение будет к ячейкам соответствующей строки.
А как быть, если необходимо вывести сумму в долларовом эквиваленте? Для этого можно воспользоваться абсолютной ссылкой.
Абсолютная ссылка создаётся с помощью знака привязки – «$». Привязать можно столбец, строку либо то и другое. В данном случае ссылка будет абсолютной по строке и столбцу.
Теперь, если потянуть за правый нижний угол ячейки, абсолютная ссылка на ячейку с курсом будет неизменной.
Та же таблица, но только в стиле R1C1.
RC — относительная ссылка на текущую ячейку
R2C2 — то же самое, что $B$2 (абсолютная ссылка)
RC5 — ссылка на ячейку из пятого столбца в текущей строке
RC[-1] — ссылка на ячейку из предыдущего столбца в текущей строке
RC[2] — ссылка на ячейку, отстоящую на два столбца правее в той же строке
R[2]C[-3] — ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
R5C[-2] — ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
Контекстное меню строк и колонок одинаковы, различаются только пункты отвечающие за настройку высоты строк и ширину колонок.
В верхней группе расположены команды работы с буфером обмена. Команде «Специальная вставка…» будет уделено отдельное внимание, но позже.
Далее расположена группа команд в отношении строк (Вставить – строки, Удалить – строки).
Столбцы и строки при необходимости можно скрывать и отображать.
Область ячеек, именованные области
Одновременно выделенные несколько ячеек образуют область ячеек. Если область состоит из одной группы соседних между собой ячеек (прямоугольная), то адрес для такой области задаётся исходя из положения верхней левой и правой нижней ячеек, между адресами ячеек ставится знак двоеточия.
Если необходимо обозначить область состоящую из нескольких областей, то такие области записываются как последовательность описай адресов ячейки и/или прямоугольных выделенных областей через знак точки с запятой.
Любой области можно назначить имя. По имени можно обращаться к именованной области как из самой книги, где находится именованная область, так и из других книг. Имеются случаи, когда использование именованных диапазонов очень удобно.
Для именования необходимо выделить необходимую область (или области с использованием клавиши Ctrl).
Далее, можно через нажатие правой кнопкой мыши по выделенной области вызвать контекстное меню, где в раскрывшемся списке выбрать пункт «Имя диапазона…».
Так же, можно указать имя диапазону ячеек через команды расположенные на ленте. Для этого надо перейти на вкладку «Формулы» и нажать кнопку «Присвоить имя…».
В результате откроется окно:
В поле «Имя» вводится название диапазона. Другие параметры будут рассмотрены в другом разделе. Теперь появилась возможность использования имени вместо адреса диапазона.
Форматы данных ячеек
Одним из важных понятий при работе с Excel является понимание форматов данных.
Ячейки могут содержать данные различных форматов, это:
Числовой, Денежный, Финансовый, Процентный, Дата, Время, Экспоненциальный, Текстовый, Дополнительный.
В рамках данной статьи будут рассмотрены только Общий, Числовой, Процентный, Дата и Текстовый форматы данных.
Почему это важно знать. Например, данные формата «Число», «Дата» или «Текст» могут совершенно одинаково выглядеть на странице, но для программы эти данные будут разными. Текст «100» невозможно сложить с текстом «50» в отличии от сложения чисел 100 и 50. Между двумя текстами «01.01.2013» и «31.02.2013» невозможно рассчитать количество дней в отличие от данных формата «Дата» 01.01.2013 и 31.01.2013.
Общий формат автоматически определяет формат данных. Если в ячейку с клавиатуры ввести текст, число или дату, то данные автоматически будут преобразованы в соответствующий формат. Это преобразование не всегда бывает корректным и тогда появляется необходимость точного определения формата данных.
У данных формата «Число» задается точность в виде количества символов после запятой.
Числовой формат с легкостью заменяет использование денежного и финансового формата.
Возможные ошибки: Иногда в таблицах размещаются числа в текстовом формате. Такие данные не подлежат суммированию и другим расчетам. Обязательно, отделение дробной части делать с помощью символа запятой. Если у вас имеется файл где в числах проставлена точка вместо запятой, исправить ситуацию можно выделив необходимый диапазон и вызвать окно «Поиска и Замены» и автоматически заменить символ «.» на «,». Программа может автоматически преобразовать данные в формат Число, если этого не произошло, тогда измените формат вручную.
Формат «Дата» позволяет настроить отображение дат.
Возможные ошибки: Иногда в таблицах размещаются даты в текстовом формате. Такие данные не подлежат расчетам. Обязательно, отделение дробной части делать с помощью символа точки. Если у вас имеется файл где даты записаны как текст, исправить ситуацию можно выделив необходимый диапазон и вызвать окно «Поиска и Замены» и автоматически заменить символ «.» на «/». Программа может автоматически преобразовать данные в формат Дата, если этого не произошло, тогда измените формат вручную.
Процентный формат позволяет настроить количество знаков после запятой. От числового формата отличается тем, что при отображении на экране значение ячейки будет умножено на 100 и будет выводиться с символом процента.
Обратите внимание на то, что в обеих ячейках хранится цифра 5 и при обращении к любой из этих ячеек мы получим значение 5.
Текстовый формат позволяет выводить строки из символов текста.
Возможные ошибки: иногда приходится вводить текст, который начинается с символов «=, -, +, *, /» программа начинает сообщать об ошибке. Это связано с тем, что программа автоматически начинает воспринимать данные как формулу. Бывают случаи, когда данные наподобие 1.2 программа автоматически преобразует в дату 01.фев (программа подставляет текущий год). Чтобы это избежать, необходимо задать в соответствующих ячейках текстовый формат.
Работа с интерфейсом
При работе с таблицами большого размера имеются различные возможности как фиксация колонок слева, сверху, разделение окон и добавление окон.
Закрепить области
Текст Один файл в нескольких окнах, разделение окна, фиксация областей.
Разделение рабочей области (окна)
Для разделения окна на верхнюю часть и нижнею используется разделитель вертикальной полосы прокрутки и соответственно для разделения на правую и леву – горизонтальную.
В результате окно будет разделено на 2 или 4 части.
Дополнительное окно
Программа позволяет открывать один файл одновременно в разных окнах. Если сохранить файл в таком виде, то при открытии файла откроются все созданные кна.
Упорядочить окно спомощью окна «Расположение окон».
Варианты отображения
Разметка страницы позволяет увидеть на экране документ в том виде, как он будет выглядеть на печати.
Страничный режим позволяет с помощью мыши перетаскивать границы печати.
Автозаполнение
Программа может автоматически заполнять список чисел, дат.
- Введите дату.
- Выделите ячейку с датой и наведите мышью на правый нижний угол ячейки, пока курсор не примет вид черного креста.
- Потяните за угол, ячейки автоматически заполнятся датами.
В программе есть функционал по автоматическому заполнению названий месяцев и дней недели.
Имеется возможность создавать свои списки автозаполнения.
Для этого в открывшемся окне «Списки» необходимо ввести новые данные в поле «Элементы списка:» и нажать кнопку «Добавить».
Строка состояния
Строка состояния при выделении области ячеек отображает Среднее, Количество и Сумму.
Так же, строка состояния может отображать Количество чисел, Минимум и Максимум. Настройки строки состояния вызываются нажатием правой кнопкой мыши по строке состояния.
Группировка
Строки и столбцы можно группировать для удобства просмотра больших таблиц.
Для этого имеются соответствующие команды на вкладке «Данные».
При нажатии пиктограммы (справа внизу) откроется диалоговое окно с настройками. Уберём флаг «итоги в строках под данными» и нажмём «ОК». Врезультате знак группировки переместится вверх.
Сортировка
Текст
Фильтр
Значения в столбцах можно отфильтровывать применяя различные условия. Для того, чтобы применить фильтр, необходимо выделить нужную таблицу и нажать «Фильтр».
В шапке таблицы появятся кнопки меню.
Окно настроек нескольких условий, вызывается при нажатии на любую команду из меню обведённого зелёной рамкой.
Фильтры даты и времени
Фильтр по цвету ячейки
Если в ячейках столбца используются различные цвета фона и шрифта, тогда появляется возможность установки фильтра по цвету.
Копирование и вставка
Каждая ячейка значение, формулу, описание форматов.
На изображении показана ячейка G5, в которой содержится формула «=E5*F5», а на экране виден результат вычисления «20». Кроме этих данных, ячейки содержат в себе информацию о формате данных (число, дата и т.п.), форматировании ячеек (цвета фона, шрифта, границ, размер шрифта, толщина границ и прочее), примечания, ширины столбцов и пр.
При копировании области ячеек вся эта информация попадает в буфер обмена.
В программе предусмотрена возможность, которая позволяет вставить из буфера обмена только часть данных. Это может быть значения, формулы, форматы, ширины столбцов и т.д. Для этого используется окно «Специальная вставка».
Формулы и функции
Программа MS Excel являясь электронной таблицей, позволяет вводить в ячейки не только значения. В ячейке можно разместить ссылку на другую ячейку, функцию или формулу. В результате, в ячейку будет виден результат вычислений.
Любая формула, начинается со знака «=», после которого, обязательно должна располагаться либо ссылка, либо формула, либо функция.
Внимание!
Если ячейка имеет текстовый формат, то введённые данные будут являться текстом!
Т.к. ячейка с формулой отображает результат вычисления, то для того, чтобы увидеть формулу, необходимо выделить ячейку и реальное содержимое будет показано в строке формул.
Каждая функция заканчивается круглыми скобками «()». В скобках располагаются аргументы передаваемые в функцию. Есть функции, которые не требуют аргументов. Все аргументы идут в определённом порядке и разделяются точкой с запятой.
Функция возвращает значение результата вычисления, либо определённое значение. Результаты вычислений могут иметь различный тип данных и принимать аргументы с определённым типом данных.
Если дважды кликнуть по ячейке с формулой, то в ячейке будет отображена формула, а под ячейкой появится подсказка.
Если в подсказке кликнуть по названию функции, то откроется окно справочной системы с подробным описанием функции.
Окно справочной системы.
ЕСЛИ
Функция возвращает один из двух возможных вариантов, выбранный вариант зависит от соблюдения или не соблюдения определённого условия.
Синтаксис
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
лог_выражение – логическое выражение, от результата которого зависит какое одно из двух значений будет возвращено.
значение_если_истина – значение, которое будет возвращено, если аргумент лог_выражение будет равен ИСТИНА.
значение_если_ложь -значение, которое будет возвращено, если аргумент лог_выражение будет равен ЛОЖЬ.
ОКРУГЛ
Функция округляет число до необходимого разряда до или после запятой.
Синтаксис
ОКРУГЛ(Число, Разрядность)
Смотрите так же в справочной системе функции:
СУММ
Функция возвращает сумму всех числовых значений ячеек и диапазонов указанных в аргументе функции.
СУММ(число1;число2; …)
Число1, число2, … — от 1 до 255 аргументов, для которых требуется определить итог или сумму.
Более подробное описание функции смотрите в справочной системе.
СУММЕСЛИ
При необходимости суммировать данные по определённому условию, удобно использовать функцию СУММЕСЛИ.
Например, необходимо просуммировать по колонке «Сумма» только те строки, где количество больше 15. Тогда формула будет выглядеть так:
В результате функция вернёт следующий результат:
Более подробное описание функции смотрите в справочной системе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Иногда бывает удобным получать суммы только тех ячеек, которые отображаются на экране игнорируя невидимые строки или столбцы, такое бывает при использовании фильтра.
Именно для таких случаев и используется описываемая функция.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;ссылка2;…)
номер_функции – аргумент указывающий, какую функцию применять к выделенному диапазону. Это может быть МИН, МАКС и прочие функции, В текущем примере будет использована функция СУММ, номер этой функции равен 9 (см. справочную систему).
Ссылка1; cсылка2 — от 1 до 254 интервалов или ссылок, для которых подводятся промежуточные итоги.
В результате будет отображена сумма всех видимых ячеек указанного диапазона:
Более подробное описание функции смотрите в справочной системе.
ВПР
Поиск соответствующего значения в вертикально расположенных данных.
Допустим, есть столбец «Код» и столбец «Товар». Необходимо указав в аргументе функции указать код и получить данные из столбца «Товар».
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
искомое_значение – значение, которое необходимо найти в крайнем левом столбце таблицы.
Таблица – прямоугольная область ячеек содержащая исходные данные.
номер_столбца – указывается столбец таблицы, данные которого необходимо получить в результате вычисления функции.
интервальный_просмотр – значение (ИСТИНА или ЛОЖЬ) определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.
Более подробное описание функции смотрите в справочной системе.
ГПР
Функция ГПР похожа на функцию ВПР, но используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые — на несколько строк ниже.
Более подробное описание функции смотрите в справочной системе.
ИНДЕКС
Функция используется при необходимости получить данные из таблицы (диапазона ячеек) указывая номера строки и столбца.
ИНДЕКС(массив;номер_строки;номер_столбца)
Массив – диапазон ячеек,
номер_строки — …
номер_столбца — …
Более подробное описание функции смотрите в справочной системе.
ПОИСКПОЗ
Если необходимо в столбце заполненного некими значениями (просматриваемый_массив) узнать номер строки, в которой находится некоторое значение (искомое_значение).
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)
Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив, а не само значение.
Более подробное описание функции смотрите в справочной системе.
СЦЕПИТЬ
Объединяет две или более текстовых строк в одну.
СЦЕПИТЬ (текст1;текст2;…)
Более подробное описание функции смотрите в справочной системе.
Возвращает целое число, представляющее текущие дату и время.
ТДАТА( )
Более подробное описание функции смотрите в справочной системе.
ДАТА(год;месяц;день)
МЕСЯЦ(дата_в_числовом_формате)
ГОД(дата_в_числовом_формате)
Более подробное описание функции смотрите в справочной системе.
Результат:
Следующий уровень — 🙂
(1)Прикольно, нестандартный подход, 3D-движок на Excel, молодец!
Может тебе попробовать свои способности реализовать на базе другого языка программирования?
Excel, это электронная таблица, специализированная для экономических, математических и т.п. задач.
Видно, что ты понимаешь суть программирования 3D-игр и это здорово!
И в заключении, пожелаю тебе однажды написать бестселлер.
Дерзай!
(2)Не, чувак. Эта статья на хабре — перевод. Движок на экселе написал другой человек.
(3)Понял, но в любом случае, ссылка на 3D-движок в Excel, в этой теме, не по адресу от слова «совсем».
Может автор комментария хотел на фоне движка сделать некую завуалированную обесценку моей статьи на фоне сравнения с 3D-движком?
(2), автор не я.
(4), Наоборот! В Excel можно делать очень крутые вещи (статья из ссылки выше — там всё на формулах — как раз та «база», которая в том числе описана в текущей публикации).
(4) Походу юмор не понимаешь, «совсем».
А статья твоя хорошая, мне понравилось.
Спасибо. Статья замечательная. Как раз встал вопрос обучения пользователей.
(8)Пожалуйста, если будут замечания, то дайте знать.
Отличная работа! Разошлю всем бухам ссылку. Имею предложение — сделать акцент на моменте «Специальная вставка -> Значения».
(10)Спасибо, учту. Меня ещё спрашивали о защите ячеек и об ограничении ввода данных. Добавлю по наличию свободного времени.
Excel — это вам не 1С, в нём можно всё. 🙂
Приветствую знатоков EXCEL. У меня есть такой вопрос, который никак не получается загуглить. Строим диаграмму со сглаживающими линиями по базовым точкам. Можно как-нибудь выцарапать «цифры» для этой самой кривой — список аргументов и значений?