Ange formel för namngivet område

Ange formel för namngivet område

Precis som Excel-appen, ger Aspose.Cells för Python via .NET API:erna möjlighet att ange en formel för ett namngivet område medan du använder dess Range.refers_to-egenskap. Det finns många användbarhetsscenarier för denna funktion, några av vilka är detaljerade nedan.

Ange enkel formel för namngivet område

Enkel formel skulle kunna vara en hänvisning till en annan cell i samma (eller annan) kalkylblad. Följande exempel skapar ett namngivet område i en ny kalkyl och anger dess hänvisning till en annan cell.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Add a new Named Range with name "NewNamedRange"
index = worksheets.names.add("NewNamedRange")
#  Access the newly created Named Range
name = worksheets.names[index]
#  Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
name.refers_to = "=Sheet1!$A$3"
#  Set the formula in the cell A1 to the newly created Named Range
worksheets[0].cells.get("A1").formula = "NewNamedRange"
#  Insert the value in cell A3 which is being referenced in the Named Range
worksheets[0].cells.get("A3").put_value("This is the value of A3")
#  Calculate formulas
book.calculate_formula()
#  Save the result in XLSX format
book.save(dataDir + "output_out.xlsx")

Ange komplex formel för namngivet område

En komplex formel kan vara vad som helst, till exempel en dynamiskt område eller en formel som sträcker sig över flera celler i olika kalkylblad. Följande exempel skapar ett dynamiskt område med hjälp av INDEX-funktionen för att hämta värdet från en lista baserat på dess plats.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Add a new Named Range with name "data"
index = worksheets.names.add("data")
#  Access the newly created Named Range from the collection
data = worksheets.names[index]
#  Set RefersTo property of the Named Range to a cell range in same worksheet
data.refers_to = "=Sheet1!$A$1:$A$10"
#  Add another Named Range with name "range"
index = worksheets.names.add("range")
#  Access the newly created Named Range from the collection
range = worksheets.names[index]
#  Set RefersTo property to a formula using the Named Range data
range.refers_to = "=INDEX(data,Sheet1!$A$1,1):INDEX(data,Sheet1!$A$1,9)"
#  Save the workbook
book.save(dataDir + "output_out.xlsx")

Här är ytterligare ett exempel som använder ett namngivet område för att summera värden från 2 celler i olika kalkylblad.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Insert some data in cell A1 of Sheet1
worksheets.get("Sheet1").cells.get("A1").put_value(10)
#  Add a new Worksheet and insert a value to cell A1
worksheets[worksheets.add()].cells.get("A1").put_value(10)
#  Add a new Named Range with name "range"
index = worksheets.names.add("range")
#  Access the newly created Named Range from the collection
range = worksheets.names[index]
#  Set RefersTo property of the Named Range to a SUM function
range.refers_to = "=SUM(Sheet1!$A$1,Sheet2!$A$1)"
#  Insert the Named Range as formula to 3rd worksheet
worksheets[worksheets.add()].cells.get("A1").formula = "range"
#  Calculate formulas
book.calculate_formula()
#  Save the result in XLSX format
book.save(dataDir + "output_out.xlsx")