Polytech-soft.com

ПК журнал
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

C b excel

Полные сведения о формулах в Excel

В этом курсе:

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

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Создание формулы, ссылающейся на значения в других ячейках

Введите знак равенства «=».

Примечание: Формулы в Excel начинаются со знака равенства.

Выберите ячейку или введите ее адрес в выделенной.

Введите оператор. Например, для вычитания введите знак «минус».

Выберите следующую ячейку или введите ее адрес в выделенной.

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Просмотр формулы

При вводе в ячейку формула также отображается в строке формул.

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

Ввод формулы, содержащей встроенную функцию

Выделите пустую ячейку.

Введите знак равенства «=», а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести «=СУММ».

Введите открывающую круглую скобку «(«.

Выделите диапазон ячеек, а затем введите закрывающую круглую скобку «)».

Нажмите клавишу ВВОД, чтобы получить результат.

Скачивание книги «Учебник по формулам»

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

Подробные сведения о формулах

Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.

Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, а не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после редактирования формулы. Обычно лучше помещать такие константы в отдельные ячейки, где их можно будет легко изменить при необходимости, а в формулах использовать ссылки на эти ячейки.

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

Стиль ссылок A1

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон

Ячейка на пересечении столбца A и строки 10

Диапазон ячеек: столбец А, строки 10-20.

Диапазон ячеек: строка 15, столбцы B-E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек: столбцы А-E, строки 10-20

Создание ссылки на ячейку или диапазон ячеек с другого листа в той же книге

В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе «Маркетинг» в той же книге.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек от B1 до B10

3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.

Примечание: Если название упоминаемого листа содержит пробелы или цифры, его нужно заключить в апострофы (‘), например так: ‘123’!A1 или =’Прибыль за январь’!A1.

Различия между абсолютными, относительными и смешанными ссылками

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

Скопированная формула с относительной ссылкой

Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

Читать еще:  Выборка данных в excel

Скопированная формула с абсолютной ссылкой

Смешанные ссылки Смешанная ссылка содержит абсолютный столбец и относительную строку, а также абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет форму $A 1, $B 1 и т. д. Абсолютная ссылка на строку имеет форму $1, B $1 и т. д. При изменении положения ячейки, содержащей формулу, относительная ссылка будет изменена, а абсолютная ссылка не изменится. Если вы копируете или заполните формулу в строках или столбцах, относительная ссылка автоматически корректируется, а абсолютная ссылка не изменяется. Например, при копировании и заполнении смешанной ссылки из ячейки a2 в ячейку B3 она корректируется с = A $1 на = B $1.

Скопированная формула со смешанной ссылкой

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. В Microsoft Excel используются все листы, указанные между начальным и конечным именами в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

Трехмерные ссылки нельзя использовать в формулах массива.

Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование. Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.

Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.

Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.

Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.

относительная ссылка на ячейку, расположенную на две строки выше в том же столбце

Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца

Относительная ссылка на строку, расположенную выше текущей ячейки

Абсолютная ссылка на текущую строку

При записи макроса в Microsoft Excel для некоторых команд используется стиль ссылок R1C1. Например, если записывается команда щелчка элемента Автосумма для вставки формулы, суммирующей диапазон ячеек, в Microsoft Excel при записи формулы будет использован стиль ссылок R1C1, а не A1.

Чтобы включить или отключить использование стиля ссылок R1C1, установите или снимите флажок Стиль ссылок R1C1 в разделе Работа с формулами категории Формулы в диалоговом окне Параметры. Чтобы открыть это окно, перейдите на вкладку Файл.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Работа с Excel c нуля

здравствуйте, я кое-как освоил работу с С# вот у меня возникло желание написать программу работающую с данными, которые записаны в Excel таблицу. в связи с этим вопрос как осуществить следующие действия: открыть таблицу, узнать ее размер, слить данные из ячейки с координатами XY или записать в таблицу что-то,

П.С. я никаких dll не умею ставить, если ваше решение содержит стороннюю dll то пожалуйста объясните, я много гуглил, но не нашел ничего понятного мне. нашел решение через COM Interop, но у меня не компилируется using Excel = Microsoft.Office.Interop.Excel; и в доступных ссылках нет Microsoft.Office.Interop.Excel чтобы подключить нужную библиотеку, но я вообще не настаиваю чтобы решение было через эту библиотеку. мне бы хоть как-то.

Добавлено через 3 часа 59 минут
народ, хелп, нужна помощь, как вы открываете Excel файлы? в интеренте разбирают Microsoft.Office.Interop.Excel но я нигде не могу найти библиотеку чтобы использовать это. подскажите как эту проблему решить можно, очень надо из экселя данные брать, а не могу.если не знаете как с этим вариантом быть, то напишите любой известный вам способ.

Читать еще:  Movavi как наложить картинку на видео
07.01.2017, 16:14

Работа с документами Excel без Microsoft.Office.Interop.Excel
всем добрый день все дело в том что для своих программ я использую БД Excel но! однако не все.

Работа с Excel (нет доступа к пространству имен Excel)
Здравствуйте! У меня проблема в том, что я делаю все так как там, но выдает ошибку в строчках на.

Экспорт в Excel: Прекращена работа программы «Microsoft Excel»
Файл сохраняется успешно, но поле строки excelApp.Quit(); Появляется окно ошибки Excel: .

Cколько чисел больше нуля, меньше нуля и среднее всех отрицательных чисел
Помогите с кодом Задание: Вводятся N чисел. Программа считает сколько чисел больше нуля, меньше.

07.01.2017, 16:50208.01.2017, 00:11 [ТС]308.01.2017, 05:304

Вот неплохая библиотека https://www.nuget.org/packages/EPPlus
Легко установить через PM> Install-Package EPPlus

08.01.2017, 13:05508.01.2017, 13:33 [ТС]6

библиотеку using Excel = Microsoft.Office.Interop.Excel; удалось корректно подключить. помогите написать консольное приложение, которе будет с Excel файла считывать численные данные с таких-то ячеек?

Чтобы в ячейку А1 что-то записать надо написать такой-то код:

только вопрос: куда должен сохранятся этот фаил? или это делается отдельной командой?

Эффективные способы работы с Excel

Работаю в консольных приложениях. у меня стоит Windows 10 и VS последняя актуальная версия. Кое как, методом тыка и вдумчивого поиска, разобрался с элементарными задачами: прочитать данные в такой-то ячейке, записать данные в такую-то ячейку, и создать новую книгу.
Использовал при этом:

Для меня большая проблема состоит в том, что я не вижу документации для работы с этой библиотекой. Если вы считаете, что есть более эффективные способы работы с Excel через C#, то просветите меня. Буду рад, если вы понятно для меня изложите.

3 ответа 3

Во-первых вам следует определиться, какой вам формат нужен: .xls или .xlsx . Они совершенно разные: xls — бинарный, а xlsx — это zip-архив XML файлов. Также, возможно, вам может подойти ods файл, который тоже представляет собой zip-архив XML файлов, но об этом ниже.

Office COM Interop

То о чём вы ведёте речь называется Office Interop. Иногда ещё к этому названию добавляют COM. Я сильно рекомендую его не использовать. Разве что только вы генерируете небольшие файлы на компьютере для себя. Почему?

Много утечек памяти. Ни в коем случае не используйте эту библиотеку для веб-сайтов или приложений, где на сервере генерируются файлы Office-форматов. У вас кончится оперативная память довольно быстро и сайт или приложение упадёт.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Что в вольном переводе значит:

Корпорация Майкрософт не рекомендует и не поддерживает, автоматизации приложений Microsoft Office с помощью автоматических, не-интерактивных клиентских приложений или компонентов (включая ASP, ASP.NET, DCOM и службы NT), потому что офис может показывать нестабильное поведение и/или взаимоблокировку (deadlock), когда офис работает в этой среде.

Требует наличия офиса определённой версии. На каждую машину, где будет работать ваше приложение.

Рассмотрю xlsx файлы. Вроде уже прошло время, когда ещё надо поддерживать Office 2003 и ниже.

Путаница с названием форматов

Некорректно называть этот формат Excel. Excel — это одна из программ пакета Microsoft Office. (Также некорректно называть docx -файл Word ). Называть формат xlsx Excel — приблизительно то же самое, что называть формат HTML — Internet Explorer.

Office Open XML

Что следует из того, что это zip-архив XML файлов? Что это значит?

Это значит, что вы можете взять любой Open XML формат (все которые оканчиваются на x : docx , xlsx , pptx и т.д.), переименовать расширение на zip, открыть его. Затем можно посмотреть все нужные XML-файлы и можно даже попробовать их редактировать. (То же самое справедливо и для ods , но это OpenDocument -формат, а не Open XML !).

Стандарт Open XML

xlsx -файлы безусловно связаны с реализацией Microsoft. OpenXML форматы (все которые оканчиваются на x : docx , xlsx , pptx и т.д.) стали выходить после пакета офиса 2007 года. Microsoft опубликовало спецификацию в 2006 году, затем выпустило офис. А зачем Microsoft выпустило спецификацию? Для того, чтобы её реализовать мог кто угодно.

Трудности в работе с Open XML

Важное обновление. Все нижеописанные трудности крайне успешно решаются с помощью бесплатного инструмента от Microsoft под названием OpenXML SDK Productivity Tool. Он позволяет

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

Дело в том, что стандарт Open XML мягко говоря, громоздкий (занимает 6546 страниц), поэтому его просто не осилишь просто чтением стандарта.

И с ним работать довольно тяжело. У вас есть объектная обёртка над различными тэгами XML. НО:

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

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

Читать еще:  Как записать формулу деления в excel

Всё сильно усугляется тем, что офис просто не откроет ваш документ. Точнее откроет и выдаст «The file is corrupt and cannot be opened». То есть одна ошибка (вложили не тот элемент или чего-то не вложили) и даже гигантский документ не открывается.

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

Здесь вы узнаете, что строковые значения ради производительности хранятся в специальной таблице ( SharedStringTable ) и при добавлении и новой строки добавляется всего лишь порядкой номер в этой таблице (к примеру в ячейке будет хранится 1 ). То есть если вам надо добавить строковое значение в ячейку вам надо будет посмотреть в таблицу SharedStringTable есть ли там такая строка, и если нет — то создать запись в этой таблице, и вернуть новое число, если да — то взять порядковый номер.

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

Если вам надо будет просто удалить строку (row) или столбец (column), то придётся все значения (ниже или правее) переносить вручную.

Библиотеки, основанные на Open XML

В общем, если обернуть всё методами и потратить где-то 1-3 рабочих дня, то в целом можно и так работать. Чтобы избежать этих сложный вещей используйте уже готовые библиотеки, основанные на Open XML:

Open Document Format

Помимо xls и xlsx существует также формат ods , который первоначально поддерживался в OpenOffice и затем LibreOffice. Cейчас форматы Open Document уже поддерживаются и Microsoft Office. Для этого работы с форматами Open Document существует библиотека AODL, для которой есть Nuget-пакет и примеры.

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector